Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export Datatable Records to MS Access Database

Posted on 2008-06-16
3
Medium Priority
?
1,088 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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