Change Access link from SQL Server to another Access file?

Question

I've seen lots of questions regarding moving data from Access to SQL Server, but I'd like to go the other route. Here's why:

I've been working on a sizable project with a SQL Server 2008 back-end and Access 2007 front-end. I'd like to be able to do some work on the front-end from home over the weekend, but I don't have access (VPN or otherwise) to SQL Server from there. I'd like to change my linked tables from SQL Server to another Access database file where I imported a snapshot of the SQL Server data. Then, come Monday morning, switch links back to SQL Server.

My problem is when I go to the Linked Table Manager and attempt to change the link, all I get is the ODBC Select Data Source dialog. If I try to link to an Access database, it tells me ODBC can't be used to link, import or export to another Access file.

One thought has occurred to me but I haven't tried yet; maybe someone could tell me if it's a good or bad idea: would I be able to delete the links, re-create them to the other Access file, and not lose any functionality in my queries/forms/reports?

Answer 1

For linking tables, you need to delete them and entirely and recreate them. Using the linked table manager to refresh ODBC links doesn't even work reliably when you're still using ODBC, as there is data cached in the table link definitions that doesn't get refreshed (e.g., if you change the number of columns in a SQL Server view, refreshing the table link with Linked Table Manager will update the number of columns, but you won't necessarily get an updateable view (assuming the original was updateable)).

But it's not clear whether or not you'll lose functionality or not. That all depends on how much of your application's logic is server-side, in SQL Server views and stored procedures. None of those will work if you link to a Jet back end.

Answer 2

My proposal would be to have SQLExpress (free) installed on your computer. You can then have all the data available on the machine. Create a publication on your main server, and have your local machine suscribe to this replication (if you don't need to save/synchronize the data changes done on your machine, you can stick to a basic 'snapshot' replication.)

You then just have to change your connection string from your network MSSQLSERVER to your localhost SQLEXPRESS server instance to have your app work.

If, for any reasons, you have to make changes to the database model while being off-line, you will then have to unsubscribe from the main server before making the changes on the local server. When you're back to the office, make sure that the same changes are done on the main server. My advice is to write your changes in T-SQL, save them in a file, and launch the file against the main server once you're back to work.

My opinion: don't work too much on weekends, or make sure your client is being billed for that.