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

'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
'
'
'The following is a comparison of the properties collection for each
'connection.   Window Handle and Prompt are the only two with
'differences... unsure what each of these if for, tho... :)
'
'Conn      Prop Prop                             Prop
'Method    No   Name                             Value
'-------  ----  --------------------------------------------------
'Designer    57 Window Handle                    3596
'Coded       57 Window Handle                    0
'Designer    59 Prompt                           2
'Coded       59 Prompt                           4
'
'Designer     1 Current Catalog
'Coded        1 Current Catalog
'Designer     2 Active Sessions                  128
'Coded        2 Active Sessions                  128
'Designer     3 Asynchable Commit                FALSE
'Coded        3 Asynchable Commit                FALSE
'Designer     4 Catalog Location                 1
'Coded        4 Catalog Location                 1
'Designer     5 Catalog Term                     Database
'Coded        5 Catalog Term                     Database
'Designer     6 Column Definition                1
'Coded        6 Column Definition                1
'Designer     7 NULL Concatenation Behavior      2
'Coded        7 NULL Concatenation Behavior      2
'Designer     8 Data Source Name
\\BAMBAM\DISK_N\PICDATA\Pics2000.mdb
'Coded        8 Data Source Name
\\BAMBAM\DISK_N\PICDATA\Pics2000.mdb
'Designer     9 Read-Only Data Source            FALSE
'Coded        9 Read-Only Data Source            FALSE
'Designer    10 DBMS Name                        MS Jet
'Coded       10 DBMS Name                        MS Jet
'Designer    11 DBMS Version                     04.00.0000
'Coded       11 DBMS Version                     04.00.0000
'Designer    12 GROUP BY Support                 4
'Coded       12 GROUP BY Support                 4
'Designer    13 Heterogeneous Table Support      2
'Coded       13 Heterogeneous Table Support      2
'Designer    14 Identifier Case Sensitivity      8
'Coded       14 Identifier Case Sensitivity      8
'Designer    15 Maximum Index Size               255
'Coded       15 Maximum Index Size               255
'Designer    16 Maximum Row Size                 4049
'Coded       16 Maximum Row Size                 4049
'Designer    17 Maximum Row Size Includes BLOB   FALSE
'Coded       17 Maximum Row Size Includes BLOB   FALSE
'Designer    18 Maximum Tables in SELECT         0
'Coded       18 Maximum Tables in SELECT         0
'Designer    19 Multiple Storage Objects         FALSE
'Coded       19 Multiple Storage Objects         FALSE
'Designer    20 Multi-Table Update               TRUE
'Coded       20 Multi-Table Update               TRUE
'Designer    21 NULL Collation Order             4
'Coded       21 NULL Collation Order             4
'Designer    22 OLE Object Support               1
'Coded       22 OLE Object Support               1
'Designer    23 ORDER BY Columns in Select List  FALSE
'Coded       23 ORDER BY Columns in Select List  FALSE
'Designer    24 Prepare Abort Behavior           1
'Coded       24 Prepare Abort Behavior           1
'Designer    25 Prepare Commit Behavior          2
'Coded       25 Prepare Commit Behavior          2
'Designer    26 Procedure Term                   STORED QUERY
'Coded       26 Procedure Term                   STORED QUERY
'Designer    27 Provider Name                    MSJETOLEDB40.DLL
'Coded       27 Provider Name                    MSJETOLEDB40.DLL
'Designer    28 OLE DB Version                   2.1
'Coded       28 OLE DB Version                   2.1
'Designer    29 Provider Version                 04.00.2521
'Coded       29 Provider Version                 04.00.2521
'Designer    30 Schema Term                      Schema
'Coded       30 Schema Term                      Schema
'Designer    31 Schema Usage                     0
'Coded       31 Schema Usage                     0
'Designer    32 SQL Support                      512
'Coded       32 SQL Support                      512
'Designer    33 Structured Storage               9
'Coded       33 Structured Storage               9
'Designer    34 Subquery Support                 63
'Coded       34 Subquery Support                 63
'Designer    35 Isolation Levels                 4096
'Coded       35 Isolation Levels                 4096
'Designer    36 Isolation Retention              9
'Coded       36 Isolation Retention              9
'Designer    37 Table Term                       Table
'Coded       37 Table Term                       Table
'Designer    38 User Name                        Admin
'Coded       38 User Name                        Admin
'Designer    39 Pass By Ref Accessors            FALSE
'Coded       39 Pass By Ref Accessors            FALSE
'Designer    40 Transaction DDL                  16
'Coded       40 Transaction DDL                  16
'Designer    41 Asynchable Abort                 FALSE
'Coded       41 Asynchable Abort                 FALSE
'Designer    42 Data Source Object Threading Model    1
'Coded       42 Data Source Object Threading Model    1
'Designer    43 Output Parameter Availability    1
'Coded       43 Output Parameter Availability    1
'Designer    44 Persistent ID Type               4
'Coded       44 Persistent ID Type               4
'Designer    45 Multiple Parameter Sets          TRUE
'Coded       45 Multiple Parameter Sets          TRUE
'Designer    46 Rowset Conversions on Command    TRUE
'Coded       46 Rowset Conversions on Command    TRUE
'Designer    47 Multiple Results                 0
'Coded       47 Multiple Results                 0
'Designer    48 Provider Friendly Name           Microsoft OLE DB
Provider for Jet
'Coded       48 Provider Friendly Name           Microsoft OLE DB
Provider for Jet
'Designer    49 Alter Column Support             36
'Coded       49 Alter Column Support             36
'Designer    50 Open Rowset Support              2
'Coded       50 Open Rowset Support              2
'Designer    51 Cache Authentication             TRUE
'Coded       51 Cache Authentication             TRUE
'Designer    52 Encrypt Password                 FALSE
'Coded       52 Encrypt Password                 FALSE
'Designer    53 Mask Password                    FALSE
'Coded       53 Mask Password                    FALSE
'Designer    54 Password
'Coded       54 Password
'Designer    55 User ID                          Admin
'Coded       55 User ID                          Admin
'Designer    56 Data Source
\\BAMBAM\DISK_N\PICDATA\Pics2000.mdb
'Coded       56 Data Source
\\BAMBAM\DISK_N\Picdata\Pics2000.mdb
'Designer    58 Mode                             16
'Coded       58 Mode                             16
'Designer    60 Extended Properties
'Coded       60 Extended Properties
'Designer    61 Locale Identifier                1033
'Coded       61 Locale Identifier                1033
'Designer    62 Jet OLEDB:System database
'Coded       62 Jet OLEDB:System database
'Designer    63 Jet OLEDB:Registry Path
'Coded       63 Jet OLEDB:Registry Path
'Designer    64 Jet OLEDB:Database Password
'Coded       64 Jet OLEDB:Database Password
'Designer    65 Jet OLEDB:Engine Type            5
'Coded       65 Jet OLEDB:Engine Type            5
'Designer    66 Jet OLEDB:Database Locking Mode  1
'Coded       66 Jet OLEDB:Database Locking Mode  1
'Designer    67 Jet OLEDB:Global Partial Bulk Ops2
'Coded       67 Jet OLEDB:Global Partial Bulk Ops2
'Designer    68 Jet OLEDB:Global Bulk Transactions    1
'Coded       68 Jet OLEDB:Global Bulk Transactions    1
'Designer    69 Jet OLEDB:New Database Password
'Coded       69 Jet OLEDB:New Database Password
'Designer    70 Jet OLEDB:Create System Database FALSE
'Coded       70 Jet OLEDB:Create System Database FALSE
'Designer    71 Jet OLEDB:Encrypt Database      
...

read more »



Tue, 21 May 2002 03:00:00 GMT  
 Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

Quote:
>'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

Take off the extra semi-colon after the Data Source parameter and try it.

Another tip: Look up App.path in the help facility.



Wed, 22 May 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

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

2. PB of size with ADO 2.1 and Jet 4.0

3. ADO 2.1, Jet 4.0 as a Provider ?

4. VB6, ADO 2.1 and MSDE (Access 2000)

5. Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

6. Add fields to an Access 2000 Table on the fly using ADO 2.1

7. Performance ADO with Jet 4.0 (Access 2000)

8. ADO and Access 2000 database (Jet 4.0)

9. ADO 2.1 Jet SEEK Problem

10. multiple parameters with ADO and SQL Server 2000 Stored proc

11. Error opening ADO recordset hitting Access 2000

12. SQLServer 2000 Stored Proc Problem

 

 
Powered by phpBB® Forum Software