Solved

Export Datatable Records to MS Access Database

Posted on 2008-06-16
3
1,081 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:TheUndecider
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 500 total points
ID: 21801772
The easiest way of doing it is to link SQL Server table to Accesss and then write the query in Access

Also you can use dataadapter

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim da1 As New SQLDataAdapter("select * from tabel1", con1)
        Dim da2 As New OleDbDataAdapter("select * from tabel2", con2)

        da1.AcceptChangesDuringFill=False
        da1.Fill(ds)

        Dim db As New OleDbCommandBuilder(da2)
        da2.Update(ds.Tables(0))
    End Sub
0
 
LVL 11

Expert Comment

by:AkisC
ID: 21802026
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

0
 
LVL 11

Expert Comment

by:AkisC
ID: 21802035
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Events in static methods 3 50
POWERSHELL: do / until, should continue even when there is an error 4 54
Vb.Net Date Formatting Assistance 4 36
Help with Syntax 9 28
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now