Link Table In Access 2000

VB GURU · May 3rd, 2004, 12:27 AM

Can i create linked tables in Access 2000 from SQL Server 2000, Oracle And VFP. Programatically. waitting.........

Steven · May 3rd, 2004, 05:45 PM

Yes, Guru, you can.

via ODBC

I am a loud man with a very large hat. This means I am in charge

Steven · May 3rd, 2004, 05:46 PM

Quick note to everyone else:
Before you go on about using ADO, note that the post said "linked tables"

I am a loud man with a very large hat. This means I am in charge

wscheiman · May 5th, 2004, 08:03 PM

Because this uses ADOX to link the table, be sure that "Microsoft ADO Ext 2.7 for DDL and Security" is checked ON as a reference. To verify this, go into your VBA editor and select Tools | References from the menu bar.

Here's how to link an table from an ODBC DSN using ADOX:

Public Function Link_ADO_DSN()
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table

    Dim strODBC_DSN As String
    Dim strODBC_TableName As String
    Dim strODBC_UserID As String
    Dim strODBC_Password As String

    strODBC_DSN = "ABC_Company"
    strODBC_TableName = "CUSTOMER"
    strODBC_UserID = "acct_receiv"
    strODBC_Password = "accounts"

    ' Set the ADO connection
    cat.ActiveConnection = CurrentProject.Connection

    ' Set the name and target for the table
    tbl.Name = strODBC_TableName
    Set tbl.ParentCatalog = cat

    ' Set the properties for the linked table
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Provider String") = _
                   "ODBC;DSN=" & strODBC_DSN & ";" & _
                   "UID=" & strODBC_UserID & ";pwd=" & strODBC_Password & ";"
    tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
    tbl.Properties("Jet OLEDB:Remote Table Name") = strODBC_TableName

    ' Append the table to the collection
    cat.Tables.Append tbl

    Set tbl = Nothing
    Set cat = Nothing

End Function

Steven · May 6th, 2004, 12:19 AM

That's a fair bit of code now isn't it?
Maybe something like:

Code:

DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors"

would suffice

I am a loud man with a very large hat. This means I am in charge

blinner · January 24th, 2006, 09:39 AM

now i know why the aussie's won the world series of poker

Leonard777 · March 16th, 2006, 06:22 PM

What about linking to another secured Access Database. I cannot find how to include the password so that it does not prompt me for each table I link to?