Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

Post by Sc00b » Sat, 04 Dec 1999 04:00:00



'Problem:
'   I'm having trouble getting ADO to open a recordset for update,
'   without using the designer.  Data is in an Access 2000 Query,
'   non-parametered.  If I use a designer based connection, the
'   recordset opens okay.  If I hardcode the connection, the recordset
'   will only open successfully with adLockReadOnly.  Opening with
'   locking gets a runtime error h80004005 'Invalid operation'.

'
'Designer connection works...
'
    envMain.conPics2000.Open

    Set rstCollValidate = New ADODB.Recordset
    rstCollValidate.Open "[CollectionsValidate]", envMain.conPics2000
_
                         , adOpenKeyset, adLockOptimistic,
adCmdStoredProc
'
'Hardcode connection does not...
'
    sPathDB2000 = "\\BAMBAM\DISK_N\Picdata\Pics2000.mdb"
    Set cnnDB2000 = New ADODB.Connection
    cnnDB2000.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & sPathDB2000 & ";"

    Set rstCollValidate = New ADODB.Recordset
    rstCollValidate.Open "[CollectionsValidate]", cnnDB2000 _
                         , adOpenKeyset, adLockOptimistic,
adCmdStoredProc

' Both connections, after being opened, return the same connection
' string...
'
' Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;
'          Data Source=\\BAMBAM\DISK_N\PICDATA\Pics2000.mdb;
'          Mode=Share Deny None;
'          Extended Properties="";
'          Locale Identifier=1033;
'          Jet OLEDB:System database="";
'          Jet OLEDB:Registry Path="";
'          Jet OLEDB:Database Password="";
'          Jet OLEDB:Engine Type=5;
'          Jet OLEDB:Database Locking Mode=1;
'          Jet OLEDB:Global Partial Bulk Ops=2;
'          Jet OLEDB:Global Bulk Transactions=1;
'          Jet OLEDB:New Database Password="";
'          Jet OLEDB:Create System Database=False;
'          Jet OLEDB:Encrypt Database=False;
'          Jet OLEDB:Don't Copy Locale on Compact=False;
'          Jet OLEDB:Compact Without Replica Repair=False;
'          Jet OLEDB:SFP=False
'
'
' Anyone have a suggestion as to what's missing on the hardcode
' connection side?  Thanks, huge !!
'
' Kurt Schulte

 
 
 

Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

Post by Brian Griffi » Sun, 05 Dec 1999 04:00:00


As I understand what you have provided, you want to run an Access Querydef
named "CollectionsValidate".

If that is your objective, your problem stems from the fact that you are
trying to use adCmdStoredProc.  The OLE-DB provider for JET 4.0 does not
implement adCmdStoredProc, use adCmdTable instead.

 rstCollValidate.Open "[CollectionsValidate]", envMain.conPics2000,
adOpenKeyset, adLockOptimistic, adCmdTable


>'Problem:
>'   I'm having trouble getting ADO to open a recordset for update,
>'   without using the designer.  Data is in an Access 2000 Query,
>'   non-parametered.  If I use a designer based connection, the
>'   recordset opens okay.  If I hardcode the connection, the recordset
>'   will only open successfully with adLockReadOnly.  Opening with
>'   locking gets a runtime error h80004005 'Invalid operation'.

>'
>'Designer connection works...
>'
>    envMain.conPics2000.Open

>    Set rstCollValidate = New ADODB.Recordset
>    rstCollValidate.Open "[CollectionsValidate]", envMain.conPics2000
>_
>                         , adOpenKeyset, adLockOptimistic,
>adCmdStoredProc
>'
>'Hardcode connection does not...
>'
>    sPathDB2000 = "\\BAMBAM\DISK_N\Picdata\Pics2000.mdb"
>    Set cnnDB2000 = New ADODB.Connection
>    cnnDB2000.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                   "Data Source=" & sPathDB2000 & ";"

>    Set rstCollValidate = New ADODB.Recordset
>    rstCollValidate.Open "[CollectionsValidate]", cnnDB2000 _
>                         , adOpenKeyset, adLockOptimistic,
>adCmdStoredProc

>' Both connections, after being opened, return the same connection
>' string...
>'
>' Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;
>'          Data Source=\\BAMBAM\DISK_N\PICDATA\Pics2000.mdb;
>'          Mode=Share Deny None;
>'          Extended Properties="";
>'          Locale Identifier=1033;
>'          Jet OLEDB:System database="";
>'          Jet OLEDB:Registry Path="";
>'          Jet OLEDB:Database Password="";
>'          Jet OLEDB:Engine Type=5;
>'          Jet OLEDB:Database Locking Mode=1;
>'          Jet OLEDB:Global Partial Bulk Ops=2;
>'          Jet OLEDB:Global Bulk Transactions=1;
>'          Jet OLEDB:New Database Password="";
>'          Jet OLEDB:Create System Database=False;
>'          Jet OLEDB:Encrypt Database=False;
>'          Jet OLEDB:Don't Copy Locale on Compact=False;
>'          Jet OLEDB:Compact Without Replica Repair=False;
>'          Jet OLEDB:SFP=False
>'
>'
>' Anyone have a suggestion as to what's missing on the hardcode
>' connection side?  Thanks, huge !!
>'
>' Kurt Schulte