Best option for getting data from MS SQL to Access

[cprot225]

I am a web developer who works plenty with SQL Server, and I really know very little about Access. But I have a project now that requires me to know something about it so I've been doing quite a bit of research.

Briefly: A definitive data set is kept in SQL server. An important user needs a copy of this data in her local copy of Access. Currently an antiquated process involving email and human intervention keeps this up to date (you don't want to know!) but that's becoming unfeasible very quickly. So my research on these forums and elsewhere has turned up a couple options and I need a qualified opinion to help me choose the best one, or suggest one I haven't even thought of. One caveat: I can't replace all of the user's access data indiscriminately because there are columns in Access not in SQL Server that she manages manually.

1) My first inclination: export updated SQL records once a day to an Excel file. That's easy. Then create a macro in Access to overwrite the affected records with new data.
-OR-
2) Do something with linked tables from SQL Server? I worry about overwriting the user's manually entered data with this option, but am still learning about it. Also, I don't want Access to be able to change data in SQL, but I think I can control this with user permissions, no?

Your opinions are welcome. Many thanks in advance.

[ADezii]

I am a web developer who works plenty with SQL Server, and I really know very little about Access. But I have a project now that requires me to know something about it so I've been doing quite a bit of research.

Briefly: A definitive data set is kept in SQL server. An important user needs a copy of this data in her local copy of Access. Currently an antiquated process involving email and human intervention keeps this up to date (you don't want to know!) but that's becoming unfeasible very quickly. So my research on these forums and elsewhere has turned up a couple options and I need a qualified opinion to help me choose the best one, or suggest one I haven't even thought of. One caveat: I can't replace all of the user's access data indiscriminately because there are columns in Access not in SQL Server that she manages manually.

1) My first inclination: export updated SQL records once a day to an Excel file. That's easy. Then create a macro in Access to overwrite the affected records with new data.
-OR-
2) Do something with linked tables from SQL Server? I worry about overwriting the user's manually entered data with this option, but am still learning about it. Also, I don't want Access to be able to change data in SQL, but I think I can control this with user permissions, no?

Your opinions are welcome. Many thanks in advance.

How about DTS (Data Transformation Services)? You can even create a DTS Package to periodically Transform the Data to a specific Access Database from SQL Server then run Action Queries within Access to Update/Delete/ Append, etc, the appropriate data. Your two Data Sources will be completely independent, you will not have to worry about Permissions, and the User cannot inadvertantly change Data within SQL Server. Since this important User needs just a 'Copy' of the Data within her Access Database, this may be a viable solution.

[cprot225]

How about DTS (Data Transformation Services)?

Thanks a lot for that great idea. I think I was getting hung up with the idea that the solution lay in Access itself, but I'm going to look more into DTS now and report back.

Other ideas or comments are naturally still welcome.

[jaxjagfan]

Is there a Primary Key for the data table?

Does the Access user maintain their data via a form currently?

The SQL table could be a linked table (user with READ rights). Displayed in a form. The user could edit and maintain his/her portion of the information in another table via a subform. No need for updates or worrying about changing each other's data. This also leaves you with a smaller mdb file if your database is continually increasing in size.

[ramdil]

Hi

I think you need to create a DSN name which connects access to sql
In access, then try to export the link tables which is the orginal tables in sql table..Then you can also create a local tables in access itself for your user input and every thing related with access.By this method ,you can get the latest values in sql server always available in access


Regards
Dilip