February 18, 2010 at 9:15 am
Hello,
I am running a script that I want to first delete all the data in 41 tables in an access db doing this from sql. I then want to insert records from my sql tables into these access tables.
Everything works fine but it takes a very long time to delete all the data in the tables. I even ran the script deleting table data only and then reran the script doing the delete only knowing that the tables were empty (0 records).
It took over 15 minutes to delete data from 41 tables totalling 1,349 records deleted.
It took over 12 minutes to delete data from 41 tables where there was no data in any of the tables.
Is there maybe something I should be doing differently?
This is one of my delete statements.
delete FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=y:\TESTING\TEST.mdb;')...[OperatorTbl]
Any assistance would be greatly appreciated.
*** I just realized that when the tables are empty it seems to take a very long time to run the script. I just ran the insert into the tables then ran the delete again and it took 8 seconds to delete the data. I must be missing something here!
February 18, 2010 at 9:29 am
Hi,
I'm assuming that the reason you're doing this is to provide a subset of your SQL data to the Access DB, which changes over time? If so, you'll be much better off creating views in SQL, then adding these to your Access DB as linked tables via ODBC.
No need to worry about deletes at all then.
Regards, Iain
February 18, 2010 at 9:37 am
We have a customer that requires their data to be available to them daily in an access db. Our data all resides in an sql db. I want to export their data to an access db that they can retrieve.
I am not familiar with using linked tables but will check it out.
February 18, 2010 at 9:38 am
I agree with irobertson.
But, that suggestion only works if the Access DB remains connected to the network. If the Access db goes off-site, then you don't have any data.
In this case I would still recommend running this from inside Access. From a master DB, use Access VBA to create a new DB from a template that already has the tables in it, but empty. Then use DoCmd.TransferDatabase command as links to copy the views irobertson mentioned into the empty tables, then drop the links. Done in about a minute. I used to do this all the time for 'field' work, new DB for each project, each day, so the data could be aggregated each evening from each crew.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply