How can I link a SQL Server database to MS Access using link tables in MS Access?
Q: How can I link a SQL Server database to MS Access using link tables in MS Access?
Bryan Oliver says: Here’s how to use Microsoft Access to query SQL Server Data – check it out:
Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task, Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)
Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.
You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.
Linked server
You can add a Access database to a SQL Server database. Following steps are -
(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.
– OR –
Issue this statement in QA of SQL Server-
EXEC sp_addlinkedserver @server = ‘DBName’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’
You have added it as linked server now. Then, use full qualified name to issue your statements.
More Information on Access and SQL Server
Here’s two links to articles on our wiki and blogs:
- Using Linked Servers to Query Access Databases in SQL Server
- How can I synchronize data between MS Access and SQL Server databases?
August 23rd, 2007 at 6:30 am
We have found at our organization, that it is much easier to support DSN-less linked tables in access that don’t prompt the user for a password. The upside is that you don’t have to copy the DSN to workstations where other employees need to use the database.
Below is the code we use in VBA to create the linked tables. Just thought I would share.
Public Sub CreateLinkedTable()
‘ Used to hold tabledef object and connection string
Dim td As TableDef
Dim ConnString As String
‘ Used in connection string
Dim LocalTableName As String
Dim SourceTableName As String
Dim SourceServerName As String
Dim SourceDatabaseName As String
Dim Login As String
Dim SourcePassword As String
‘ Sets all variable values for connection string
LocalTableName = “MySQLTableLinked”
SourceTableName = “MySQLTable”
SourceServerName = “SQLServerName”
SourceDatabaseName = “SQLServerDBName”
Login = “Login”
SourcePassword = “Password”
‘ Creates connection string for the linked table
ConnString = “ODBC;DRIVER=SQL Server;SERVER=” & SourceServerName & “;DATABASE=” & SourceDatabaseName & “;UID=” & Login & “;PWD=” & SourcePassword
‘ Sets the table definition object
Set td = CurrentDb.CreateTableDef(LocalTableName, dbAttachSavePWD, SourceTableName, ConnString)
‘ Creates the table by appending to the current list of table definitions.
CurrentDb.TableDefs.Append td
End Sub
August 23rd, 2007 at 9:17 am
You can also link SQL Tables into an Access database without a DSN by using a little bit of VBA code and DAO (yes, good old DAO):
Public Function AttachTables()
Dim tdTemp As TableDef
Set tdTemp = CurrentDb.CreateTableDef("TableName")
tdTemp.SourceTableName = "dbo.TableName"
tdTemp.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection"
' without Trusted security, you can pass a user id and password instead:
tdTemp.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;uid=UserID;pwd=PassWord"
CurrentDb.TableDefs.Append tdTemp
End Function
August 23rd, 2007 at 9:18 am
The code got wrapped in the posting above, but the tdTemp.Connect is all one line up through the closing quotes.
November 20th, 2007 at 3:04 pm
When I try to set up this code in MS Access, I get an error message stating that TableDef is not a defined type: —————————
Microsoft Visual Basic
—————————
Compile error:
User-defined type not defined
—————————
OK Help
—————————
We are using Access 2002. If it is in v 2.0, it should work here. What’s going on?
December 2nd, 2007 at 5:32 am
Bill, check this out, see if it helps you.
December 9th, 2007 at 4:43 pm
Bill:
I’m probably making a foolish assumption, but do you have the DAO 3.6 library included in your references?
Derek.
December 17th, 2007 at 10:18 am
I did not create a backend and bascically copied my db to a shared network since the users are only running reports. I have 4 tables that are linked to another DB! Does every user have to create a link to these other tables? I used the AttachTables function and seems to work fine but if I try to open the tables from another users PC, I get an ODBC connection error! So I’m assuming that all users will have to create the link! And my last question is – how do I check if the table name already exists?
Thanks,
Tom
December 18th, 2007 at 5:09 am
If the users are using DSNs like Bryan recommends above, then each client PC will have to host the DSN.
August 16th, 2008 at 11:53 pm
It worked excellently. I had 100s of tables to be linked and certain times only few of them were to be used.
I created an additional table containing the names and properties of the sql tables and used this table in a loop to connect and index them as and when required.
September 17th, 2008 at 3:25 am
i successfully linked the tables but it is read only. is there a way i can link it with write access?
October 27th, 2008 at 9:24 am
Thanks a lot dude…. its working..
Srinivas
May 26th, 2009 at 1:39 pm
Ну так ведь без недостатков и достоинства не так заметны
May 31st, 2009 at 10:52 pm
Даа… Финиш… По-видимому пора бы расслабиться и отдохнуть