Solved

Copy Access tables from one BE database to another BE database

Posted on 2014-01-25
10
826 Views
Last Modified: 2014-02-04
Having a brain cramp this afternoon, must be these freezing temperatures.

Have a backend database with about 50 tables.  I need to create another BE with identical table structures, indices, relationships, ... but with empty tables (at least most of them will be empty), via VBA code, from my FE

Just cannot seem to get a handle on this today.  I know I could copy the entire BE, then loop through the tables I want cleaned out and delete all the records, but I would have to do that in the proper order or end up with referential integrity issues.  I'm looking for something a bit more elegant.
0
Comment
Question by:Dale Fye (Access MVP)
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39809355
Does the hidden MSysRelationships table show the parent child relations of the fields?
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39809914
How about DoCmd.TransferDatabase? You can transfer Structure Only:

DoCmd.TransferDatabase acExport, "Microsoft Access", "Your New Database", acTable, "Source Table Name", "Destination Table Name", True

So just loop through the TableDefs and run that for all tables except the MSYS tables. It should create an empty table, and should keep everything else intact except for relationships. To move those over, use code like in the MoveRelationshps section below.

The ExportToNew creates a new database prefixed with the word "COPY" in the same location as the current database, then moves all non-system tables over, then calls that MoveRelationships code:

Function ExportToNew()
    Dim tdf    As DAO.TableDef
    Dim dbs    As DAO.Database

    Set dbs = CurrentDb
    '/ create a new database
    Dim sPath  As String
    Dim sName  As String
    sPath = dbs.Name

    Dim iName  As Integer
    iName = InStrRev(sPath, "\")

    sName = Right(sPath, Len(sPath) - iName)
    sPath = Left(sPath, iName)

    sName = sPath & "COPY_" & sName

    DAO.CreateDatabase sName, dbLangGeneral

    Debug.Print sName

    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSYS" Then
            DoCmd.TransferDatabase acExport, "Microsoft Access", sName, acTable, tdf.Name, tdf.Name, True
        End If
    Next

    Set dbs = Nothing
    Set tdf = Nothing
    '/ now move the relationships over
    MoveRelationships sName
    
End Function

Function MoveRelationships(NewDatabase As String)
    Dim rel    As DAO.Relation
    Dim nrel   As DAO.Relation
    Dim fld    As DAO.Field
    Dim strRName As String
    Dim strFName As String
    Dim strFFName As String
    Dim strTName As String
    Dim strFTName As String
    Dim varAtt As Variant
    
    Dim dbs    As DAO.Database
    Set dbs = DAO.OpenDatabase(NewDatabase)
    Dim cdb    As DAO.Database
    Set cdb = CurrentDb
    
    For Each rel In cdb.Relations
        With rel
            If Left(.Name, 4) <> "MSYS" Then
                'Get properties of relation to copy.
                strRName = .Name
                strTName = .Table
                strFTName = .ForeignTable
                varAtt = .Attributes

                'Create relation in current db with same properties.
                Set nrel = dbs.CreateRelation(strRName, strTName, strFTName, varAtt)

                For Each fld In .Fields
                    strFName = fld.Name
                    strFFName = fld.ForeignName
                    nrel.Fields.Append nrel.CreateField(strFName)
                    nrel.Fields(strFName).ForeignName = strFFName
                Next

                dbs.Relations.Append nrel
            End If
        End With
    Next
End Function

Function ShowRels()

Dim rel As DAO.Relation
Dim dbs As DAO.Database

Set dbs = CurrentDb

For Each rel In dbs.Relations
Debug.Print rel.Name
Next

End Function

Open in new window

I snagged parts of this code long ago from here:  http://support.microsoft.com/kb/298174. Didn't need the other stuff, just the relationship move, so I parsed those sections out.
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 39809956
Scott,

That process works great if you are moving the tables out of the currentdb, but if those tables are "linked" in the currentdb, then they will be linked to the original source in the destination db as well.  The point here is that I want to copy my BE database to a new BE.  

I already tried simply setting db =dbengine.opendatabase (...), thinking that if I tried using docmd within a With statement, that docmd might use that database object rather than currentdb:
set db = dbengine.Opendatabase (strBESource)
With db
    For each tdf in db.tabledefs
        if instr(tdf.name, "mSys") = 0 then 
             docmd.transferdatabase ...
        end if
    Next
end with

Open in new window

But it didn't, and I ended up with the linked tables in the 2nd BE.

I like that move relationships code, though.  Maybe I'll adapt that to move the indices as well.  Doing that, I could probably simply use a MakeTable query:

strSQL = "SELECT [" & tdf.Name & "].* " _
             & "INTO [" & tdf.Name & "] '" & strNewBE & "' " _
             & "FROM [" & tdf.Name & "] WHERE 0=1"

Then run the indices code and then the relationships code.  That might just do the job.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 85
ID: 39810111
That process works great if you are moving the tables out of the currentdb, but if those tables are "linked" in the currentdb, then they will be linked to the original source in the destination db as well.
You can set the "dbs" variable to whatever database you like. Instead of:

Set dbs = CurrentDb

do this:

Set dbs = DAO.OpenDatebase("Some Other Path")

The code would still be viable. I do something exactly like that to create in-use backups in one application.
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 39810301
Scott,

Tried that with code similar to what I showed above.  The docmd.Transfer database inside the With / End With construct copied the tables from Currentdb to the destination database, not from the db defined by the variable.

Have not tried:

set db = dbengine.opendatabase(strNewBE)

and trying to import from the old BE.  Will have to give that a try later today.

Have some "honey do" items to get off my list.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39810377
Right ... didn't think about that.

You could create an Access object and use it from there:

  Dim acc As Access.Application
  Set acc = New Access.Application
  acc.OpenCurrentDatabase "FilePath"
  
  
    Dim tdf    As DAO.TableDef
    Dim dbs    As DAO.Database

    Set dbs = acc.CurrentDb ' CurrentDb
    '/ create a new database
    Dim sPath  As String
    Dim sName  As String
    sPath = dbs.Name

    Dim iName  As Integer
    iName = InStrRev(sPath, "\")

    sName = Right(sPath, Len(sPath) - iName)
    sPath = Left(sPath, iName)

    sName = sPath & "COPY_" & sName

    DAO.CreateDatabase sName, dbLangGeneral

    Debug.Print sName

    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSYS" Then
            acc.DoCmd.TransferDatabase acExport, "Microsoft Access", sName, acTable, tdf.Name, tdf.Name, True
        End If
    Next

    Set dbs = Nothing
    Set tdf = Nothing
    '/ now move the relationships over
    MoveRelationships sName

Open in new window

0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39810890
You didn't say whether this was a one-time effort or something you needed to do at regular intervals.  Everyone else has assumed the latter.  However, if it is the former, just open a new empty database.  Select the option to get external data.  Choose the database, select all the tables.  Choose the option to import only the schema but not the data.  Press the options button to see additional options and check the option to import relationships also.
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 39812200
Pat,

It is something the client needs to do intermittently throughout the year.  For security and contract purposes, they are forced to separate data for various contracts, so they need separate but identical backend databases.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 300 total points
ID: 39812433
If you always start with an empty BE, then create one with everything you need.  When the user wants to start a new project, they can click a button and your code can copy the template and rename it with the project name and then relink all the tables to the FE the user is currently in.  That way you don't have to worry about the tables ending up being linked to the original BE.

Of course having all those BE's is problematic since if you ever need to make a structural change, you will have to change all the BE's plus the template.

I have an application that needs to create a archive.  The active data can be from many clients but when the company is done with a particular audit, they archive the data which saves it in a template (I link to the copied BE and append the selected data) and then deletes it from the active database.  You know and I know it is quite simple to keep individual sets of client data separate but if your contract says you must store it separately, then I guess that's what you have to do.
0
 
LVL 48

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 39833674
Finally getting back to this question.  Decided to go with a blank database template.

Scott, thanks for the code example.  I think it is just going to be easier to maintain the db template, than try to copy the objects and then recreate the relationships and referential integrity.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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