Solved

Copy Access tables from one BE database to another BE database

Posted on 2014-01-25
10
768 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)
  • 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 84

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 47

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
 
LVL 84
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 47

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 84

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 34

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 47

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 34

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 47

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now