Results 1 to 13 of 13

Thread: Copy Access table to another database

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Copy Access table to another database

    I wish to copy one table which exists in Access database A (structure and data) to Access database B using VB6 and DAO without doing it record for record.
    Any help will be appreciated.
    Thanks

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Copy Access table to another database

    I use

    Code:
    INSERT INTO Table1
    SELECT *
    FROM [;database=C:\A.mdb].Table1;
    or

    Code:
    INSERT INTO Table1
    SELECT *
    FROM Table1 IN 'C:\A.mdb';

    edit: That was just data, here's both

    Code:
    SELECT * INTO Table2
    FROM [;database=C:\A.mdb].Table1;
    Code:
    SELECT * INTO Table2
    FROM Table1 IN 'C:\A.mdb';

  3. #3

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Copy Access table to another database

    Thanks Dexwerx,
    Can you please give me the correct syntax for:
    from table costcentres in database "c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb.costcentres" into my opened database set as dBase - I presume like in:
    Set rs = dBase.OpenRecordset("SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb.costcentres;]", dbOpenDynaset)
    Thanks

  4. #4
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: Copy Access table to another database

    Quote Originally Posted by Peekay View Post
    I wish to copy one table which exists in Access database A (structure and data) to Access database B using VB6 and DAO without doing it record for record.
    Any help will be appreciated.
    Thanks
    If you want to do it with DAO objects:

    Code:
    ' Copies a table from one database to another, it can copy only structure or also data.
    ' If the databases are already open, send them in the nDBOrig and nDBDest parameters, otherwise send their paths in nDBOrigPath and nDBDestPath
    Public Sub CopyTableFromDBToDB(nTableName As String, nCopyData As Boolean, Optional nDBOrig As Database, Optional nDBDest As Database, Optional nDBOrigPath As String, Optional nDBDestPath As String)
        Dim iDBOrig As Database
        Dim iDBDest As Database
        Dim iTableOrig As TableDef
        Dim iTableDest As TableDef
        Dim iFieldOrig As Field
        Dim iFieldDest As Field
        Dim iIndexOrig As Index
        Dim iIndexDest As Index
        Dim iRecOrig As Recordset
        Dim iRecDest As Recordset
        
        If Not nDBOrig Is Nothing Then
            Set iDBOrig = nDBOrig
        Else
            If Dir(nDBOrigPath) = "" Then
                Err.Raise 9001, App.Title & ".CopyTableFromDBToDB", "File not found for origin Database."
            End If
            Set iDBOrig = DBEngine.OpenDatabase(nDBOrigPath)
        End If
        If Not nDBDest Is Nothing Then
            Set iDBDest = nDBDest
        Else
            If Dir(nDBDestPath) = "" Then
                Err.Raise 9002, App.Title & ".CopyTableFromDBToDB", "File not found for destination Database."
            End If
            Set iDBDest = DBEngine.OpenDatabase(nDBDestPath)
        End If
        
        On Error Resume Next
        Set iTableOrig = iDBOrig.TableDefs(nTableName)
        On Error GoTo 0
        If iTableOrig Is Nothing Then
            Err.Raise 9003, App.Title & ".CopyTableFromDBToDB", "Table not found in origin database."
        End If
        
        Set iTableDest = iDBDest.CreateTableDef(iTableOrig.Name)
        For Each iFieldOrig In iTableOrig.Fields
            Set iFieldDest = iTableDest.CreateField(iFieldOrig.Name, iFieldOrig.Type)
            
            If (iFieldOrig.Type And dbLong) = dbLong Then
                If (iFieldOrig.Attributes And dbAutoIncrField) = dbAutoIncrField Then
                    iFieldDest.Attributes = iFieldDest.Attributes Or dbAutoIncrField
                End If
            End If
            iFieldDest.Required = iFieldOrig.Required
            If (iFieldOrig.Type And dbText) = dbText Then
                iFieldDest.Size = iFieldOrig.Size
            End If
            If ((iFieldOrig.Type And dbText) = dbText) Or ((iFieldOrig.Type And dbMemo) = dbMemo) Then
                iFieldDest.AllowZeroLength = iFieldOrig.AllowZeroLength
            End If
            If iFieldOrig.DefaultValue <> "" Then
                iFieldDest.DefaultValue = iFieldOrig.DefaultValue
            End If
            
            iTableDest.Fields.Append iFieldDest
        Next
    
        For Each iIndexOrig In iTableOrig.Indexes
            Set iIndexDest = iTableDest.CreateIndex(iIndexOrig.Name)
            For Each iFieldOrig In iIndexOrig.Fields
                iIndexDest.Fields.Append iIndexDest.CreateField(iFieldOrig.Name)
            Next
            iIndexDest.Unique = iIndexOrig.Unique
            iIndexDest.Primary = iIndexOrig.Primary
            iIndexDest.Required = iIndexOrig.Required
            iTableDest.Indexes.Append iIndexDest
        Next
        
        iDBDest.TableDefs.Append iTableDest
        
        If nCopyData Then
            Set iRecOrig = iDBOrig.OpenRecordset(nTableName)
            Set iRecDest = iDBDest.OpenRecordset(nTableName)
            
            If Not iRecOrig.EOF Then
                iRecOrig.MoveFirst
                Do Until iRecOrig.EOF
                    iRecDest.AddNew
                    For Each iFieldOrig In iTableOrig.Fields
                        iRecDest.Fields(iFieldOrig.Name).Value = iRecOrig.Fields(iFieldOrig.Name).Value
                        If iFieldOrig.Required Then
                            If IsNull(iRecDest.Fields(iFieldOrig.Name).Value) Then
                                iRecDest.Fields(iFieldOrig.Name).Value = iFieldOrig.DefaultValue
                            End If
                        End If
                    Next
                    iRecDest.Update
                    iRecOrig.MoveNext
                Loop
            End If
        End If
    End Sub

  5. #5
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Copy Access table to another database

    using this: https://support.microsoft.com/en-us/...uery-in-vb-3.0

    More like:

    Code:
    dBase.Execute "SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb].costcentres"

  6. #6

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Copy Access table to another database

    Thank you both - I will try it.

  7. #7

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Copy Access table to another database

    DexWerx,
    I am almost there. How do I handle a password in the database I read from?
    Thanks

  8. #8
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Copy Access table to another database

    Code:
    SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb;PWD=mypwd].costcentres

  9. #9

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Copy Access table to another database

    DexWerx,
    Thanks - I hope this is the last obtacle. It will not overwrite the table if it exists in the target database.

  10. #10

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Copy Access table to another database

    DexWerx,
    Somehow it does not allow me to rate your post. Am I missing something?

  11. #11
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Copy Access table to another database

    It will fail if the table exists in the other database. the INTO clause is used to create the table on the fly from a select statement. If the table exists then you use a standard insert statement....

    Insert into tableName from XXXXX
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  12. #12
    gibra
    Guest

    Re: Copy Access table to another database

    Quote Originally Posted by Peekay View Post
    DexWerx,
    Somehow it does not allow me to rate your post. Am I missing something?
    Show us your code.

  13. #13
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Copy Access table to another database

    g---I think he wants to 'RATE YOUR POST', but can't....

    OP, if that is correct, what does it say? Something about spreading around more ratings?

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