?
Solved

Copy Access tables from one BE database to another BE database

Posted on 2014-01-25
10
Medium Priority
?
871 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
  • 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 800 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 49

Author Comment

by:Dale Fye
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 49

Author Comment

by:Dale Fye
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 800 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 40

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 49

Author Comment

by:Dale Fye
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 40

Accepted Solution

by:
PatHartman earned 1200 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 49

Author Closing Comment

by:Dale Fye
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

807 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