Access Database- Linking to SQL Server Data Source

[BlueRhinos]

We're exploring the idea of linking our access database to a sql server database.

When we go down this route, the application gives us 2 options. Our thought is that option #2- Linking to a SQL Server Db by Creating a Linked table is the way to go.

However, the system goes on to say that Changes Made to the data in access will be reflected in the source data and vice versa.

Can someone help clarify this? I can't see how our changes in an Access db would effect a sql server source database.

 

[Norie]

Why wouldn't the changes be reflected in a linked data source?

It shouldn't matter if it's SQL Server, another Access DB or another datasource, in fact is kind of the whole point of using linked tables.

If you don't want the data to be able to be updated you'll probably need to look into some other approach.

 

[gaj104]

Hi,

We've recently moved all our tables to a SQL server.

When you link I link the tables over an ODBC connection we cannot update the table within access but strangely an append query works fine.

What I do is create a temporary staging table for appending. For updates I use VBA to create a connection and execute the SQL.

If you want to control the entry of data just set up permissions and schemas up i.e. db_datareader only. Best practise however is to create views and then link the views instead of tables.

Hope it helps

 

[james_lankford]

...
However, the system goes on to say that Changes Made to the data in access will be reflected in the source data and vice versa.

Can someone help clarify this? I can't see how our changes in an Access db would effect a sql server source database.

that's what linked tables are; not copies, but links, like links in a chain, connected; access is just a connection to sql server, access doesn't store any data, its just a front end that links your to your data;
but while you can change the data in access, you can't change the table structure

...
When you link I link the tables over an ODBC connection we cannot update the table within access but strangely an append query works fine....

do you have primary keys in all the linked tables ?
the reason access can't update linked tables is because it can't figure out which record to update if the table doesn't have a primary key; but if the linked table does have a primary key then access is able to update it

I don't think you can update linked views though; just like you can't update union queries

 

[xenou]

the system goes on to say that Changes Made to the data in access will be reflected in the source data and vice versa

All good comments so far. But what is "the system"? Are you using a specific conversion utility made for your application? Or something in Access? At any rate, the message is correct. Databases can generally connect to each other and do all kinds of things - the beauty of ODBC and other connection technologies. In SQL Server-Access integrations, it's usually SQL server that stores the data and Access that acts as an interface for users (Forms, Queries, Reports). It should be possible to control these connections sufficiently - via permissions, access rights, using read-only connections, etc. etc. As mentioned, one idea is connecting to SQL server via a query/view that is read-only, rather than directly linking tables, which would help to "lock down" the data from mishaps (we assume the data is in SQL Server in this case).

 

[gaj104]

do you have primary keys in all the linked tables ?
the reason access can't update linked tables is because it can't figure out which record to update if the table doesn't have a primary key; but if the linked table does have a primary key then access is able to update it

Never knew that. You're right, I had no primary keys. By adding a primary key it all updates. Thanks for the info.