We help IT Professionals succeed at work.

How do I transfer one MS Access table into another MS Access Database using ADO/VB.NET

1,233 Views
Last Modified: 2012-06-21
Hello, here is my code; I have sucessfully made the two connection strings, dataAdapters etc...I also made a dataset which fills into a list box which gives me a visual confirmation that I am receiving data into my dataset from my first database connection string.  

The problem is I can't figure out for the life of me how to transfer that data into the other database.  I see alot of code out there but none of which I see is viable; i.e. it seems to be "Work arounds".

I got a majority of this figured out; I think; I just need to get the data from one database and put it into another Microsoft Access Database.

Thank you for what ever help any of you can offer.


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Databases\SourceDatabase\.mdb"
        Dim strDest As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Databases\DestDatabase.mdb"
        Dim sqlStr As String = "SELECT * FROM site"

        ' Create connection object
        Dim conn As OleDbConnection = New OleDbConnection(conStr)
        Dim conn2 As OleDbConnection = New OleDbConnection(strDest)

        ' Create data adapter object
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
        Dim da2 As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn2)

        ' Create a dataset object and fill with data using data adapter's Fill method
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "site")

        ' Attach dataset's DefaultView to the datagrid control
        Dim dv As DataView = ds.Tables("site").DefaultView
        ListBox1.DataSource = dv
        ListBox1.DisplayMember = "sitename"

    End Sub
Comment
Watch Question

CERTIFIED EXPERT

Commented:
do you want to copy the whole database or just a table to a database?

Author

Commented:
Hello, and thank you for responding to my request.  I want to really copy two tables however if I can figure out how to copy one then I can do the other.  So for now I want to copy one table from database A and put that table into database B.

More information here.  I have 54 databases with hundreds of tables and only two I need; I want consolidating them into one database so I can easily move it into SQL or simply just do reports in Access.  I figure I should be looking at SQL statements like 'Append' or 'Insert' the tables into a master table.  

I have not been able to organize in my mind the method but I am figuring that I would have to write a query for the INSERT transaction or some more complex code which I am not that good yet to be able to do.  I do have quite a bit of VB experience though.

Is this enough information for you or more confusing?
CERTIFIED EXPERT

Commented:
here is some code that you can look at.
you may need more. just let me know.
#Region "Make a Table to a Database"
    Public Sub FillTable(ByVal sNewTable As String, ByVal sOldTable As String, ByVal sDBName As String)
        sSql = "INSERT INTO " & sNewTable & " IN " & sDBName & " SELECT FieldName1, FieldName2, FieldName3, FieldName4, FieldName5 " & _
               "FROM " & sOldTable & " IN " & sDBName
        RunSQL(sSql)
    End Sub
    Private Sub RunSQL(ByVal strSQL As String)
        Dim Cmd1 = New OleDb.OleDbCommand(strSQL, Con)
        Try
            Cmd1.Connection.Open()
            Cmd1.ExecuteNonQuery()
            Cmd1.Connection.Close()
        Catch exp As OleDb.OleDbException
            Dim errorMessages As String
            Dim i As Integer
 
            For i = 0 To exp.Errors.Count - 1
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                               & "Message: " & exp.Errors(i).Message & ControlChars.Cr _
                               & "NativeError: " & exp.Errors(i).NativeError & ControlChars.Cr _
                               & "Source: " & exp.Errors(i).Source & ControlChars.Cr _
                               & "SQLState: " & exp.Errors(i).SQLState & ControlChars.Cr
            Next i
 
            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
            log.Source = "My Application"
            log.WriteEntry(errorMessages)
            MessageBox.Show("An exception occurred. Please contact your system administrator.")
        Catch exp As Exception
            Cmd1.Connection.Close()
            ''  MessageBox.Show(exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub
#End Region
#Region "Copy a Table"
    'USE CopyTable("D:\athos.mdb", "client", "clienttest")
 
    Public Sub CopyTable(ByVal DBPathName As String, ByVal TargetTable As String, ByVal NewTableName As String)
        Dim cmd1 As New OleDb.OleDbCommand("SELECT " & TargetTable & ".* INTO " & NewTableName & " IN '" & DBPathName & "' From " & TargetTable)
        cmd1.Connection = Con
        Con.Open()
 
        Try
            cmd1.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
#End Region

Open in new window

CERTIFIED EXPERT

Commented:

Author

Commented:
Hello, thank you for you help; I do see and understand your example and I am working with it; now I also viewed the link you sent me and this is exactly what I am tyring to avoid; making a statement for every field.  If I have to do that I will; but I can't beleive a perosn can just do this in two queries in ADO while using a DataSet.

However, so we are on the same page; the example you gave here does not inculde a dataset; instead it is a query based solution making use of command objects.  Correct?
CERTIFIED EXPERT

Commented:
Yes, I have not looked into the dataset side. but I do agree that you would think a easy dataset fill and copy to a new table would be easy. i haven't tried it , but we will see.

Author

Commented:
Hello, thank you again for your response.  I would agree it should be easy to just dump a dataset into a table i.e. 'Append' or 'Insert' (the same thing).  But what is stupid on the .net end is they make it easy one way but not going the other.  I think you would agree that I have getting the data out of the database although I could have put the query in a CommandText but filling the table and displaying it is the easy part.

Now do I have list out every single field/column and insert?  I don't think you have to do that; I think their is a much easier way.  I can't believe more people have not done this before.  In fact when I search the internet I am finding I am not the only person to have this problem; in fact their is almost no 'EASY' solution for Appending data from a dataset from one database to another database.  I am for the first time stumped.
CERTIFIED EXPERT

Commented:
I found this code for you to look at...

 Private Sub AccessToAccess(ByVal sourceFile As String, ByVal sourceTable As String, ByVal targetFile As String, ByVal targetTable As String)
 
        'Sub transfers all records from ..mdb sourcefile.sourcetable ...
        '... to .mdb targetfile.targettable
        'It is assumed that the .mdb targettable definition already ...
        '... exists, with the same number and types of fields, ...
        '... in the same order, as the .mdb sourcetable fields.
        'It does not depend on the field names being the same ...
        '... in the source and target tables.
 
        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";User Id=admin;Password=;"
        Dim sourceSQL As String = "SELECT * FROM " & sourceTable
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable
 
        'use dataadapter for target and command builder to ...
        '... create insert command, including parameter collection
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand
 
        'now do the work
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 0 To sourceReader.FieldCount - 1
                'load values into parameters
                targetCommand.Parameters(i).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next
            '... or here
            'then write to target
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()
 
    End Sub

Open in new window

CERTIFIED EXPERT

Commented:
OK I findly found the code that we where looking for that uses datasets thanks you EE RonaldBiemans:
Check it over and test your data to see if it will work.
 'Ron used 2 access databases db1, db2 as you can see in the connection
    ' strings with identical tables called tabel1 in db1 and tabel2 in db2,
    ' Ron added to oledbdataadapters (da1,da2) 
 
    Dim con1 As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
            "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\" & _
            "db1.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
            " OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
            "ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
            "t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
            "Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
            "e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
 
    Dim con2 As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\" & _
        "db2.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
        " OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
        "ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
        "t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
        "Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
        "e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
 
    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 OleDb.OleDbDataAdapter("select * from tabel1", con1)
        Dim da2 As New OleDb.OleDbDataAdapter("select * from tabel2", con2)
 
        da1.Fill(ds)
 
        Dim db As New OleDb.OleDbCommandBuilder(da2)
        da2.Update(ds.Tables(0))
    End Sub

Open in new window

Author

Commented:
Hello, O.K here is what I tried with my input informaiton; From what I see this should work and I don't know what I am missing here but it does not.  However, notice I put in the listBox2 which verifies that I am grabbing data from the connection string 1 (con1).

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Setting the connection string to the type and path(s) of the databases
        Dim con1 As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Databases\AK\AKUM40.mdb"
        Dim con2 As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Databases\MasterDatabase\MasterDatabase.mdb"

        'Setting up a new dataset
        Dim ds As New DataSet
        'Create dataAdapters
        Dim da1 As New OleDb.OleDbDataAdapter("select * from site", con1)
        Dim da2 As New OleDb.OleDbDataAdapter("select * from site", con2)

        'Fill the dataset with the results of the queries form the dataAdapters getting t
        'the informaiton from the tables
        da1.Fill(ds, "site")

        'This is the command creates a new instance of db using the command builder and (da2)
        Dim db As New OleDb.OleDbCommandBuilder(da2)

        'This I beleive should tell the da2 dataAdapter to update the table in the second connection string
        'with the dataset created from the first
        da2.Update(ds.Tables(0))

        'This is just another list box to help me make sure the infomraiton is
        'being pulled form the connection string
        Dim dv As DataView = ds.Tables("site").DefaultView
        ListBox2.DataSource = dv
        ListBox2.DisplayMember = "sitename"

    End Sub
End Class
CERTIFIED EXPERT

Commented:
Ok, I found the problem in that the insert command would error.
So I added this new code and now the moving in the tables works.
Also I added some code in the conection strings so you will need to copy in all of the snippet.
   Dim con1 As String = _
           "Jet OLEDB:Global Partial Bulk Ops=2;" & _
           "Jet OLEDB:Registry Path=;" & _
           "Jet OLEDB:Database Locking Mode=1;" & _
           "Jet OLEDB:Database Password=;" & _
           "Data Source=C:\temp.mdb;" & _
           "Password=;" & _
           "Jet OLEDB:Engine Type=5;" & _
           "Jet OLEDB:Global Partial Bulk Ops=2;" & _
           "Jet OLEDB:Global Bulk Transactions=1;" & _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Jet OLEDB:System database=;" & _
           "Jet OLEDB:SFP=False;" & _
           "Extended Properties=;" & _
           "Locale Identifier=1033;" & _
           "Mode=Share Deny None;" & _
           "Jet OLEDB:New Database Password=;" & _
           "Jet OLEDB:Create System Database=False;" & _
           "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
           "Jet OLEDB:Compact Without Replica Repair=False;" & _
           "User ID=Admin;" & _
           "Jet OLEDB:Encrypt Database=False"
    Dim con2 As String = _
        "Jet OLEDB:Global Partial Bulk Ops=2;" & _
        "Jet OLEDB:Registry Path=;" & _
        "Jet OLEDB:Database Locking Mode=1;" & _
        "Jet OLEDB:Database Password=;" & _
        "Data Source=C:\temp2.mdb;" & _
        "Password=;" & _
        "Jet OLEDB:Engine Type=5;" & _
        "Jet OLEDB:Global Partial Bulk Ops=2;" & _
        "Jet OLEDB:Global Bulk Transactions=1;" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Jet OLEDB:System database=;" & _
        "Jet OLEDB:SFP=False;" & _
        "Extended Properties=;" & _
        "Locale Identifier=1033;" & _
        "Mode=Share Deny None;" & _
        "Jet OLEDB:New Database Password=;" & _
        "Jet OLEDB:Create System Database=False;" & _
        "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
        "Jet OLEDB:Compact Without Replica Repair=False;" & _
        "User ID=Admin;" & _
        "Jet OLEDB:Encrypt Database=False"
 
    Private Sub Move_datsets()
        Dim ds As New DataSet
        Dim ds2 As New DataSet
        Dim da1 As New OleDb.OleDbDataAdapter("select * from table1", con1)
        Dim da2 As New OleDb.OleDbDataAdapter("select * from table1", con2)
        da1.AcceptChangesDuringFill = False
 
        da1.Fill(ds)
        ds2.Merge(ds)
        Dim db As New OleDb.OleDbCommandBuilder(da2)
        db.QuotePrefix = "["
        db.QuoteSuffix = "]"
        da2.Update(ds2.Tables(0))
    End Sub

Open in new window

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hello, Thank you again for this code when I execute this for now I am getting an error stating that I have duplicate values in my index.  So I am checking my table(s) now and reviewing that.  I might even put a list box up to see what kind of data is comming up.

Author

Commented:
I would like to thank you for your help; I have now gotten this project underway and the data transfer is working beautiful.  I will now add my condition structure and the rest of the code and I am a happy camper.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.