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.
Syd2008Asked:
Who is Participating?
 
TextReportConnect With a Mentor Commented:
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
0
 
LucasConnect With a Mentor MS Dynamics DeveloperCommented:
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.
0
 
Syd2008Author 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 ?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TextReportCommented:
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
0
 
Syd2008Author 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> ??
0
 
TextReportConnect With a Mentor Commented:
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
0
 
TextReportCommented:
Syd2008, has this resolved the issue?
Cheers Andrew
0
All Courses

From novice to tech pro — start learning today.