Version Control by Exporting or Importing Objects(Tables vs all other objects)

Ok I have a Master development version of my database and multiple versions of the same database with different names and the data will be different for each..  I need to keep all the versions in sync, however they are become unruly - I would like to create a process that will allow me to automatically push the changes to the various mdbs with the click of a button.

I have a list of objects to be exported or imported - not sure which is the best process yet.
or
a list of the tables to be imported into the newly copied version of the Master.mdb prior to renaming the mdb and replacing it in the correct location.  Also I would need to backup the previous version of the individual mdb prior to replacement.

I have a list of databases and their locations.

I am unsure of the best approach to either  loop thru the list of databases either push to newly revised objects to the databases or whether to take the Master, rename it and import the live data tables with relationships to create the last version of the database.

I believe that taking the entire Master version, renaming it, replacing the tables & relationships seems to be a cleaner way, then  replacing the chlld database.

What do you think would be the best approach?

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossCommented:
Hi Karen,

Most of us split the data into one db (the "back end") and the other objects into another db (the "front end") and then link the tables.  That way you can send an update that replaces the entire "front end" and link top the data so the data stays in place and the forms/reports and code are always current.

Regards,

Bill
0
Karen SchaeferBI ANALYSTAuthor Commented:
Unfortunately that is not an option here - I won't go into the reason why - but this is what I have to work with.

Since the data will be different for each of the copied and separately name mdbs - talking about 50 or more mdbs.  This is what I inherited and have to stay within these guidelines.

That is why I am looking for the best approach to make the necessary changes for all the databases.

K
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The best process would be as Bill said. I still don't understand why developers and/or companies refuse to do this. It's almost certain to cause data and/or object corruption, and eventually you'll lose your data. That seems to be a high price to pay for something like this, but then it's your data, not ours!

The best way would be to (a) delete all the objects in the target database (except the tables, of course) and then (b) import your new objects into that database and then (c) compact that database. This would maintain your current data in the target database, but update your new objects.

This is not something you should do over a network. You should move the target database to your machine, make the change, and then deploy the target back to the client. Trying to do this over a network will cause you no end of troubles.

You can move objects using the TransferDatabase method. See Help for full syntax.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
the biggest reason for not using the backend/frontend approach is that we are currently writing the requirements to move these databases to an Oracle database, so I cannot change the table structure,  however, the users still need to work in the databases and I need to modify the forms, queries, reports, and code to meet their needs.

Due to each department has it own version of the database I need to make the changes when and issue has been resolved..  So I need to be able to push out the changes and I have found that the easiest way to do this manually is to copy the revised Master, rename it , replace the none common tables with the departments data. then replace the newly created mdb after backing up the original for each department.

I was hoping to automate this process - What do you think would be the best approach.

K
0
Bill RossCommented:
Hi,

You might look at fmsinc.com.  They have an updater that will apply updates to various database objects.

Regards,

Bill
0
Karen SchaeferBI ANALYSTAuthor Commented:
Already have it is not approve software for my company- so not an option.  I am using ClearCase to keep copy of the latest version of the mdb and its code - however, I still need to push out the changes to all the other mdbs.

Thanks for the suggestion though.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
as I see it the steps that need to be code would be as follows.

Copy and rename MasterDev.mdb to the name of the department - Which I have stored in a table along with the file path of the mdb.

base on list of required tables to be replaced I would delete the table objects in the newly created mdb(local version)
Then import the same tables back into the newly created mdb.

Compact and Repair (will need code to do this)

Backup Current live version

File copy the new to replace the old.

What is your opinion?  can this be done within the MasterDev version or will I have to open the newly created MDb to finish the task of importing the tables.


K
0
Bill RossCommented:
You can open a db in code, create a table and then import the data.  The function below opens a db and creates a table named MissedWork.  You can see how it fits together.  Once the table is constructed you can you can then import the data.

Public Function AddMissedWorkTable()
    On Error Resume Next
    Dim ws As DAO.Workspace, db As DAO.Database, v, vSQL As String
    Dim tdf As DAO.TableDef, fld As DAO.Field, idx As DAO.Index, vFileName As String, v1 As Long
    Set ws = DBEngine.CreateWorkspace("TEST", "Admin", "")
   
    vFileName = "yourfilenamehere"
    Set db = ws.OpenDatabase(vFileName, False, False)
    On Error Resume Next
    db.TableDefs.Delete "MissedWork"
    On Error GoTo 0
    Set tdf = db.CreateTableDef("MissedWork")
    With tdf
        Set fld = .CreateField("MWID", dbLong)
        fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append fld
       
        Set fld = .CreateField("MWAmt", dbCurrency)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
       
       
        Set fld = .CreateField("MWDateOut", dbLong)
        .Fields.Append fld
       
        Set fld = .CreateField("MWDateRet", dbLong)
        .Fields.Append fld
       
        Set fld = .CreateField("MWWage", dbCurrency)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
       
        .Fields.Append .CreateField("MWWageType", dbText, 10)
       
        Set fld = .CreateField("MWNumHours", dbDouble)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
               
        Set fld = .CreateField("MWNumDays", dbDouble)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
       
       
        Set fld = .CreateField("MWCalcLoss", dbDouble)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
               
        Set fld = .CreateField("MWSelfEmplYN", dbBoolean)
        fld.Required = True
        fld.DefaultValue = 0
        .Fields.Append fld
               
               
        .Fields.Append .CreateField("MWEContact", dbText, 50)
        .Fields.Append .CreateField("MWEName", dbText, 50)
        .Fields.Append .CreateField("MWAddr1", dbText, 50)
        .Fields.Append .CreateField("MWAddr2", dbText, 50)
        .Fields.Append .CreateField("MWECSZ", dbText, 50)
        .Fields.Append .CreateField("MWEPhone", dbText, 20)
        .Fields.Append .CreateField("MWRmbCom", dbMemo)
        .Fields.Append .CreateField("MWECom", dbMemo)

    End With
    db.TableDefs.Append tdf

    Set idx = tdf.CreateIndex("PrimaryKey")
    With idx
        .Fields.Append .CreateField("MWID")
        .Unique = False
        .Primary = True
    End With
    tdf.Indexes.Append idx
    Set idx = tdf.CreateIndex("CNo")
    With idx
        .Fields.Append .CreateField("CNo")
    End With
    tdf.Indexes.Append idx

*** Now table is built - you could attach the original and run an update query here before you close the db and workspace****

    Set db = Nothing
    Set ws = Nothing
    Exit Function

End Function
0
Karen SchaeferBI ANALYSTAuthor Commented:
thank you but I do not want to recreate the table structure for each table I just want to replace the tables within the newly created mdb with the tables from the live version.

I cannot affect the table structure in any way.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
by recreating the table structure would effect the existing relationships, whereas importing the existing tables the relationships are included.

K.
0
Bill RossCommented:
Although you can create relationships in code, I think you would be better to delete and import the necessary objects (forms, reports, queries, etc.) rather than moving or copying the data.  You could add a routine to each database that imports various objects when executed.

Bill
0
Bill RossCommented:
FYI - You cannot replace the tables without destroying the existing structure and rebuilding it.  The is what the import process does but if there is any error you might have data loss.
0
Karen SchaeferBI ANALYSTAuthor Commented:
do you have any code samples that will allow for the deletion and importing of the necessary objects, without having the popups asking to replace exiting objects.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
I was not deleting the data from the tables - I was deleting the table objects and importing the same objects w/relationships intact  from the live versions.  Do you see any issues with this method instead of replacing all the other objects?

K
0
Bill RossCommented:
Hi Karen,

I don't have any code but the following sequence will work in a function or a macro.

docmd.setwarnings false
docmd.DeleteObject...
docmd.CopyObject...
docmd.setwarnings true

Regards,

Bill
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use TransferDatabase to move an object from one Access database to another. To remove an object from a remote database, however, you'll have to automate that database. The function below accepts the full path to your database, and returns an Access.Application object:

Function OpenRemoteDatabase(PathToDatabase As String) As Access.Application

Dim o As Access.Application
Set o = New Access.Application

o.OpenCurrentDatabase PathToDatabase

Set OpenRemoteDatabase = o

End Function

To use it:

Dim oAcc As Access.Application
Set oAcc = OpenRemoteDatabase("Path to db")

The oAcc object is ready to use. For example, to delete a table named "tblCustomers" from that database:

oAcc.DoCmd.DeleteObject "tblCustomers"

To transfer a new tblCustomers into that database:

oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access",  "Path To Master Database", acTable, "tblCustomers", "tblCustomers"            

When you're finished with the oAcc object, be sure to cleanup correctly:

oAcc.ClosecurrentDatabase
oAcc.Quit
Set oAcc = Nothing
0
Karen SchaeferBI ANALYSTAuthor Commented:
Scott,

Your idea is intriguing,  Would you recommend that this be done in a third database to execute the copying of files and replace the database from mdb1 to mdb2?

Here is my latest attempt - however, this is run from my Master developement copy - which I was planning on copying the mdb, renaming it, exchanging the tables with the live data.  Of course I ran into the issue of filecopy not allowing me to copy an open mdb.

It looks like your solution may be the work around for this if I place the code in the third mdb to execute, do you agree?

Karen.
Option Compare Database
Option Explicit
Public Const mStrFile = "C:\Development\MasterDev_ApDbms.mdb" '"\\nw\data\NWI&DS_FTI\MASTERS\NewAirplaneDbs\Master_ApDbms.mdb"
Public Const mStrFileC = "C:\Temp\"
Public Function ExportImport()
Dim strSQL As String
Dim strSQL1 As String
Dim nApNo As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strTbl  As String
Dim SourceFile As String
Dim DestinationFile As String

Set curdb = CurrentDb()

strSQL = "SELECT ApNo, ApDbms_Db" & _
        " FROM TA_AirplaneInfo" & _
        " WHERE (((CurrentStatus)='Active') AND ((NotValid_FTCS)=0))"

Set rs = curdb.OpenRecordset(strSQL)

rs.MoveFirst
    Do Until rs.EOF
        nApNo = rs.Fields("Apno")
        strFile = rs.Fields("ApDbms_Db")
        If FileExists(strFile) = True Then
            strSQL2 = "SELECT RevMaj" & _
                        " FROM TS_DB_Revisions IN '" & strFile & "'"
            
            Set rs2 = curdb.OpenRecordset(strSQL2)
        
                If rs2.Fields("RevMaj") > 4 Then
                    strSQL1 = "SELECT Name, Activate" & _
                            " FROM tbl_IMPORTS" & _
                            " WHERE (((tbl_IMPORTS.Activate)=-1))"
                    Set rs1 = curdb.OpenRecordset(strSQL1)
                    rs1.MoveFirst
                    Do Until rs1.EOF
                        strTbl = rs1.Fields("Name")
                        DoCmd.DeleteObject acTable, strTbl
                        rs1.MoveNext
                        DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
                    Loop
                
                Else
                    GoTo Cont:
                End If
            GoTo Cont:
        End If
            SourceFile = mStrFile    ' Define source file name.
            DestinationFile = mStrFile & nApNo & "_ApDbms.mdb" ' Define target file name.
            FileCopy SourceFile, DestinationFile    ' Copy source to target."
 
Cont:  rs.MoveNext
    Loop
    
End Function
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
Here is my latest attempt.

Just need to play with the order of the loop for copying the files then updating the tables.

K
Option Compare Database
Option Explicit
Public Const mSourceFile = "C:\Development\MasterDev_ApDbms.mdb" '"\\nw\data\NWI&DS_FTI\MASTERS\NewAirplaneDbs\Master_ApDbms.mdb"
Public Const mFileTemp = "C:\Temp\"
Public Const mFileApInfo = "\\nw\data\NWI&DS_FTI\DB-Data\AirplaneInfo\AirplaneInfo.mdb"
Public Function ExportImport()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim nApNo As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strTbl  As String
Dim strFile  As String
Dim SourceFile As String
Dim DestinationFile As String
Dim curDB As DAO.Database
Dim oAcc As Access.Application

Set curDB = CurrentDb()

strSQL = "SELECT ApNo, ApDbms_Db" & _
        " FROM TA_AirplaneInfo IN '" & mFileApInfo & "'" & _
        " WHERE (((CurrentStatus)='Active') AND ((NotValid_FTCS)=0))"

Set rs = curDB.OpenRecordset(strSQL)

rs.MoveFirst
    Do Until rs.EOF
        nApNo = rs.Fields("Apno")
        Debug.Print nApNo
        strFile = rs.Fields("ApDbms_Db")
        If FileExists(strFile) = True Then
            strSQL2 = "SELECT RevMaj" & _
                        " FROM TS_DB_Revisions IN '" & strFile & "'"
            
            Set rs2 = curDB.OpenRecordset(strSQL2)
        
                If rs2.Fields("RevMaj") > 4 Then
                    If Not rs2.EOF Then
                        ' Define source file name.
                        SourceFile = mSourceFile
                        ' Define target file name.
                        DestinationFile = mFileTemp & nApNo & "_ApDbms.mdb"
                        ' Copy source to target."
                        FileCopy SourceFile, DestinationFile
                        
                        'Opens temp database to replace the tables.
                        OpenRemoteDatabase (DestinationFile)
                        Set oAcc = OpenRemoteDatabase(DestinationFile)
                    End If
                    strSQL1 = "SELECT Name, Activate" & _
                            " FROM tbl_IMPORTS" & _
                            " WHERE (((tbl_IMPORTS.Activate)=-1))"
                    Set rs1 = curDB.OpenRecordset(strSQL1)
                    rs1.MoveFirst
                    Do Until rs1.EOF
                        
                        strTbl = rs1.Fields("Name")
                        'The oAcc object is ready to use. For example, to delete a table named "tblCustomers" from that database:
                        oAcc.DoCmd.DeleteObject acTable, strTbl
                        
                        'To transfer a new tblCustomers into that database:
                        oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
                        
                        rs1.MoveNext
                    Loop
                        'When you re finished with the oAcc object, be sure to cleanup correctly:
                        oAcc.CloseCurrentDatabase
                        oAcc.Quit
                        Set oAcc = Nothing
                Else
                    GoTo Cont:
                End If
            GoTo Cont:
        End If
 
Cont:  rs.MoveNext
    Loop
    
End Function
Function OpenRemoteDatabase(PathToDatabase As String) As Access.Application

Dim o As Access.Application
Set o = New Access.Application

    o.OpenCurrentDatabase PathToDatabase
    
    Set OpenRemoteDatabase = o

End Function
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Well ... truthfully, I wouldn't recommend you do this at all, but it seems you have little choice other than to maintain varied numbers of databases.

With that in mind, it would seem that your code should work, although I'm not clear on the reasoning for the loop and such. In cases like this, it's a good idea to fully comment your code, since it'll be much easier to troubleshoot and update when you have to revisit that code in a few months (or years).

Also, do yourself a favor and STOP using GoTo and Labels. It's lazy, and it causes a LOT more problems than it ever fixes. The only viable use for GoTo is with error handling, and that's not what you're doing. I don't know what your loops are supposed to do, but there is never - and let me repeat: never - any reason to use GoTo <somelabel> in modern programming. Use an If - Else branch, or a Select Case strucutre, or (better yet) callout-type programming, but get rid of those labels. Quite honestly, when I see code posted here that uses GoTo, I tend to stop trying to figure out the structure, since it is maddeningly difficult to track it down.

0
Karen SchaeferBI ANALYSTAuthor Commented:
callout-type programming???????????????????

what is that?

Ok I will remove the GoTo and I will eventuallly comment my code I am still figuring out the order of the Loops and IF statements.

Thanks for your input


K
0
Karen SchaeferBI ANALYSTAuthor Commented:
What is the syntax to compact and repair prior to closing the newly creating database once the tables have been updated.

I tried
                        oAcc.CompactRepair (DestinationFile)
                        oAcc.CloseCurrentDatabase
                        oAcc.Quit
                        Set oAcc = Nothing
it didn't like my syntax.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
If I remove the GoTo and then how to I handle the rs.movenext without forcing the code to jump ahead to far.

here is my latest code.  Please look at the end where I originally had the Goto code.  Note I commented the code better, I hope it is easier to understand.

K
Option Compare Database
Option Explicit
Public Const mSourceFile = "C:\Development\MasterDev_ApDbms.mdb" '"\\nw\data\NWI&DS_FTI\MASTERS\NewAirplaneDbs\Master_ApDbms.mdb"
Public Const mFileTemp = "C:\Temp\"
Public Const mFileApInfo = "\\nw\data\NWI&DS_FTI\DB-Data\AirplaneInfo\AirplaneInfo.mdb"
Public Function ExportImport()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim nApNo As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strTbl  As String
Dim strFile  As String
Dim SourceFile As String
Dim DestinationFile As String
Dim curDB As DAO.Database
Dim oAcc As Access.Application

Set curDB = CurrentDb()
'Creates list of Airplane's database to be considered for Version upgrades
strSQL = "SELECT ApNo, ApDbms_Db" & _
        " FROM TA_AirplaneInfo IN '" & mFileApInfo & "'" & _
        " WHERE (((CurrentStatus)='Active') AND ((NotValid_FTCS)=0))"

Set rs = curDB.OpenRecordset(strSQL)

rs.MoveFirst
    Do Until rs.EOF
        nApNo = rs.Fields("Apno")
        Debug.Print nApNo
        'Sets filepath for current Airplane Number.
        strFile = rs.Fields("ApDbms_Db")
        'Determines if the Airplane Database exist in the directory (ApDbms_Db)
        If FileExists(strFile) = True Then
            
            'Validates the current version for each Airplane.mdb & if greater the ver 4, then Update database objects and versions.
            strSQL2 = "SELECT RevMaj" & _
                        " FROM TS_DB_Revisions IN '" & strFile & "'"
            
            Set rs2 = curDB.OpenRecordset(strSQL2)
        
                If rs2.Fields("RevMaj") > 4 Then
                    If Not rs2.EOF Then
                       'Copies Master_Apdbms.mdb to c:Temp & renames to Airplane Number in question.
                        ' Define source file name.
                        SourceFile = mSourceFile
                        ' Define target file name.
                        DestinationFile = mFileTemp & nApNo & "_ApDbms.mdb"
                        ' Copy source to target."
                        FileCopy SourceFile, DestinationFile
                        
                        'Opens temp database to replace the tables.
                        OpenRemoteDatabase (DestinationFile)
                        Set oAcc = OpenRemoteDatabase(DestinationFile)
                    End If
                    strSQL1 = "SELECT Name, Activate" & _
                            " FROM tbl_IMPORTS" & _
                            " WHERE (((tbl_IMPORTS.Activate)=-1))"
                    Set rs1 = curDB.OpenRecordset(strSQL1)
                    
                    'Loopas thru list of tables to be replaced in the newly created version of each applicable Airplane Database
                    rs1.MoveFirst
                    Do Until rs1.EOF
                        
                        strTbl = rs1.Fields("Name")
                        'The oAcc object is ready to use. For example, to delete a table named "tblCustomers" from that database:
                        oAcc.DoCmd.DeleteObject acTable, strTbl
                        
                        'To transfer a new tables into that database:
                        oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
                        
                        rs1.MoveNext
                    Loop
                        'When you re finished with the oAcc object, be sure to cleanup correctly:
                       ' oAcc.CompactRepair (DestinationFile)
                        oAcc.CloseCurrentDatabase
                        oAcc.Quit
                        Set oAcc = Nothing
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>  


              Else
                    rs.MoveNext
                End If
            rs.MoveNext
        End If
       rs.MoveNext
    Loop
    
End Function

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
how do I handle the replacing of the tables when relationships are involved?

Getting error msg about the relationships.

K
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
These really are questions which should be asked as new questions.

"callout type programming" is where you would include function calls that you use to perform "blocks" of code. For example, if I'm in a loop and I need to validate numeric data against other stored table data, I could do it in the loop, or I could build an external function and "callout" to that function from inside my loop. My function would return a Boolean value, perhaps, and my loop would take action based on the return of that function.

<If I remove the GoTo and then how to I handle the rs.movenext without forcing the code to jump ahead to far.>

That's difficult to say, since I'm not sure exactly what is going on, or what your goal is with the code. If you could writeup a paragraph or so of text that defined what you're trying to do with the code, we might be able to suggest some alternate methods.

0
Karen SchaeferBI ANALYSTAuthor Commented:
to restate what I am attempting to accomplish is as follows:

1.  Determine list of Databases that need to be revised based on list of Airplanes along with the Maximum Revision number.

2.  If Revision letter >4, then copy the master template, rename the template and store on the C:Temp

3.  Then based on a list of tables that will need to be replaced in the newly copied mdb, loop thru the list of tables & delete and import the tables from the Live version of the mdb.
                - HOWEVER, I am currently getting an error about relationships - You can;t deleter thable table name: it is participating in one or more relationships.
        WHAT is the work around to solve this issue.

4.  upon updating of the tables then I need to move to the next Airplane and repeat the code - Hence the GOTO code. (ie. Loop)

See the comments in my code - may help you with the different portions of the code process.



Option Compare Database
Option Explicit
Public Const mSourceFile = "C:\Development\MasterDev_ApDbms.mdb" '"\\nw\data\NWI&DS_FTI\MASTERS\NewAirplaneDbs\Master_ApDbms.mdb"
Public Const mFileTemp = "C:\Temp\"
Public Const mFileApInfo = "\\nw\data\NWI&DS_FTI\DB-Data\AirplaneInfo\AirplaneInfo.mdb"
'---------------------------------------------------------------------------------------
' Procedure : ExportImport
' DateTime  : 12/14/2011 08:54
' Author    : Karen F. Schaefer, DBA
' Purpose   : Export database objects for version control of Airplane Databases.
'---------------------------------------------------------------------------------------
'
Public Function ExportImport()
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim nApNo As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim strTbl  As String
Dim strFile  As String
Dim SourceFile As String
Dim DestinationFile As String
Dim curDB As DAO.Database
Dim oAcc As Access.Application

   On Error GoTo ExportImport_Error

    Set curDB = CurrentDb()
    'Creates list of Airplane's database to be considered for Version upgrades
    strSQL = "SELECT ApNo, ApDbms_Db" & _
            " FROM TA_AirplaneInfo IN '" & mFileApInfo & "'" & _
            " WHERE (((CurrentStatus)='Active') AND ((NotValid_FTCS)=0))"
    
    Set rs = curDB.OpenRecordset(strSQL)
    
    'Loops thru list of applicable Airplanes
    rs.MoveFirst
        Do Until rs.EOF
            nApNo = rs.Fields("Apno")
            Debug.Print nApNo
            
            'Sets filepath for current Airplane Number.
            strFile = rs.Fields("ApDbms_Db")
            
            'Determines if the Airplane Database exist in the directory (ApDbms_Db)
            If FileExists(strFile) = True Then
                
                'Validates the current version for each Airplane.mdb & if greater the ver 4, then Update database objects and versions.
                strSQL2 = "SELECT Max(RevMaj) as Rev" & _
                            " FROM TS_DB_Revisions IN '" & strFile & "'"
                
                Set rs2 = curDB.OpenRecordset(strSQL2)
            Debug.Print rs2.Fields("Rev")
                    If rs2.Fields("Rev") > 4 Then
                        If Not rs2.EOF Then
                           
                           'Copies Master_Apdbms.mdb to c:Temp & renames to Airplane Number in question.
                            '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            ' Define source file name.
                            SourceFile = mSourceFile
                            ' Define target file name.
                            DestinationFile = mFileTemp & nApNo & "_ApDbms.mdb"
                            ' Copy source to target."
                            FileCopy SourceFile, DestinationFile
                            '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            
                            'Opens temp database to replace the tables.
                           ' OpenRemoteDatabase (DestinationFile)
                            Set oAcc = OpenRemoteDatabase(DestinationFile)
                        
                        End If
                        strSQL1 = "SELECT Name, Activate" & _
                                " FROM tbl_IMPORTS" & _
                                " WHERE (((tbl_IMPORTS.Activate)=-1))"
                        Set rs1 = curDB.OpenRecordset(strSQL1)
                        
                        'Loops thru list of tables to be replaced in the newly created version of each applicable Airplane Database
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                        rs1.MoveFirst
                        Do Until rs1.EOF
                            
                            strTbl = rs1.Fields("Name")
                            'The oAcc object is ready to use. For example, to delete a table named "tblCustomers" from that database:
                            oAcc.DoCmd.DeleteObject acTable, strTbl
                            
                            'To transfer a new tables into that database:
                            oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
                            
                            rs1.MoveNext
                            
                            'Updates the SysApNo with the new Airplane Number
                            'oAcc.DoCmd.RunSQL ("UPDATE TS_DbEffectivity SET SysApNo = " & nApNo & "")
                        Loop
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            'When you're finished with the oAcc object, be sure to cleanup correctly:
                            oAcc.CloseCurrentDatabase
                            oAcc.Quit
                            Set oAcc = Nothing
                                
                            CompactDatabase (DestinationFile)
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                    Else
                        GoTo Cont:
                       ' rs.MoveNext
                    End If
                 GoTo Cont:
                ' rs.MoveNext
            End If
Cont:       rs.MoveNext
        Loop

   On Error GoTo 0
   Exit Function

ExportImport_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportImport of Module modUpdate"
    
End Function

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
ok not sure if I should ask this question here or close this out and open new question?

Maybe I am approaching this wrong - instead of adding and deleting tables from the copied mdb, maybe I should create a blank mdb in a temp folder than import all objects from the master template except the tables and import the tables from the live database/

What do you think?

here is some code I found that might be useful, how would i make it work on the remote database?


DestinationFile = mFileTemp & nApNo & "_ApDbms.mdb"
        CreateNewMDBFile (DestinationFile)

Function ObjectList()
  Dim obj As AccessObject, dbs As Object
    
    Set dbs = Application.CurrentData
    Debug.Print "Tables:"
    For Each obj In dbs.AllTables
    oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
      
    Next obj
    
    Set dbs = Application.CurrentData
    Debug.Print "Queries:"
    For Each obj In dbs.AllQueries
            Debug.Print "        " & obj.Name
    Next obj

    Set dbs = Application.CurrentProject
    Debug.Print "Forms:"
    For Each obj In dbs.AllForms
            Debug.Print "        " & obj.Name
    Next obj

    Set dbs = Application.CurrentProject
    Debug.Print "Reports:"
    For Each obj In dbs.AllReports
            Debug.Print "        " & obj.Name
    Next obj

    Set dbs = Application.CurrentProject
    Debug.Print "Macros:"
    For Each obj In dbs.AllMacros
            Debug.Print "        " & obj.Name
    Next obj

    Set dbs = Application.CurrentProject
    Debug.Print "Modules:"
    For Each obj In dbs.AllModules
            Debug.Print "        " & obj.Name
    Next obj
End Function

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Below is the fix for the GoTo.

As to the relationships issue - you'll need to store the relationships, then delete the relationships, then delete the table, then move the table, and then recreate the relationships.

But that should be asked as another question.
Do Until rs.EOF
            nApNo = rs.Fields("Apno")
            Debug.Print nApNo
            
            'Sets filepath for current Airplane Number.
            strFile = rs.Fields("ApDbms_Db")
            
            'Determines if the Airplane Database exist in the directory (ApDbms_Db)
            If FileExists(strFile) = True Then
                
                'Validates the current version for each Airplane.mdb & if greater the ver 4, then Update database objects and versions.
                strSQL2 = "SELECT Max(RevMaj) as Rev" & _
                            " FROM TS_DB_Revisions IN '" & strFile & "'"
                
                Set rs2 = curDB.OpenRecordset(strSQL2)
            Debug.Print rs2.Fields("Rev")
                    If rs2.Fields("Rev") > 4 Then
                        If Not rs2.EOF Then
                           
                           'Copies Master_Apdbms.mdb to c:Temp & renames to Airplane Number in question.
                            '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            ' Define source file name.
                            SourceFile = mSourceFile
                            ' Define target file name.
                            DestinationFile = mFileTemp & nApNo & "_ApDbms.mdb"
                            ' Copy source to target."
                            FileCopy SourceFile, DestinationFile
                            '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            
                            'Opens temp database to replace the tables.
                           ' OpenRemoteDatabase (DestinationFile)
                            Set oAcc = OpenRemoteDatabase(DestinationFile)
                        
                        End If
                        strSQL1 = "SELECT Name, Activate" & _
                                " FROM tbl_IMPORTS" & _
                                " WHERE (((tbl_IMPORTS.Activate)=-1))"
                        Set rs1 = curDB.OpenRecordset(strSQL1)
                        
                        'Loops thru list of tables to be replaced in the newly created version of each applicable Airplane Database
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                        rs1.MoveFirst
                        Do Until rs1.EOF
                            
                            strTbl = rs1.Fields("Name")
                            'The oAcc object is ready to use. For example, to delete a table named "tblCustomers" from that database:
                            oAcc.DoCmd.DeleteObject acTable, strTbl
                            
                            'To transfer a new tables into that database:
                            oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", strFile, acTable, strTbl, strTbl
                            
                            rs1.MoveNext
                            
                            'Updates the SysApNo with the new Airplane Number
                            'oAcc.DoCmd.RunSQL ("UPDATE TS_DbEffectivity SET SysApNo = " & nApNo & "")
                        Loop
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                            'When you're finished with the oAcc object, be sure to cleanup correctly:
                            oAcc.CloseCurrentDatabase
                            oAcc.Quit
                            Set oAcc = Nothing
                                
                            CompactDatabase (DestinationFile)
                        '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                    
                    End If                 
            End If
			rs.MoveNext
        Loop

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the assist,

k
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.