We help IT Professionals succeed at work.

Macro ---Run Command

In my Access db I created 2 querys delete_table1_query and Append_table1_query I want to run this querys through macro --->open query----->delete and append query but its taking a long time to run this macro so how can I call my delete and append querys through RunCommand in macro ? I am not too familiar with VB Script so I would like to stick with macro

Note:above is just an example,in real there are total 12 delete and Append query and this Access db is sitting on a network drive.
Comment
Watch Question

LucasMS Dynamics Developer
CERTIFIED EXPERT
Commented:
Open up your Macro and add 2 lines:

OpenQuery  specify your delete query
OpenQuery   specify your append query
.
.
.
and so on.

You will have a problem because it is on the network.  you should split your db so that you have the forms and the interface on the local PC and your data on the network.  That would speed things up.
The RunCommand  will not improve the performance of the DELETE and INSERT queries.

The network is certainly an area to look, see what the difference is between running it on the network drive verses a local drive.

You need to look at improving the performance of the Queries themselves. This could be creating indexes on the tables so your selection can be optermized, however, the creation of indexes have an overhead when you are running INSERTS and DELETES.

Cheers, Andrew

Author

Commented:
Andrew,
I run the Macro(delete and append querys) in my local drive and its taking no more then 2 min where as in network drive the same macro is taking 15-20 min.
Scope, I want to make a copy of the 6 link tables coming from the production data so for example: Link table name: employee I created a UAT database in  network drive and created a table called employee_copy so every day when we get new data I am first deleting current data from employee_copy and then append latest data from employee (link table which is coming from the production data)

Any easy way out ?
Before going down that route force the opening of the database on the so it is exclusive adn see if it makes a difference. The easiest way is through teh File Open dialog and the Open is a drop down.
Cheers, Andrew

Author

Commented:
<<opening of the database on the so it is exclusive adn >> I dint get that can you please explain me what you are tring to say ?
<<File Open dialog and the Open is a drop down> ??
When you start Microsoft Access without loading a database go to the File Menu and select Open. Navigate and select your file from the network then rather than clicking on the OPEN button you will notice a drop down for the Open Button, if you select the dropdown there is an option to Open Exclusive.
Cheers, Andrew
Syd2008, has this resolved the issue?
Cheers Andrew

Explore More ContentExplore courses, solutions, and other research materials related to this topic.