TransferDatabase acImport usage on an MDE front end

[BrettM]

I have some code that works beautifully on a split Access database when run in the original MDB format. When the front end is converted into an MDE the code no longer functions correctly. Has anyone had any experience with this phenomenon before?

The code I am using is...

DoCmd.TransferDatabase acImport, "Microsoft Access", tbFileName, acTable, PublicVar, PublicVar

...where tbFileName is the external file to retrieve data from and PublicVar is the table requested.

Regards Brett

 

[Guus2005]

... When the front end is converted into an MDE the code no longer functions correctly. ...

What do you mean? Can you be a little bit more specific?

 

[namliam]

Is this an existing table your importing to?? I dont have much experience with MDE, but I think one of the limitations is you cannot craeate new objects (tables) in it....

 

[BrettM]

The front end is an MDE but the back end is an MDB.

Guus2005, the code does not produce any errors, it simply does not do anything. No transfer of data takes place.

One thought I had was to write directly to the back end (instead of via the system link). Still... this seems to be the long way around the problem.

I will explore (read: hunt the web) for your thoughts about MDE limitations namliam. Thanks for the input guys.

Brett

 

[BrettM]

I need a quick refresher here...

How does one change the current active database? ie. I want to work directly with the back end and not the links in the front end.

Do I use Set objAccess = DBEngine.Workspaces(0).OpenDatabase(LinkedDBName, False, False) etc?

Does this set the VBA working with the back end file directly?

The full code I am testing is...

 Dim objAccess As Database
 Set objAccess = DBEngine.Workspaces(0).OpenDatabase(GetLinkedDBName, False, False)
    For i = 0 To objAccess.TableDefs.Count - 1
        PublicVar = objAccess.TableDefs(i).Name
        If Left(PublicVar, 4) <> "Msys" And Left(PublicVar, 1) <> "~" Then
            DoCmd.TransferDatabase acImport, "Microsoft Access", tbFileName, acTable, PublicVar, PublicVar
            DoCmd.DeleteObject acTable, PublicVar
            DoCmd.Rename PublicVar, acTable, PublicVar & "1"
        End If
    Next i

Regards Brett

 

[BrettM]

More feedback guys...

Just discovered that the code does seem to work but the problem is accessing the back end to do my table exports with the original backup routine.

In a linked DB DoCmd.TransferDatabase acExport, "Microsoft Access", BackupFileName, acTable, TableName, TableName ...doesn't copy the data... only the link so there is actually no real data in the backup at all.

How does one copy the actual contents of the back end?

Your help is appreciated.
Regards Brett

 

[Guus2005]

Your code works if the tablenames are already in your database. It should produce an error if not.

In a linked DB DoCmd.TransferDatabase acExport, "Microsoft Access", BackupFileName, acTable, TableName, TableName ...doesn't copy the data... only the link so there is actually no real data in the backup at all.

You have to modify your code in your previous post a little bit to refer to the database which has the actual tables in it.

Hint:
Start an Access application and open the database with the tables.
application.DoCmd.TransferDatabase

 

[BrettM]

Thanks Guus,

The penny dropped earlier today for that after reading up about it on the Microsoft Technet site. Then decided to cut out 95 percent of my code by simply copying the back end to another file. Hey presto... instant backup. Makes the whole exercise take about 5 seconds instead of the 60 or so it took to do it table by table.

Simple really.

    DoCmd.Close acForm, "Main", acSaveNo
    FileCopy BackupName, BackEndName
    DoCmd.OpenForm "Main", acNormal

Do you guys agree or am I trying to "uncomplicate" it too much?

Regardss Brett

 

[namliam]

Make sure that at the time of copy the front end is not using the backend... If it is beeing used, it can cause serious data corruption.

Other than that copying the backend is a surefire way of getting a backup of the backend.

 

[gemma-the-husky]

out of interest an mde is not a problem

you can import and manage tables and queries in an mde.

you cannot manage forms/reports/modules in any way.

-----------
i use this a lot, to import csv's etc, and process them - but note that the import imports to the FRONT END, not the backend - so the imported table is not available to other users - depends what you want it for

offhand i think an import will overwrite any previous version of the table without warning

you can use a maketable query to clone a linked table, but you dont get all the indexes from the linked table, which may or may not be
useful.

using DBEngine.Workspaces(0).OpenDatabase as you are is actually opening the backend - so you may be OK importing the table then - but I have no experience with this technique.