Results 1 to 8 of 8

Thread: Easiest way of merging two Access databases together

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    5

    Easiest way of merging two Access databases together

    Hi All,

    I have absolutely no clue about databases (although I've been developing for years) and find myself having to merge the contents of one file into another e.g. FileNew.accdb into FileMain.accdb, from a VB application. The two files contain 1 table, 1 primary key, identical columns etc. and I want to merge everything so my SQL is pointing to "Select * from ...."

    I have accessed this data by adding it as a datasource in the solution explorer to the application (a different application I should point out) and accessing it using OleDb.OleDbConnection.

    Having looked around the net it would seem I should be able to do this using SqlConnection but the first problem I get is that elements of the connectionString aren't recognised (I assume this is due to not setting references correctly or something). Do I still need to add the database as a data source in order to satisfy filling the SqlDataAdapter etc?
    Whats the difference between SqlConnection and OleDbConnection?

    Basically I need a complete idiots guide to merging two files which expands on the following psuedo code:
    get data from file 1
    get data from file 2
    Merge file1 data into file 2
    Save file2 data
    close both files

    Thanks in advance

  2. #2
    Lively Member
    Join Date
    Jun 2008
    Location
    Bayang Magiliw, Perlas Ng Silangan
    Posts
    100

    Re: Easiest way of merging two Access databases together

    Easiest way of merging Access database together?

    I presume you want to merge two similar tables from 2 .accds? if this is the case you can create an append query from within MS ACCESS db.

    Open FileNew.accdb within MS ACCESS then make an APPEND QUERRY, then RUN it.

    Code:
    INSERT INTO <YourTableDest> IN 'C:\FileMain.accdb'
    SELECT <YourTableSour>.*
    FROM <YourTableSour>;
    <YourTableDest> is your table from FileMain.accbd
    <YourTableSour> is your table from FileNew.accbd
    Last edited by nubie; Mar 15th, 2011 at 02:47 PM.

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    5

    Re: Easiest way of merging two Access databases together

    Hi Nubie,

    Thanks for that - unfortunately I need to do this merge from a VB.Net application.

    I was hoping someone could tell if something like the DataSet.Merge(xxxxx) function was appropriate and if so a good example of how to use it

    Should I have posted this thread in the VB.NET forum?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Easiest way of merging two Access databases together

    Welcome to VBForums

    The SQL statement that nubie posted (like most others) can be run from VB.Net too, just create a command object (OLEDBCommand) and set the properties as apt, then do .ExecuteNonQuery

    Whats the difference between SqlConnection and OleDbConnection?
    They are almost identical, but the OleDB one is for any database system, whereas the SQL one is for SQL Server only (and because it is specific, it is more efficient).

    In general any database related item that starts with Sql (such as SqlCommand) is directly equivalent to the OleDb equivalent (such as OleDbCommand).

    Should I have posted this thread in the VB.NET forum?
    It is better here, as you tend to get the database specialists (who can give advice based on lots of experience), rather than just those with general VB.Net knowledge (who can be very helpful, but are likely to give suggestions that aren't as good).

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    5

    Re: Easiest way of merging two Access databases together

    Hi Si,

    As I said, I have no clue about DB stuff and SQL (too many years dealing with embedded device code!!) so will have to look at the command object solution.

    I'll bite the bullet and post some (probably embarassing) code that I originally used to approach the problem and maybe someone can expalin to me why it doesn't work. Given that the source has 1500 records in it and the destination is empty - I would have expected the destination to contain all the records form the source after running this code - but it remains empty. As said before, the table structure for both is identical.

    Code:
        Private Sub MergeDatabase(ByVal SourceDB As String, ByVal DestinationDB As String)
    
            Dim CellPassword As String = ""
            Dim DestinationDataBaseConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DestinationDB & ";Jet OLEDB:Database Password=" & CellPassword & ";Mode=Share Deny None;"
            Dim SourceDataBaseConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceDB & ";Jet OLEDB:Database Password=" & CellPassword & ";Mode=Share Deny None;"
            Dim sql As String = "SELECT * FROM Cell_Data"
    
            Try
    
                'Open Connections
                Dim sourcecon As New OleDb.OleDbConnection
                sourcecon.ConnectionString = SourceDataBaseConnectionString
                sourcecon.Open()
                Dim destcon As New OleDb.OleDbConnection
                destcon.ConnectionString = DestinationDataBaseConnectionString
                destcon.Open()
    
                'Get source data into DataSet
                Dim Source_DS As New CellDataPointsDataSet
                Dim Source_DA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, sourcecon)
                Source_DA.Fill(Source_DS, "Cell_Data")
                Dim Source_CB As New OleDb.OleDbCommandBuilder(Source_DA)
    
                'Get Destination data into DataSet
                Dim Dest_DS As New CellDataPointsDataSet
                Dim Dest_DA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, destcon)
                Dest_DA.Fill(Dest_DS, "Cell_Data")
                Dim Dest_CB As New OleDb.OleDbCommandBuilder(Dest_DA)
    
                'Merge the databases
                Dest_DS.Merge(Source_DS, True, MissingSchemaAction.AddWithKey)
    
                'Save changes to the Destination accdb
                Dest_DA.Update(Dest_DS, "Cell_Data")
    
                'Close Connections
                sourcecon.Close()
                destcon.Close()
    
            Catch ex As Exception
            End Try
    
    
        End Sub

  6. #6

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    5

    Re: Easiest way of merging two Access databases together

    ...I should also point out that if I loop through and add each row instead of using the .Merge function it works perfectly. So I'm definitely doing something wrong with the Merge

  7. #7
    Lively Member
    Join Date
    Jun 2008
    Location
    Bayang Magiliw, Perlas Ng Silangan
    Posts
    100

    Re: Easiest way of merging two Access databases together

    check this out, this works in my sample vb.net app

    Code:
    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\FileMain.accdb")
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO d SELECT * FROM [MS Access;DATABASE=C:\FileNew.accdb].[s]", AccessConn)
    
    AccessConn.Open()
    AccessCommand.ExecuteNonQuery()
    AccessConn.Close()
    
    Note:
    d --> destination table
    s --> source table

  8. #8

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    5

    Re: Easiest way of merging two Access databases together

    Hi nubie,

    I was hoping to get a try at that today - but obviously didn't. I'll let you know how I get on when I do find time to look at it (in the next few days I hope)

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width