Solved

Export Datatable Records to MS Access Database

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Want to create a userform that looks like the image provided 10 63
Regex validation 2 25
Winform Module - What is the ASP.Net equiv 2 23
No Data for DropDown List 2 26
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…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

803 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