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
 
Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
Rey Obrero (Capricorn1)

jjafferr

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
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
jjafferr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DavidE

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
rhlopez1

ASKER
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
Rey Obrero (Capricorn1)

you can do the relinking of your tables in the load event of your startup form
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rhlopez1

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rhlopez1

ASKER
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!
Rey Obrero (Capricorn1)

Yes
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy