That still doesn't help my situation. Because I have the only ODBC connection to the SQL database. If I create a front end database view to my back end access database view. It still tries to look for the ODBC connection and then fails.
So what I'm thinking is to have an automated process that will run a make table query on my linking table data. Then their front ends will point to those tables. Has anyone ever done something like this before?
You will have to run this from your db.
In your database you will link to SQL tables (already done i suppose). In your database create the local tables via a make table query.
Create a new db for the contractors. Import the tables from your db you created with the make table query as well as the other objects (forms, queries, reports, etc). You will have to rename the tables to match the linked names as they are in your database - if you have objects that rely on table names (forms, queries, etc).
Delete the local copies of the tables you have in your db and now link to the same tables in the contractors db.
If you are just appending new recorrds to the contractors db then create a query to do so.
If you are replacing the data in the contractors db then you will need a delete query to delete the data. Change the original make table query (make sure it is pointing to the linked contractors tables since changing names) to an append query.
Once the contactors db is built you only have to update it.
For simplicity you can create a command button:
-
Private Sub myButton_Click()
-
-
Docmd.SetWarnings False
-
'Repeat for all tables needed
-
DoCmd.OpenQuery "mydeletequery" 'if any deletes needed
-
DoCmd.OpenQuery "myappend"
-
Docmd.SetWarnings True
-
-
End Sub
-
FYI - Your SQL DBA's can link to an Access DB with DTS and update the contractors copy via their daily scripts much easier and faster.
2
