Export Datatable Records to MS Access Database
Posted on 2008-06-16
Hello, I have a datatable that is populated from a SQL 2000 stored procedure. I basically select * from mytable. What I need to do is to export or copy these records into an MS Access database. I don't need to edit any of the data. I only need to 'bulk export.' I am not supposed to use a DTS package though. Both the SQL table and the Access table have the same fields.
I'm trying to take another route in order to not use the datatable and try to copy the records from the SQL table into the Access table. This is what I am trying to do, but I get an exception error:
Dim ADOConn As New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source= c:\MyAccessDB.mdb;")
Dim SQLString As String
SQLString = "INSERT INTO MY_Export SELECT * FROM MY_Export IN Server=MyServer;database=MyDatabase;User ID=MyUser;Pwd=MyPassword;Persist Security Info=True;Integrated Security=sspi;"
Dim AccessCommand As New System.Data.OleDb.OleDbCommand(SQLString, ADOConn)
Do you have an idea on what I am doing wrong or should I abandon this altogether and try to use the datatable, loop through all the records there and try to export them to Access. If so, can anyone give me an example on how to acomplish this?