Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    ADO code needed (Any)

    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.)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO code needed (Any)

    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

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ADO code needed (Any)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ADO code needed (Any)

    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
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO code needed (Any)

    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?
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO code needed (Any)

    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

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ADO code needed (Any)

    It is an Access database. The MSKB # is 287756.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ADO code needed (Any)

    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO code needed (Any)

    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 <!t>[Table1]<!/t> ALTER COLUMN <!t>[RecordID]<!/t> COUNTER (1000,4);"

    strSQL = "ALTER TABLE <!t>[" & strTbl & "]<!/t> ALTER COLUMN <!t>[" & strFld & "]<!/t> " & _
    "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

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ADO code needed (Any)

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

    Thanks again for your help.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO code needed (Any)

    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 <!t>[" & strTbl & "]<!/t> ALTER COLUMN <!t>[" & strFld & "]<!/t> " & _
    "COUNTER (" & lngSeedVal & "," & lngIncrementVal & ");"

    db.Execute strSQL
    db.Close

    Set ws = Nothing
    Set db = Nothing

    Example of use:

    daoResetAutoIncrementField "C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb","Table2","Reco rdID",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
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •