Linking two databases to same SQL table

[kevlray]

This may have been answered before, but when I googled for answers, I did not come up with anything.

We have a case where there is an append query in one database that is is updating another database table. We have moved all the tables to MS-SQL (2008) using the SSMA tool. We were wondering if it would be possible to create a new linked table in the DB where the append query is. Of course we cannot use the SSMA tool again since the table already exists on the MS-SQL server.

Normally when I try to make linked tables from Access, it wants to create a ODBC connection which we are not using.

Any suggestions will be appreciated.

 

[Galaxiom]

If you want to link the table in SQL Server then you will need ODBC (or OLEDB).

Another alternative is to use a Stored Procedure to run the query on the Server. You can connect to the Server in code and tell it to run the SP.

Note that queries including joins between the Access database tables and the SQL Server tables will perform poorly.

 

[kevlray]

I do not know how the SQL Server Migration Assistant (SSMA) avoids creating an ODBC, but somehow it works. And since we have migrated staff over to Active Domain, it avoids the staff having to know any passwords (Windows Authentication) to open the DB.

 

[kevlray]

Update: Found code that should create the link, no errors when I run it, but I do not see the linked table either.

Option Compare Database

Sub getdata()
Dim strConnectionString As String
Dim strNameInAccess As String
Dim strNameInSQLServer As String

' set the connection string
strConnectionString = "ODBC;DRIVER=SQL Server; " & "SERVER=TCM1010\hhsadmin;DATABASE=PreEmployment-SQL;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 = "tbl_Candidate"

strNameInSQLServer = "dbo.Candidate"

' delete the table from the local database if it exists

On Error Resume Next


DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectionString, acTable, strNameInSQLServer, strNameInAccess
End Sub

Any idea what is going on?

 

[kevlray]

Solved: I had the wrong name for the SQL table name. Works now.