Link to home
Create AccountLog in
Avatar of TheUndecider
TheUndeciderFlag for United States of America

asked on

Export Datatable Records to MS Access Database

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

ADOConn.Open()
 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)

AccessCommand.ExecuteNonQuery()
ADOConn.Close()

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?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of iboutchkine
iboutchkine
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of AkisC
AkisC
Flag of Greece image
I beleive that the 'exception error' is because your table exists in the new database
Try first: delete the table and try your code

If not successful try if this works...
Dim myConn as string="your SQL connection string"
Dim vSQL as string ="SELECT MY_Export.* INTO MY_Export IN ' C:\MyAccessDB.mdb' FROM MY_Export"
if RunMySQL(vSQL,myConn)
  'ok
else
  'failed
end if

    Public Function RunMySQL(ByVal thisSQL As String, ByVal ThisConnenctionString As String) As Boolean
        Dim RunOk As Boolean = True
        On Error GoTo errRunSQLHandler
        Dim conn As New System.Data.OleDb.OleDbConnection(ThisConnenctionString)
        Dim cmdP As New System.Data.OleDb.OleDbCommand(thisSQL, conn)
        cmdP.Connection.Open()
        cmdP.ExecuteNonQuery()
        cmdP.Dispose()
ExitRunSQLSub:
        If (conn.State = ConnectionState.Open) Then conn.Close()
        conn.Dispose()
        Return RunOk
        Exit Function
errRunSQLHandler: RunOk = False
        MsgBox("Action Faild(!)" & "Run SQL: " & vbCrLf & thisSQL & vbCrLf & vbCrLf & Err.Description & vbCrLf & "Function-> RunMySQL", MsgBoxStyle.Critical, "Action Failed(!)")
        GoTo ExitRunSQLSub
    End Function

Open in new window