Solved

Export Datatable Records to MS Access Database

Posted on 2008-06-16
3
1,084 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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