Trigger a Make Table Query in an External database

[music_al]

Hi,

I have a database which needs to link to a table in another Access Databse (tbl_Employees) which I have no control over. The other Access Database is my company's HR Database so some data in there is confidential. However, the IT Department have created a Make Table Query for me and the table that it makes (tbl_Employees_Light) is the table that I am linking to.

I want to be sure that the linked table in MY database is always up to date, so (without me asking the IT Department to refresh my query for me each day) how can I ensure that the data I am seeing is up to date or how can I force a refresh of the query that makes my table ?

I hope that makes sense.


Al

 

[namliam]

However, the IT Department have created a Make Table Query for me and the table that it makes (tbl_Employees_Light) is the table that I am linking to.

With all due respect, but WHAT BLEEPING IT DEPARTMENT COMES UP WITH THIS???



Surely they will have made a select query that you can use?? Instead of making a dumb make table??
If you can read the new table you can (probably) read the source too, assuming it is in the same db, which it sounds to be.

 

[DCrake]

Is there a reason why you cannot simply link the tbl_employees in the other mdb to yours. Then it will always be up to date.

Make table queries are cumbersome as you have to delete the prior one first and recreate. The table composition is not always maintained. Are the making the table from theirs into yours or theirs in to theirs then linking. All sorts of permutations. What you should be doing is creating the light table then doing an append after a delete. However you will have to contend with bloating.

also do you have read only access or do you want read write privilages. Needs further explanations before providing a suitable solution.

David

 

[music_al]

I cant link straight to it as there is data in there that they dont want me (or anyone else) to see, like Salary etc.

I will only be able to READ from the tbl_Employee_Light. I actually need to link to more than one table, namely

tbl_Employee
tbl_Role
tbl_Department

 

[namliam]

IMPOSSIBLE

You cannot run a create table without having access to the source table. Thus you will be dependant upon the "IT" department.

 

[DCrake]

Ok, that's fine. Who is going to be responsible for updating the ligh version of the tables? If you do not have access to the whole table or have the facility to access it then it must be the Owners of the mdb.

What you/they need to do.

Create a blank mdb in a shared location
Within that table create the tables that will contain the resulting data.

You:
In your front end link to these light tables and employ them accordingly.

Them:
A) Create a link to the same tables that you have in the light mdb

B) Create a macro/function that will
1. Delete the contents of all the necessary tables.
2. Append the up to date data to the linked tables.
3. Run a compact and Repair on the light mdb.

When the above procedure is complete you linked light tables will reflect the changes they have made during this routine.

The more often they run this procedure the more uptodate you will be.

David




a) delete

 

[music_al]

The Light table will NEVER have to be updated - just read from.

I had another idea. Maybe I can add some code that forces a Make Table (making a table in MY database) when the Source Table is saved. They can then just click OK to delete the old table and I will just make sure I compact my database frequently.

How does that sound ?

 

[namliam]

The Light table will NEVER have to be updated - just read from.

It will have to be updated each time the source changes no?

This update problem is here and here to stay, I would make it ITs problem to get it updated on a regular basis and make sure your own people know about this issue so any reporting errors are escalated to IT not you.

 

[music_al]

OK - I have sorted it. Here's what I did...

I created a Make Table query in the Source Database that creates a table in MY Database. I added a DoCmd.OpenQuery command to the Form.Close event that opens the Make Table Query.

As long as the user of the source database always clicks Yes to deleting the old table, it works fine. I just need to make sure I compact my database frequently.

Thanks for your help guys.

Al

 

[namliam]

I created a Make Table query in the Source Database that creates a table in MY Database.

Which means
1) YOu have access to the db
2) YOu have access to the source table
3) this whole thing is not needed.

 

[music_al]

No, you misunderstand. I have tested this idea offline before I go back to IT with the NEW solution as I wasnt happy with the INITIAL solution that created the table within their database. Creating the table in MY database and forcing them to Open the Query on close is a much better solution.

Going forward, I will not be able to get access to the Source Database, I am completely reliant on them clicking Yes when they see the attached dialogue boxes...

Its not a perfect solution, but it will work for me.

Thanks again for your input guys


Al

 

[namliam]

If you use Currentdb.Execute instead, they wont see the popups, thus ...

Still runing a make table each time a form is closed, can cause considerable slow down of the navigation. Depends offcourse on the "weight" of the query... but yeah... thats the thing.

 

[music_al]

Could you explain where I would use this Currentdb.Execute command ?

The query itself is only running over 200 records and only returns 3 fields. Its very lightweight.

 

[DCrake]

In response to the comment made in #11 the lighweight table doe snot reside in the IT database it resides in it own independant mdb, as explained in my earlier post. Its just that IT has this as a linked table that is flushed and reappended to each closedown or whenever required. Again you front end will also have a link to this lightweight back end. To this end any bloating only occurs in this mdb.

David