ADO code needed (Any)

Topic

New Reply

I’m somewhat ashamed to admit it, but my ADO skill are rather limited (and even that is an understatement). Can someone show me the correct syntax for opening a connection to another database? (That is, a different database than the CurrentProject.)

Reply | Quote

Replies

This is a simple example of opening a connection to Northwind.mdb from another database:

Public Sub adoTestConnection()
On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strAppPath As String
Dim strCnn As String
Dim strSQL As String
Dim strMsg As String

‘ If db is secured use this syntax:
‘ strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
‘ “User ID=MyUserName;” & _
‘ “Password=MyPwd;” & _
‘ “Data Source=” & strAppPath & “MyApp.mdb;” & _
‘ “Jet OLEDB:System database=” & strAppPath & “MyApp.mdw;”

‘ If db is not secured:
strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb;”

strSQL = “SELECT * FROM ORDERS;”

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.ConnectionString = strCnn
cnn.Open
rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic
‘ Test:
strMsg = “There are ” & rst.RecordCount & ” records in the Orders table.”
MsgBox strMsg, vbInformation, “RECORD COUNT”

rst.Close
cnn.Close

Exit_Sub:
Set cnn = Nothing
Set rst = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ADO CONNECTION ERROR”
Resume Exit_Sub
End Select

End Sub

Note if other db is secured you will need to provide valid username & password & specify path to workgroup file to open a connection, as shown in commented out block. This example opens read-only recordset. You’ll need to modify the rst.Open statement depending on what type of recordset you need – the ADO Help files may provide some guidance on correct arguments to use. For example, for an updatable recordset you’d use adOpenDynamic in place of adOpenStatic for CursorType argument. If updating records much of the syntax is similar (or identical to) DAO equivalent (except, as I usually forget, ADO has no “Edit” method….)

HTH

Reply | Quote

Thanks! This is a really big help.

I’m using this with code I got from the MSKB to reset the autonumber “seed” value. We’ve hit that problem in which (after an import) the next autonumber value duplicates an existing record. I’m still somewhat amazed that Microsoft can’t fix this.

Reply | Quote

I seem to be having some problems. Here is the function I got from MSKB. It works fine for a table within the same database. But from my Frontend database, I need to manipulate the autonumbers in tables in the Backend. So right now I’m hung up on that “Set cnn = CurrentProject.Connection” line of code.

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
‘You must pass the following variables to this function.
‘strTbl = Table containing autonumber field
‘strCol = Name of the autonumber field
‘lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

‘Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties(“Seed”) = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties(“seed”) = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

Reply | Quote

Mark,

If the table is in the backend database, you need to make a connection to manipulate the table’s design there. So you can’t use the CurrentProject.Connection approach, you have to build the full connection. There’s an example in on-line help of using the Columns Collection in ADOX that ;might be helpful to you. And what kind of table is the backend, because “seed” isn’t a property exposed by the Jet OLE DB provider?

Reply | Quote

It is an Access database. The MSKB # is 287756.

Reply | Quote

I usually only use ADOX with Current Project. Here is an example of opening connection to other than CurrentProject for data definition purposes:

Public Sub adoCreateNewTable()
On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strCnn As String
Dim strMsg As String

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb;”

cnn.ConnectionString = strCnn
cnn.Open strCnn
cat.ActiveConnection = cnn

tbl.Name = “Table1”
tbl.Columns.Append “PK_Field”, adInteger
tbl.Keys.Append “PrimaryKey”, adKeyPrimary, “PK_Field”
cat.Tables.Append tbl
cnn.Close

MsgBox “New table Table1 created in Northwind.mdb.”, vbInformation, “TEST MSG”

Exit_Sub:
Set cnn = Nothing
Set cat = Nothing
Set tbl = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ADOX NEW TABLE ERROR”
Resume Exit_Sub
End Select

End Sub

Note the line highlighted in bold – when opening connection be sure to include connection string or this will not work! This example successfully created new table in Northwind.mdb. I’m afraid I don’t know if ADOX will let you reset “seed” for an AutoIncrement field.

HTH

Reply | Quote

Now I’m get a “Can’t find installable isam” error message when executing the “cnn.open strcnn” line of code. I’ve find a few references to that in MSKB, but so far none of them seem to help. Aargh!

Reply | Quote

Taking another look at this issue I had no problem setting connection to another .MDB (linked back table db, Northwind.mdb), tho’ resetting the AutoIncrement field proved to be tricky. Here is example of sub using ADO connection to reset an AutoIncrement (aka AutoNumber aka Identity aka Counter) field. Instead of using flaky ADOX to reset field attributes, used DDL SQL which is simpler & more reliable; use the ADO Connection Execute method to run the SQL statement:

Public Sub adoResetAutoIncrementField(ByRef strDbName As String, _
ByRef strTbl As String, _
ByRef strFld As String, _
ByRef lngSeedVal As Long, _
ByRef lngIncrementVal As Long)
On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim strCnn As String
Dim strSQL As String
Dim strMsg As String

‘ strDbName = “C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb”

Set cnn = New ADODB.Connection
strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & strDbName & “;”
cnn.ConnectionString = strCnn
cnn.Open strCnn

‘ DDL syntax: “ALTER TABLE [Table1] ALTER COLUMN [RecordID] COUNTER (1000,4);”

strSQL = “ALTER TABLE [” & strTbl & “] ALTER COLUMN [” & strFld & “] ” & _
“COUNTER (” & lngSeedVal & “,” & lngIncrementVal & “);”

cnn.Execute strSQL, , adCmdText Or adExecuteNoRecords
cnn.Close

‘ Possible errors:
‘ strTbl = “Orders”, strFld = “OrderID”
‘ This generated error due to OrderID being used in relationships:
‘ Error No -2147467259: Cannot change field ‘OrderID’. _
It is part of one or more relationships. _
Also will get error if fld is defined as PK for table: _
Error No -2147467259: Invalid field data type.

strMsg = “AutoNumber field ” & strFld & ” seed value has been reset to ” & _
lngSeedVal & ” in ” & strTbl & ” table. ” & _
“Increment Value reset to ” & lngIncrementVal & “.”
MsgBox strMsg, vbInformation, “TEST MSG”

Exit_Sub:
Set cnn = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Debug.Print strMsg
MsgBox strMsg, vbExclamation, “RESET AUTOINCREMENT FIELD ERROR”
Resume Exit_Sub
End Select

End Sub

Note that if the AutoNumber field was defined as a Primary Key, or involved in a relationship, was not able to alter field; got one of the error msg’s described in commented out block above. To be able to reset AutoNumber field defined as PK, it was necessary to first remove the PK index, then reset using DDL SQL Constraint clause like in this example:

ALTER TABLE Table1 ALTER COLUMN RecordID COUNTER (2000,5) CONSTRAINT PrimaryKey PRIMARY KEY;

If the AutoNumber field is also involved in relationships, it might be a bit convoluted to reset; you’d probably have to delete the relationships then re-create – not fun. I don’t know why you’d get an “Installable ISAM” error when trying to open ADO connection – that makes no sense!! The above code worked correctly on my system which is using AXP with Access 2000 file format for both FE & BE db’s.

HTH

Reply | Quote

Dummy that I am, I had typed in “DataSource=” rather than “Data Source=”! Works fine now.

Thanks again for your help.

Reply | Quote

PS – if ADO connection doesn’t work you can always resort to DAO as last resort, using Database object Execute method:

Public Sub daoResetAutoIncrementField(ByRef strDbName As String, _
ByRef strTbl As String, _
ByRef strFld As String, _
ByRef lngSeedVal As Long, _
ByRef lngIncrementVal As Long)
On Error GoTo Err_Handler

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim strMsg As String

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDbName)

strSQL = “ALTER TABLE [” & strTbl & “] ALTER COLUMN [” & strFld & “] ” & _
“COUNTER (” & lngSeedVal & “,” & lngIncrementVal & “);”

db.Execute strSQL
db.Close

Set ws = Nothing
Set db = Nothing

Example of use:

daoResetAutoIncrementField “C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb”,”Table2″,”RecordID”,5000,5

This successfully reset AutoNumber field for Table2 (a linked table in current db) as shown in attached screen shot. Using DAO db Execute method seems to work same as ADO Connection Execute for executing a DDL SQL statement (you’ll get the same errors as well).

HTH

Reply | Quote