Programmatically creating linked tables in access

Question

Hi all, We need to find a way to programatically *link all the tables* in a SQL Server database to an access db. We will be invoking this access database from a program that uses .net/SQL Server 2008.

While invoking the application we would like to add the linked tables so that the users can just run the reports/modules from access without having to worry about linking the tables. Is there a way we can do this?

Thanks!

Answer 1

You can achieve the equivalent by using a Linked Server in SQL Server that points to the Access db. This will give you access to all the tables in the Access db so that you can reference them like:

Select ..
From [LinkedServerName]...[AccessTableName]

Btw, a linked server may be overkill for what you want. Look into the OPENROWSET function which effectively let's you pass a connection string.

EDIT: I originally read the question to literally mean "link tables in SQL Server to access" which I translated to mean from SQL to Access. So, given that, my solution would apply. However, if the desire is to go from Access to SQL, then that is different and other solutions presented would be more appropriate.

Answer 2

You would use ADOX to do the actual linking.

As far as enumerating the tables in a database you are connected to, you could do something as simple as running this query against your SQL Server, but there are a lot of ways to skin that cat:

SELECT * FROM INFORMATION_SCHEMA.TABLES

Answer 3

Hello,

You'll need an ODBC connection to the SQL database. Once this connection ready, you can use it for all tables that you want to link:

DoCmd.TransferDatabase acLink, _
                       "ODBC Database", _
                       myODBCconnection, _
                       myDatabaseName, _
                       acTable, _
                       myTableName

I guess you can declare your ODBC connector "on the fly", as proposed here for example.

To enumerate your tables, you have the following options:

• Enumerate them in the code: one transferDatabase line per table
• Save the table names in a local table, and browse the table
• Save the table names in a file (text, xml) anywhere on the network and browse the file
• Access the system table on the server that holds the table list, and browse the table
• Use the ADOX object to browse all tables in your database server: be carefull not to include system tables. This solution might be also quite confusing because you'll have to first open an ADODB connection to your database, and you'll then use an ODBC connection to open the tables

In all cases, this procedure shall be launched with the autoexec macro, meaning that links will be created\updated each time the user opens the mdb client.

Answer 4

Here are some notes.

Dim sLocalName As String
Dim tdf As TableDef
Dim rs As dao.Recordset

''This is a basic connection string, you may need to consider password and so forth
cn = "ODBC;DSN=TheDSNName;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=TheDatabaseName;"

''All fields from tables
strSQL = "SELECT TABLE_CATALOG, " _
& "TABLE_SCHEMA, " _
& "TABLE_NAME, " _
& "TABLE_TYPE " _
& "FROM [" & cn & "].INFORMATION_SCHEMA.tables " _
& "WHERE TABLE_TYPE = 'BASE TABLE'"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF
    sLocalName = rs!TABLE_SCHEMA & "_" & rs!TABLE_NAME 

    With CurrentDb
        If DLookup("Name", "MSysObjects", "Name='" & sLocalName & "'") <> vbNullString Then
            If .TableDefs(sLocalName).Connect <> cn Then
                .TableDefs(sLocalName).Connect = cn
                .TableDefs(sLocalName).RefreshLink
            End If
        Else
            ''If the table does not have a unique index, you will neded to create one
            ''if you wish to update.
            Set tdf = .CreateTableDef(sLocalName)
            tdf.Connect = cn
            tdf.SourceTableName = rs!TABLE_NAME
            .TableDefs.Append tdf
            .TableDefs.Refresh

            ''This will produce a message box if the table does not have a unique index
            ''DoCmd.TransferDatabase acLink, "ODBC Database", cn, acTable, rs!TABLE_NAME, sLocalName
        End If
    End With
    rs.MoveNext
Loop