I am trying to write to an Access 2000 table from the Session_OnStart 

subroutine of global.asa however an error number 0x80004005: 'Unspecified 

Error' (what ever that is supposed to mean!) is occurring when I try and 

open the second recordset. A very peculiar error because I am using the 

same connection object and exactly the same arguments to open the table, 

however I am using a different variable and a different table. Here is an 

extract from the code I am using:



---------------------------------



Sub Session_OnStart



Const adOpenForwardOnly = 0

Const adLockPessimistic = 2 

Const adCmdTable = &H0002 



Dim conMyConnection

Dim rstRecordset1, rstRecordset2



'Set up the connection to the database

Set conMyConnection = Server.CreateObject("ADODB.Connection")

conMyConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

	Data Source=" & Server.MapPath("MyDatabase.mdb")



'Open the first recordset

Set rstRecordset1 = Server.CreateObject("ADODB.Recordset")

rstRecordset1.Open "Table1", conMyConnection, adOpenForwardOnly, & _

	adLockPessimistic, adCmdTable



'Do something to the recordset



'Close the recordset and clean up

rstRecordset1.Close

Set rstRecordset1 = Nothing



'Open the second recordset

Set rstRecordset2 = Server.CreateObject("ADODB.Recordset")



'The error occurs on the next line



rstRecordset2.Open "Table2", conMyConnection, adOpenForwardOnly, & _

	adLockPessimistic, adCmdTable



'Close the recordset and connection, and clean up

rstRecordset2.Close

conMyConnection.Close

Set rstRecordset2 = Nothing

Set conMyConnection = Nothing



End Sub



---------------------------------



I have tried this on IIS 5.0 on Windows XP, PWS on Windows ME and online 

using IIS, every time it is the same error. If I change the target table 

of the second recordset to Table1 (the one used for the first recordset) 

then it appears to work, however I odviously want to use Table2. I can 

access the database from MS Access, I have checked the table names, I can 

make a connection to the database using VB, however I have to convert it 

to an Access 97 database, ASP just does not want to know.



Has anyone got any ideas, because I am completely stumped!

first thing: you can't use an adOpenForwardOnly cursor with an

adLockPessimistic locktype :-)

you might as well change the cursor type to adOpenKeyset, because that's

what you're getting.



Secondly, do you really need the adLockPessimistic? You'd be stopping anyone

else accessing the recordset (except in read-only mode) whilst you have it

open. Can you use adLockOptimistic instead?



Lastly, do you really need to use recordset's at all? Can't you just build

an SQL statement, and avoid the whole cursor system?



strSQL = "INSERT INTO Table1 (field1) VALUES (1)"

objConn.Execute strSQL,,adExecuteNoRecords+adCmdText



strSQL = "INSERT INTO Table2 (field2) VALUES (2)"

objConn.Execute strSQL,,adExecuteNoRecords+adCmdText



objConn.Close

Set objConn = Nothing



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Owain Williams" <email@o...>

Subject: [access_asp] Error 0x80004005 'Unspecified error'





: I am trying to write to an Access 2000 table from the Session_OnStart

: subroutine of global.asa however an error number 0x80004005: 'Unspecified

: Error' (what ever that is supposed to mean!) is occurring when I try and

: open the second recordset. A very peculiar error because I am using the

: same connection object and exactly the same arguments to open the table,

: however I am using a different variable and a different table. Here is an

: extract from the code I am using:

:

: ---------------------------------

:

: Sub Session_OnStart

:

: Const adOpenForwardOnly = 0

: Const adLockPessimistic = 2

: Const adCmdTable = &H0002

:

: Dim conMyConnection

: Dim rstRecordset1, rstRecordset2

:

: 'Set up the connection to the database

: Set conMyConnection = Server.CreateObject("ADODB.Connection")

: conMyConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

: Data Source=" & Server.MapPath("MyDatabase.mdb")

:

: 'Open the first recordset

: Set rstRecordset1 = Server.CreateObject("ADODB.Recordset")

: rstRecordset1.Open "Table1", conMyConnection, adOpenForwardOnly, & _

: adLockPessimistic, adCmdTable

:

: 'Do something to the recordset

:

: 'Close the recordset and clean up

: rstRecordset1.Close

: Set rstRecordset1 = Nothing

:

: 'Open the second recordset

: Set rstRecordset2 = Server.CreateObject("ADODB.Recordset")

:

: 'The error occurs on the next line

:

: rstRecordset2.Open "Table2", conMyConnection, adOpenForwardOnly, & _

: adLockPessimistic, adCmdTable

:

: 'Close the recordset and connection, and clean up

: rstRecordset2.Close

: conMyConnection.Close

: Set rstRecordset2 = Nothing

: Set conMyConnection = Nothing

:

: End Sub

:

: ---------------------------------

:

: I have tried this on IIS 5.0 on Windows XP, PWS on Windows ME and online

: using IIS, every time it is the same error. If I change the target table

: of the second recordset to Table1 (the one used for the first recordset)

: then it appears to work, however I odviously want to use Table2. I can

: access the database from MS Access, I have checked the table names, I can

: make a connection to the database using VB, however I have to convert it

: to an Access 97 database, ASP just does not want to know.

:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I found the problem.



The name of the table I am accessing in the second recordset is 

called 'Session' and ADO did not like this. I got round this by enclosing 

the table name in square brackets, so the code has changed from:



	rstRecordset2.Open "Session", conMyConnection, & _

		adOpenForwardOnly, adLockPessimistic, adCmdTable



To:



	rstRecordset2.Open "[Session]", conMyConnection, & _

		adOpenKeyset, adLockOptimistic, adCmdTable



I am assuming this is because 'Session' is a reserved word. Just to be 

sure, I also used a 'KeySet' cursor instead of a 'ForwardOnly' one, with 

an 'Optimistic' lock instead of a 'Pessimistic' one.



Thanks to Ken Schaefer for pointing me in the right direction. Indecently, 

I am using a cursor because I need to get an Autonumber field from a newly 

created record in Access. I had a look at adOpenAtatic.com, specifically 

the 'What is the fastest way to get the Autonumber of a newly inserted 

record?' section of the 'Experiments' column, to see how to do this with 

SQL, but I have never heard of the 'SELECT @@Identity' statement and I 

could not get it to work in my code. Thanks anyway.