By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,864 Members | 1,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,864 IT Pros & Developers. It's quick & easy.

How to Create Local Tables From Linked Tables Automatically?

P: 2
Here is my situation. I have ODBC Rights to a SQL database but I have 4 users who need to get to this information but because they are contractors they are not allowed to get there own ODBC access.

So I wanted to create an Access Database using Linking Tables and then Create Local Tables from the Linking Tables. Then they would have a front end view to my newly created tables.

Does anyone know how to do this where the data will automatically update the tables will new data or just re populate the data with an event?
Jan 15 '08 #1
Share this Question
Share on Google+
4 Replies


P: 45
Here is my situation. I have ODBC Rights to a SQL database but I have 4 users who need to get to this information but because they are contractors they are not allowed to get there own ODBC access.

So I wanted to create an Access Database using Linking Tables and then Create Local Tables from the Linking Tables. Then they would have a front end view to my newly created tables.

Does anyone know how to do this where the data will automatically update the tables will new data or just re populate the data with an event?
Access sees all linked tables as local. So the question is what are you allowed to make visible to Access. You could have the SQL database periodically create an Excel report that is linked to tha Access database - everytime the report is updated the changes will be visible in the Access front end.
Jan 16 '08 #2

P: 2
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?
Jan 16 '08 #3

P: 12
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?
Would a possible solution be to import the tables into Access? This could be done on your workstation. If you want to ensure that they are seeing the latest and greatest, your would need to create a small app that run on your machine to open/delete tables/reimport tables. If you are using Windows, you could then schedule this app to run on a regular basis. You would need to build into your app that if the ldb file exist either skip the update or send a message to the users to exit the mdb.
Jan 16 '08 #4

jaxjagfan
Expert 100+
P: 254
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub myButton_Click()
  2.  
  3. Docmd.SetWarnings False
  4. 'Repeat for all tables needed
  5. DoCmd.OpenQuery "mydeletequery" 'if any deletes needed
  6. DoCmd.OpenQuery "myappend"
  7. Docmd.SetWarnings True
  8.  
  9. End Sub
  10.  
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.
Jan 16 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.