Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    New York, NY USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using VBA to Link a Table in Access (Access 2002)

    I want to use my front-end forms with a choice of data tables that I want the user to select when the applicatin opens. when the user selects an .mdb file, I want to link several table from that .mdb file. How can I do this in VBA? So far I've tried ADOX and DAO with no success. Any suggestions? Thanks.

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

    Re: Using VBA to Link a Table in Access (Access 2002)

    Do you wany to allow the user to select a particular database as the back end when the application opens? If so, and if you want to use bound forms, you will need to use DAO recordsets, which means you will need to attach the tables in the back end to the front end. You can create the links using DAO or ADO, although DAO would probably be simpler for you. Unless you're planning to link all the non-system tables from the selected database, you will need a table in your front end that holds the names of the tables to be linked. Then you can use a recordset based on that table in creating the links. Are you removing existing links in doing this? If so, don't forget to delete the existing tabledef before trying to link another with the same name. You will need to step through a list of tables to link and individuallly create the link. You can create a simple link using the DoCmd.TranfserDatabase statement like this:

    <pre>DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, strTable, strAlias</pre>


    This will create a link from the database path and filename specified in strPath from the string value in strTable to the current database with the name provided in strAlias

    If you provide more details about exactly what you are trying to do, someone will be able to help you with the specifics.
    Charlotte

  3. #3
    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: Using VBA to Link a Table in Access (Access 2002)

    When changing a linked table path in an MDB from Word VBA, I have used the following code. Please note that I have NO IDEA WHATSOEVER whether these are helpful inside Access, but maybe they'll come in handy for some point or another.

    <pre>Sub ChangeLinkedDBPath(strTable As String, strFolderPath As String)
    'connection string format valid for Jet databases ONLY
    Dim dbTimeRpt As DAO.Database
    Set dbTimeRpt = OpenDatabase(System.PrivateProfileString(TimeRptin iPath, _
    "MacroSettings", "TimeRpt6mdbPath"))
    With dbTimeRpt
    .TableDefs(strTable).Connect = ";DATABASE=" & strFolderPath
    .TableDefs(strTable).RefreshLink
    End With
    dbTimeRpt.Close
    Set dbTimeRpt = Nothing
    End Sub

    Sub ChangeLinkedDBPathADO(strTable As String, strFolderPath As String)
    'valid for Access-to-Access linking ONLY
    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>

    My recollection is that DAO was faster, but I switched to ADO because it was the wave of the future. Now, I guess I'm supposed to learn ADO.NET...

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

    Re: Using VBA to Link a Table in Access (Access 2002)

    For stuff like this, DAO generally is faster, and that code should work inside Access as well except for the PrivateProfileString calls which are looking for an ini file and so may not apply in this case.
    Charlotte

Posting Permissions

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