Copy a DataSet/DataTable to an Access Database.
Posted on 2004-10-11
Hi there... I'm having a very hard time trying to find a direct, fully-programatic way to select data from a table in SQL Server and copy it into a table in Access. There are a million "work-arounds" out there, but none of them are very efficient options. I have exhausted the options available using ADO in VB, and since this process will eventually be written in VB.NET, I figured I'd try this angle.
So here's my scenario... I open a connection to SQL Server (using an OLEDB con in .NET) I select the contents of a table (or perform any kind of SELECT query) and populate a DataTable/DataSet with the results... I then open an OLEDB con to an Access DB. I now have a DataTable (or DataSet) full of data that I want to drop directly into a new table in the Access DB.
So how do I do this?
P.S. The most common "solution" I've found up to this point involves looping through every single record and writing them to a table/text file/whatever... that's not an efficient option in my case, since I will be working with so many records.