Append from Access
-
I would regularily like to append a large amount of data from a remote PC running Access 97 to SQL Server.
Currently I am using ODBC in Access with an Append query. Is there a faster way of doing this and if so do you have examples of VBA code I should use?
There are up to one million rows.
-
Probably something a bit more robust and controllable, would be to use a DTS package. It would run on the server and pull the data in to the database.
You could execute the package via a scheduled job on the server, or via VBA in Access. If you really wanted something to play with, you could create and run the DTS package in Access using VBA code.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
Thanks for this suggestion, and I may end up trying this in the longer term.
In the shorter term however, the Access program with the data to be uploaded is running on a remote PC on our WAN. I don't think it would be possible for SQL Server to see the PC therefore I have to do the programming from the remote PC.
-
We used to this do a lot across a slow WAN (4 T-1). It works, but does tend to be slow. Its not Access fault for the most part - slow link is a slow link. Couple things you can do to speed it up. One is do a pure append, not a join to SQL plus append. Another is to do it batches - sorry, this doesn't really affect speed, but it does let you see status as you go and if something fails, you don't have to start from row 1. For large appends we went with zipping the mdb and dropping on a server folder where a DTS package picked it up and did the import. Lots faster.
Andy
-
Andy, thanks.
I suspect that I might end up moving a zipped file and then using DTS but I guess might go down the zipped file route. I was hoping to avoid the manual steps. FTP, Unzip run DTS.
When you talk about "pure append, not a join to SQL plus append" I guess you mean use an append rather than update query. In Access I currently use an ODBC linked SQL Server table which I append to using an "insert into" statement in VBA.
I don't suppose there is a faster way of doing this using Pass-Through or ADO in Access
-
If its internal you could copy instead of FTP. Easy enough to put the client piece in a batch file or write a mini app to make it easy for the user. For Access, just appending to the linked table is fine, its when you add a join to make the insert conditional that incur (obviously) additional overhead. If you were to need that, better to insert to a staging table and then do the join on the server. Pass through or ADO won't help, you have to be able to see both data sources, Access does that good enough.
What you might want to do is compare how long the insert takes done server side, then try again remote - then you have a ballpark estimate of what the network overhead is.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply