Link SQL Views to MS Access using VBA
How to link SQL Views and Tables to MS Access using VBA without getting the annoying Unique Record Identifier pop up.

Posted by David Wakefield on 26 June 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

MS Access to SQL Server linked tables using VBA

When linking an MS Access database to views in an SQL Server database you can only make the view updateable from Access if you choose a Unique Record Identifier (usually the primary key of an underlying table). Using the MS Access GUI this is easy, but sometimes you may want to re-link to the database using VBA.

Using TransferDatabase

This is the easiest method: it works fine as long as the SQL version of the table or view has a primary key setup.

' Some variable to make the code more generic

Dim strConnectionString As String

Dim strNameInAccess As String

Dim strNameInSQLServer As String

' set the connection string

strConnectionString = "ODBC;DRIVER=SQL Server; " & _

"SERVER=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes"

' specify the tables you want to link. The table can be

' known by a different name in Access than the name in SQL server

strNameInAccess = "tblYacht"

strNameInSQLServer = "tblSailingBoat"

' delete the table from the local database if it exists

On Error Resume Next

DoCmd.RunSQL "drop table " & strNameInAccess

On Error GoTo 0

DoCmd.TransferDatabase acLink, "ODBC Database", _

strConnectionString, acTable, strNameInSQLServer, strNameInAccess

 

If you are linking to an SQL table/view without a primary key you will see this dialog box pop up:

Unique Record Identifier

From this window you can select the field or combination of fields that uniquely identify a record.

 

The problem is how to write VBA to select the fields you want and dispense with this window. Quite simply it cannot be done using TransferDatabase.

Using DAO

The following code is slightly more long winded, but crucially it allows you to set the key field(s) in code:

' we will need to create this table using DAO

Dim tdf As DAO.TableDef

' Some variable to make the code more generic

Dim strConnectionString As String

Dim strNameInAccess As String

Dim strNameInSQLServer As String

Dim strKey As String

' set the connection string

strConnectionString = "ODBC;DRIVER=SQL Server; " & _

"SERVER=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes"

' specify the tables you want to link. The table can be

' known by a different name in Access than the name in SQL server

strNameInAccess = "tblYacht"

strNameInSQLServer = "tblSailingBoat"

' specify the key field

strKey = "SailingBoatID"

' Delete the table from the local database if it exists

On Error Resume Next

DoCmd.RunSQL "drop table " & strNameInAccess

On Error GoTo 0

' Create a table using DAO give it a name in Access.

' Connect it to the SQL Server database.

' Say which table it links to in SQL Server.

Set tdf = CurrentDb.CreateTableDef(strNameInAccess)

tdf.Connect = strConnectionString

tdf.SourceTableName = strNameInSQLServer

' Add this table Definition to the collection

' of Access tables

CurrentDb.TableDefs.Append tdf

' Now create a unique key for this table by

' running this SQL

On Error Resume Next

DoCmd.RunSQL "CREATE UNIQUE INDEX UniqueIndex ON " _

& strNameInAccess & " (" & strKey & ")"

On Error GoTo 0

I store the names of the tables and views I want to link to, along with their key fields in a local Access table. I then loop over the table re-linking without any user intervention.

This blog has 0 threads Add post