Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010

    Unanswered: TransferDatabase without Relationships?


    I have a BE/FE split Access 2007 database, and there are multiple installations of this setup in remote places being operated by not-very-sophisticated users. Therefore I have to automate as much admin as I possibly can!

    Obviously when I am sending out updates I try to keep the changes just to the FE, so the upgrade is easy. However, I think that the next release is going to need some BE changes too, so my remote users are going to need to do some data migration from old BE to new BE when they upgrade.

    In my BE I have a "data migration" form which tries to make this as easy as possible. The general idea is to copy the tables in from the old BE, then copy the data making any necessary fixes on the way, then delete the imported tables.

    I'd like to automate the cleanup by using DoCmd.DeleteObject on each unwanted table at the end, but you can't do that if there are relationships existing. Going round finding and deleting the relationships looks tricky (ok, not impossible) so I thought the easy workaround would be to import the tables without relationships in the first place.

    If you use the Import Access Wizard then this is ok ... after selecting the file and the tables you can select "Options" and then untick "relationships". Great. But I want to automate this so I'm not dependent on users finding this tickbox, so what I want to do is import the tables in VBA (perhaps using the DoCmd.TransferDatabase method) WITHOUT the relationships ... but I can't find a VBA equivalent of that tick box.

    It feels like it ought to be there but I just can't find it ... help!!


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Try this:
    Function ExportTables(ByVal SourceDatabase As String, ByVal DestinationDatabase As String, ByVal Prefix As String, ParamArray Tablelist() As Variant)
        Dim appAccess As Access.Application
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        Dim i As Integer
        Set appAccess = New Access.Application
        With appAccess
            .OpenCurrentDatabase SourceDatabase, True
            Set dbs = .CurrentDb
            If UBound(Tablelist) = -1 Then
                For Each tdf In dbs.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then .DoCmd.CopyObject DestinationDatabase, Prefix & Tablelist(i), acTable, tdf.Name
                For i = 0 To UBound(Tablelist)
                    .DoCmd.CopyObject DestinationDatabase, Prefix & Tablelist(i), acTable, Tablelist(i)
                Next i
            End If
        End With
        Set dbs = Nothing
        Set appAccess = Nothing
    End Function
    Prefix can be supplied to add a prefix (ex. "Old_") to the name of the exported table. If you do not supply the ParamArray TableList() parameter, the function will export every file in the source database, except those with a name beginning with "MSys" (system table). The function will export every table with its name in the TableList() array otherwise (provided it exists in the source database).

    You could find useful to perform some tests to verify that both SourceDatabase and DestinationDatabase exist, and also to implement an error handler, specially if the function is to be run unattended.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    persoanlly I'd
    lock the existing application
    copy the old backend as a backup
    make changes in place to the backend using DDL
    as you make your changes apply any RI checks, ideally beforehand alternative strategy is to run a compliance check before hand and report on any changes to data that need making before changing the DB. designed properly you could then inhibit the application changes being made. you coudl do this as a series of queries returngin the n umber fo rows which fall foul of the new rules. if the count is more than 0 you have errors.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2010
    Thanks for the quick responses:

    Sinndho - nearly, but not quite. "CopyObject" works for copying objects out of the current database into another one (or a new name in the current). I need to bring the objects in from elsewhere. I can't run this code in the source database as by definition its the old one!

    Healdem - I don't think you've read my notes! I can't make random changes to the old BE, it is in a faraway place with inexperienced admins. Hence the wish to automate the importing of data from old BE to new BE...


  5. #5
    Join Date
    Mar 2010
    OK, think I've sorted it. If I use DoCmd.TransferDatabase to bring the tables in, they seem to come in without relationships, which means I can just delete them when I'm done.

    Thanks for the dialogue!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts