About MS ACCESS Exception 80020009 Errors

[Michael C. Amundsen [MVP]]

OK, time for some serious NG-community work.

Several folks have seen this error and I'm trying to help track it down. I
don't have an answer for you, but *do* have some ideas that you might look
into. I'm hoping we can start a singel thread here to focus members here
and we can all solve the problem together. There's several reporting this
problem and several MVPs in this group that have experience in this area.
Together, we'll get it!

I'll start by saying I am not seeing this problem on my machines and have
not been able to re-create it (yet!). I'm proposing we all combine our
resources to detail the problem. This will make it easier for us to ferret
out.

So...

THE PROBLEM
Let's talk about the problem. Here's your chance to chime in with examples
and help us narrow the field:

The typical condition is when you attempt to use DTC Recordsets to connect
to an MS Access database. The report occurs on a handful of different link
numbers within the Jscript code that is used by the DTC Recordset.

Usually you can follow the code module, line number to a line that looks
something like this:

DE.Reordsets('your table')

_initDE.Create....

or something else that seems pretty 'low-level' and ominous.

This problem seems to disappear when the same folks attempt to use an SQL
Server data source.

THE KB ARTICLES:
I found only three articles with 80020009 as the error number. You can pull
these articles yourself by sending an email to msh...@microsoft.com with the
Q-number in the subject line.

Q175239
The first deals reports that you'll get this error if you have an SQL BLOB
or TEXT field as anything other than the first field in the record. For MS
Access, this would be the equivalent of the MEMO field. If this sounds like
you, modify your query to leave out the memo field and see if the problem
goes away.

Q174222
This one is for Commerce Server folks. Article says this happens if there
is not proper error handling in a *component* used by Commerce Server. Not
likely for us...

Q179406
Another Commerce Server item. Reports the error when a Server.CreateObject
fails. Possibly related to us. I suspect some Server.CreateObject stuff
happens in the DE area since it's really accessing the ADO library. Just
fishing, here.

A SIMPLE TEST
So here's a test:

- create a new VI6 project

- add an ASP document

- add a data connection to an existing Access database. Make sure the
database is on the *same box* as the web server. If you're using VI6,
build the web on your own machine and place the database on this same
machine.

- add a data connection that selects *one field* from an existing table that
has no memo fields. something like: SELECT MyField FROM MyTable

- drop the command onto the ASP document (say yes to scripting object
model).

- drag the one field from the project explorer onto the page (this will
create a text box)

- save the page

- run it

does it work?<g>

Any other suggestions from folks here are welcome, too.

Hope this helps!

MCA
Michael C. Amundsen, MCP/MVP
mi...@amundsen.com
http://www.amundsen.com
=======================================================
Subscribe: VI6Newsletter (www.amundsen.com/vi6news.htm)
Join: Using VI6 Club (www.amundsen.com/vi6club.htm)
Read: Using VI6 (QUE) (www.amundsen.com/vi6book.htm)
=======================================================

[Hans Carlsson]

Well... I get that kind of error using MS SQL Server 6.5. Here's where it
occurs in my code.

===Begin code===

<!--#INCLUDE FILE="_ScriptLibrary/Recordset.ASP"-->
<SCRIPT LANGUAGE="JavaScript" RUNAT="server">
function _initRecordset1()
{
thisPage.createDE();
!!! =====> var rsTmp = DE.Recordsets('Command1');
Recordset1.setRecordSource(rsTmp);
Recordset1.open();
if (thisPage.getState('pb_Recordset1') != null)
Recordset1.setBookmark(thisPage.getState('pb_Recordset1'));
}

===End code===

This happens in a plain vanilla new ASP page generated by 'Add Active Server
Page...' in VID. I have one data connection and a simple table containing
two int columns. The DE object seems to be OK if you put a watch on it just
before the Recordsets call. The JScript message you get when executing
DE.Recordsets('Command1') is something in the style of "An exception of type
'Microsoft JScript runtime error. Invalid procedure call or argument' was
not handled".

Out of the blue: maybe installing/experimenting with different versions of
WSH might have broken something.

Michael C. Amundsen [MVP] wrote in message ...

>OK, time for some serious NG-community work.
>
>Several folks have seen this error and I'm trying to help track it down. I
>don't have an answer for you, but *do* have some ideas that you might look
>into. I'm hoping we can start a singel thread here to focus members here
>and we can all solve the problem together. There's several reporting this
>problem and several MVPs in this group that have experience in this area.
>Together, we'll get it!

[snip]

[Morten Tvedten]

THANK YOU!!
It's good to know that I am not alone in the big fight against the big
Microsoft bugs!

You may have seen my posting in this newsgroup a bit further down the list
(15/09/98)
After successfully getting Visual interdev to work locally on my PC at home
I was confident to install it on our intranet server at work.
All our database applications created in Visual Interdev 1.0 still work OK,
but when I attempt to make a new database connection, in VI6.0 I get the
following message:

error '80020009'
Exception occurred
/datatest/Data1.asp, line 22

This is the spseified recordset runtime script from data1.asp:

<!--#INCLUDE FILE="_ScriptLibrary/Recordset.ASP"-->
<SCRIPT LANGUAGE="JavaScript" RUNAT="server">
function _initRecordset1()
{
thisPage.createDE();

var rsTmp = DE.Recordsets('Command1'); <----- THIS IS
LINE 22

Recordset1.setRecordSource(rsTmp);
Recordset1.open();
if (thisPage.getState('pb_Recordset1') != null)
Recordset1.setBookmark(thisPage.getState('pb_Recordset1'));
}

function _Recordset1_ctor()
{
CreateRecordset('Recordset1', _initRecordset1, null);
}
function _Recordset1_dtor()
{
Recordset1._preserveState();
thisPage.setState('pb_Recordset1', Recordset1.getBookmark());
}
</SCRIPT>

This was using an SQL statement. I then tried to access the a table in the
recordset this gave me the familiar 80040005 Data Source Not found and No
default driver...
error, but I could not get any further.

Our setup is as follows:
Server:
Compaq Proliant 1600
NTServer 4.0 SP3
NT OptionPack installed from one of the MSDN CD from a few months back (see
below)
Visual Interdev Server intallations
Intel 300Mhz Pentium II, 128MB RAM

Workstations:
Windows 98 and 95
Visual Interdev 6.0 was installed with Visual Interdev 1.0 still in place.
No server installations
Intel Pentium 200MMX, 64 MB RAM

By the way: A few months ago, after upgrading from IIS3 to IIS4, we could no
longer use UNC Paths to connect to databases in Interdev 1.0, even if the
database was on the same server.
We had to make a system DNS on both the server and development workstations.
Then edit the global.asa file.
We deleted the connection string line, and replaced it with a line saying
that the connection string equaled the DSN name:

Session("DataConn_ConnectionString") =
"DBQ=C:\WEBSHARE\SCRIPTS\ktkommentar.mdb;DefaultDir=C.........

was replaced with

Session("DataConn_ConnectionString") = "MyDSNName"

Hope this is useful. If you need any more information, please let me know
ASAP.
I am anxious to get this working properly!

Morten Tvedten
mor...@mediafarm.no

[Morten Tvedten]

[Morten Tvedten]

THANK YOU!!

[Morten Tvedten]

[Hans Carlsson]

In my case it was because I had configured my ODBC Data Source in the ODBC
CPL to use 'Windows NT authentication using the network login id' to verify
the login. I changed that to use SQL Server authentication instead and
everything works smooth. Btw, my SQL Server uses mixed security mode.

Whine... ODBC, SQL Server, and NT authentication has always been a mess to
configure. It isn't conceptually easier when you mix in anonymous accounts
for web services (IUSR_MACHINE) etc.

[Michael C. Amundsen [MVP]]

HC:

thanks for the info.

So, in your case, this was happening because NT Authentication was turned on
in your ODBC definition for the data set. When you switch to use SQL Server
as the permission source, all is well, right?

<attempting a test....>

Yes, I can recreate the problem by changing the settings in the ODBC
definition.

This won't solve the MS Access troubles, but might give everyone some ideas,
eh?

MCA

Hans Carlsson wrote in message
<#qVQ2OL5...@uppssnewspub05.moswest.msn.net>...

[Hans Carlsson]

Yes. I got it figured out when I turned on ODBC tracing and had a peek at
the log file and saw that SQL Server rejected the connection attempts. Maybe
you should try that with Access too, to see what's really happening...

Michael C. Amundsen [MVP] wrote in message ...

[Michael C. Amundsen [MVP]]

HC:

turn on trace for MS Access...

hmmm....

MCA

Hans Carlsson wrote in message ...

[Morten Tvedten]

I tried to trace the ODBC. The log file war rather greek to me, but I found
that the result was different than if i tried one of the old Interdev 1.0
applications.
I stoped shortly after one line saying:

DIAG [S1009] [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't
a valid path. Make sure that the path name is spelled correctly and that
you are connected to the server on which the file resides. (-1023)

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (0)

But if I changed the recordset option to read from a Table, not from a
DEcommand it worked!!

I.e I could read data via a table, but not via a SQL statement!

This does not tell me much, but maybe it means something to you.

Morten Tvedten
mor...@mediafarm.no

[Morten Tvedten]

[Brett Turley]

That was my results as well. If I changed the recordset to read from a
table instead of the decommand, it worked every time. I would like to know
exactly what the problem is.

Brett

Morten Tvedten wrote in message ...

[patrick....@iname.com]

>
> error '80020009'
> Exception occurred
> /datatest/Data1.asp, line 22
>

In case you didn't solve the problem:

Just open the global.asa file, go to the line where the connection string is
defined and remove anthing in that string except "DSN=Fitch;". Things will
work better.

You'll get explanations about this in article Q178215 of the Knowledge Base.

Good luck.

Patrick Philippot
patrck.p...@iname.com

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum