MS Access VBE Routine is slowing down as time goes by

I have 200 Sharepoint tables, and want to download them all in MS Access. I do that programatically in VBE by downloading one table at a time by using the DoCmd.TransferSharePointList Method in a loop.

I noticed that after the 50th table, the routine becomes extremely slow.

I am worried I might be using the DoCmd.TransferSharePointList Method incorrectly. Do I need to close out something before I open the next table? Am I causing an overflow (such as a for without next, or Open without Close, or PUSH without POP)?

What command do I need to give to close out before opening the next table?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I wouldn't do the download at all.  Just link to the SharePoint tables and use them that way.

Are you compacting the database after every set of downloads? Access does not recover deleted space so deleting all rows and downloading new rows simply doubles the size of the table.  The new records do not occupy the space vacated by the deleted rows.  Only a compact can recover the empty space.
Member_2_7966563Author Commented:
The reason I am downloading is because finally, I need the content of all 200 tables (similarly structured) in one final table. So I download, append to the main table, and then delete the downloaded table. Then I move to the next table.

If I dont download, and leave all 200 tables linked, I will need to create a UNION query that combines all 200 tables. Do you think Access will be able to handle a long query like SELECT * from Table 1 UNION SELECT * from Table 2 SELECT ............ from Table 200?

Space does not seem to be the problem. If I do the download in 4 parts (say 50 tables at a time, and then restart Access), everything goes fine, even if I dont compact the database.
Dale FyeOwner, Developing Solutions LLCCommented:
So these are 200 SharePoint lists with the same structure?
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

You may not be able to get such a large union query to work.  You can break unions and nest them so union tables 1-10 in qry 1, 11-20 in qry2, etc,  Then union qry1, qry2, etc.  You might need to break this differently and I dont' know if it will ever work So, importing may be the better choice.

There is no need to download the data table by table.  To simplify the process link to each table, one at a time.  Assign the same table name  so you only need one append query.  

Loop through list of SharePoint tables (make a table if this makes it easier)
    Link to the table - name it SP_Link
    Run append query to append from SP_Link to your local table
Member_2_7966563Author Commented:
Dale, sorry for the late reply. But yes, these are 200 Sharepoint lists with the same structure.
Member_2_7966563Author Commented:
Pat, I will try and let you know shortly
Dale FyeOwner, Developing Solutions LLCCommented:
I think Pat's recommendation of adding the names of the lists to a table, then looping through the recordset, linking to the list, appending to your table, then dropping the link and linking the next list is probably the best way to go.

However, I would keep track of which list each record came from in your master table.  You could do this with a LinkedListID field in your table of links.  Then you would have that field, as well as the ID field from each list to identify each record, individually.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.