Link to home
Start Free TrialLog in
Avatar of rhlopez1
rhlopez1

asked on

How do I refresh linked tables automatically on a time basis or with a command button programed to do so?

Good Morning,
My dilemma.  I've created a separate database (Special Program Database) which has 12 tables linked to another database (District Database).

My specific question is this:  How can I refresh the linked tables in the Special Program Database on a daily basis without doing it manually?  Can I use a command button programmed to run a macro? Not my first choice.  Or, can I program a script/code to run at a specific time during the night to refresh the tables?

I'd rather program a code so that users don't press the command button to try to refresh while others are updating/entering data.

Thanks in advance.

rlopez
 
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Hi rhlopez1,

Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

How to perform Seek on Linked Tables
http://www.mvps.org/access/tables/tbl0006.htm

Relink tables from different datasources
http://www.mvps.org/access/tables/tbl0012.htm

jaffer
Avatar of rhlopez1
rhlopez1

ASKER

Thanks, I reviewed all the forwarded htms-- however my "database programming language" is at a beginners level.  Can you be more specific with instructions on which option is best for what I am trying to do and how/where do I program the code?  My databases do not have any OBDC links.

Thanks
SOLUTION
Avatar of jjafferr
jjafferr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi rh

To understand your request:

Each day the users enter information into the District Database.
Special Program Database is linked to District Database and you wish to only view updates once per day.

I assume that Special Program Database has 12 queries which rebuild your working tables.

How about this? Run a windows scheduled event each night to make a copy of the District Database (as Copy DD) and have your Special Program Database link to the Copy DD and use the linked tables directly so that you do not need to run any rebuilds.

Or have I misunderstood your requirement?

You would only need to delete your working tables and then rename the linked tables and your system should work as now.

David
David,
Thanks for the response.  Your recommendation would probably work however I would be back to "square one".  I would have to do just as much manual work to update the tables--perhaps more-- than I would just refreshing the link manually.

As it is now, the only thing I have to do is, using the "Link Table Manager", press the "Select All" command button, and the "OK" button--- and, all the tables are refreshed.

What I am attempting to do is automate this procedure so that I don't have to do it manually.

Thanks
you can do the relinking of your tables in the load event of your startup form
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.
Question: If I program it in the "On Load" event of my startup form will it not run every time a user opens the database and the startup form loads?  The datase (Special Program) will be used by approximately eight users.  My concern is what will happen when a user is data entering and another users logs on and the linking process occurs.

Am I concerning myself with something that is not going to happen or that does not interfere with the ongoing use of the database??

Thanks
   
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great!
I was under the impression that despite the link the user still had to update (refresh) the linked tables periodically.  If this already occurs automatically then I'm chasing a ghost and creating needless work.

For clarity sake: You are telling me that as data is entered in the District Database the linked tables in the Special Program Database, without having to do anything, are updated.  If the anwer is "yes" then my problem is solved!