Updating linked tables. 1

[Layth]

I have an access database that has multiple linked tables. One query pulls out all of the information I need, I then turn that query into a make table query and query the subsequent table. From there I use other queries to count records, manipulate records mathematically based on their fields, and merge back together. The end result is a report with the fields from the original query and new fields that I have designed based on the specs of our customer.

This has worked great, with no problems but one. The linked tables are updated every day. In order for me to update the access database I have to double click each make-table query to update that table. But before I can do that I have to delete the relationships to that particular table or I will get an error message.

So for each table I have to:
1. Delete the relationships going to that particular table.
2. Activate the Make-Table query to make the new table.
3. And redo the relationships to the updated table.

I have about 30 make-table queries and they have to be updated in a certain order, it takes about an hour to do.

My question is: Is there anyway to make this process automatic.


Any help is greatly appreciated!


thanks,

Layth

 

[GingerR]

How about this instead: instead of a Make Table query, use DELETE and APPEND queries? Change all of your Make Table queries to APPEND queries. In fact, you don't even have to write a DELETE query, you can just do it in code. You can list all of your queries in a vba module, then either run it manually (you just click RUN) or call it from an "AUTOEXEC" macro which you can run when the db opens (at 4 am while you're sound asleep


Make a new code module and put in some code sort of like this:

Function RefreshData()
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "Delete * from Table1NameHere"
    Docmd.OpenQuery "AppendQuery1NameHere"

    DoCmd.RunSQL "Delete * from Table2NameHere"
    Docmd.OpenQuery "AppendQuery2NameHere"

    DoCmd.RunSQL "Delete * from Table3NameHere"
    Docmd.OpenQuery "AppendQuery3NameHere"

    Msgbox "Done!"
    DoCmd.SetWarnings True

End Function

hit File+Save.

Select the first row (Function Blah()) and hit RUN.

Hope this helps!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Layth]

Thanks for the help, I'm not really familiar with append queries, I think this is what I want to do, but if you could elaborate I think I could use this method more clearly.

thank you for your help.

Layth

 

[GingerR]

Check out a text book or MS Access help as it's a basic feature. Basically, on the toolbar, pick APPEND instead of MAKE TABLE.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Layth]

Alright, this is very cool, one more question How do I get this to immediately update in vba when someone opens this access database, or perhaps have the update linked to a command button.

THANK YOU!!!

Layth

 

[GingerR]

from previous post:

or call it from an "AUTOEXEC" macro which you can run when the db opens (at 4 am while you're sound asleep

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SeeThru]

Run the command on OnOpen of a form?

 

[GingerR]

Look up "Autoexec Macro"

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Layth]

Actually, I figured this out a few days ago, sorry I havn't written back, my whole database runs like a dream, Thanks so much!!!

Thanks again,

Layth