Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC/Linked Tables (Access 2000/2003)

    When I use PassThrough queries, I can in the ODBC Connect Str property paste ina string for a file DSN. This string can contain the UID and PWD so no user intervention is required to run the query.

    For Linked tables, how is the best way to edit this property. I have used the Linked Table Manager to connect the table using thefile DSN, but the UID and PWD are not included in the string.


    Thanks for your help.
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC/Linked Tables (Access 2000/2003)

    You can use VBA if you set a reference to the Microsoft DAO 3.6 Object Library. The TableDef object has a property Connect:

    CurrentDb.TableDefs("tblLinked").Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=secret;DSN=MyDS N;"

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC/Linked Tables (Access 2000/2003)

    You da man.

    I was just looking at that think that was going to be the way.
    Now I know it will work and the corret order for the arguments .


    Thanks for your help.
    Richard

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ODBC/Linked Tables (Access 2000/2003)

    For future reference, maybe, it should also be possible using ADO, but it's more work. This sample code connects a local MDB named TimeRpt6 with another MDB that can be anywhere on the network. It was written to run inside a Word template, so please ignore the non-Access bits (like the System object):
    <pre>Sub ChangeLinkedDBPathADO(strTable As String, strFolderPath As String)
    'valid for Access-to-Access linking ONLY
    'requires a reference to Microsoft ADO Ext. 2.x for DDL and Security
    StatusBar = "Updating database link"

    'grab path to TimeRpt6 database
    Dim strTRDB As String
    strTRDB = System.PrivateProfileString(TimeRptiniPath, _
    "MacroSettings", "TimeRpt6mdbPath")

    ' Open ADOX catalog on TimeRpt6 database
    Dim catDB As New ADOX.Catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strTRDB & ";"

    ' Inspect table LINK DATASOURCE property and reset as needed
    Dim tblDB As ADOX.Table
    Set tblDB = catDB.Tables(strTable)
    With tblDB
    If UCase(.Properties("Jet OLEDB:Link Datasource").Value) <> UCase(strFolderPath) Then
    .Properties("Jet OLEDB:Link Datasource") = strFolderPath
    End If
    End With
    Set tblDB = Nothing
    Set catDB = Nothing
    End Sub</pre>


  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC/Linked Tables (Access 2000/2003)

    I am using this code to try and update the CONNECT prorperty of the linked tables in a database.

    ' Loop through all the tables in the database
    For cntTbl = 0 To DocumentDB.TableDefs.Count - 1
    Set curTable = DocumentDB.TableDefs(cntTbl)
    ' If the table is a system object, a hidden object, or
    ' one of the documentation tables, ignore it
    If curTable.Connect <> "" Then
    curTable.Connect = "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=RAheron;PWD=nasa7USA;SERVER=FINAPP;;TA BLE=" & curTable.Name



    End If

    Next cntTbl


    The problem is, it is not changing the value.
    Do I have something in the wrong order?
    Is the string just WRONG for the property?

    Thanks for any help
    Richard

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC/Linked Tables (Access 2000/2003)

    The Connect property only specifies the database. The source table is specified by the SourceTableName property. Does this work? (I have no experience at all with connecting to Oracle):

    If ... Then
    curTable.Connect = "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=...;PWD=...SERVER=...;"
    curTable.SourceTableName = curTable.Name
    End If

    If that doesn't help, perhaps someone who does work with Oracle can jump in.

Posting Permissions

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