CODEWITHSUNDEEP
sql-serverms-access
techindustry
techindustry

Reputation: 23

How to link Access table in SQL Server

I have a SQL database and I want to link an Access table in the SQL database. Most of the online articles and videos show how to link the two databases from Access but not from SQL. I want the access table linked in SQL database so that every time the table gets updated in Access, the table in SQL server gets updated as well. Thanks!

Upvotes: 0

Views: 3107

Answers (2)

Gustav
Gustav

Reputation: 55806

You can do that. It's called a Linked Server and will link your Access database in SQL Server where you typically will create a view to read the Access tables to avoid the DatabaseName..TableName syntax otherwise needed.

However, it will probably not work for you because SQL Server will insist on opening the linked Access database exclusively, meaning that you cannot open it later from your Access application. And if you open it first in Access, an attempted reading from SQL Server will fail because SQL Server cannot get exclusive access.

You may argue, that a shared access should be possible, and connection parameters indicate this, but I haven't found a way to achieve this.

So, as others have mentioned, you may have turn this upside/down, and use, say, ODBC to link the SQL Server tables in your Access application, and then let this take care of the maintenance of the SQL Server tables.

Upvotes: 2

Krish
Krish

Reputation: 5917

It must be like this (SQL SERVER) => (MS Access)

and yes you are correct, it's called linked tabled in Ms Access. In order to link a table from another database, you must have

  1. Correct drivers installed. (ODBC)
  2. Know the login details of your SQL server and have sufficient permission in SQL server

Go to MS Access -> External data -> ODBC Databse -> link to the data source by creating a linked table ->

Now you can select a DSN file where your sql server credentials are saved, or you can make "new" and follow the steps to get connected to the SQL server.

Once connected, you will be presented with a list of all the tables from SQL server. Select the table you want to link and finish the wizard.

You now have SQL table connected in MS Access and can input data directly.

Upvotes: 0

Related Questions