Strange behaviour

[samotek]

I want to export all queries as follows
Dim FrontDB As DAO.Database, BackDB As DAO.Database, Qry As QueryDef
Set FrontDB = CurrentDb
Set BackDB = OpenDatabase("C:\BE\MGP\Survey\Survey.mdb")
For Each Qry In FrontDB.QueryDefs
DoCmd.TransferDatabase acExport, "Microsoft Access", BackDB.Name, acQuery, Qry.Name, Qry.Name
Next
BackDB.Close
FrontDB.Close


However I get the error that the path is not found. However the path is true,I do not know why this error occurs.
For example the fllowing line is OK :
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\BE\MGB\Survey\Survey.mdb", acModule, "mdlupsize", "mdlupsize"

Why it is Ok in the second example but not in the first I do not know. It is very strange. Do you have any explanation ?

 

[lameid]

Does BackDB.Name evaluate what you expect it to when debugging?

I have not used transferdatabase myself, so I am assuming you have the right syntax.

Your database path seems to comply with 8.3 naming conventions. I have seen things not work before when they were not. If that happens, often wrapping everything in double quotes like you would at the command prompt helps.

 

[Golom]

Works OK for me except that I had to change

Set BackDB = OpenDatabase("C:\BE\MGP\Survey\Survey.mdb")

to

Set BackDB = [red]DAO.DBEngine(0).[/red]OpenDatabase("C:\BE\MGP\Survey\Survey.mdb")

 

[Remou]

It worked fine for me without changing anything.

 

[Remou]

I wonder could a linked query be causing the problem?

 

[TheAceMan1]

How are ya samotek . . .

[blue]However I get the error that the path is not found.[/blue]

The third arguement [purple]databasename[/purple] appears to be the culprit here.

[blue]databasename A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.[/blue]

In your fist render you only provide the name:

[blue]DoCmd.TransferDatabase acExport, "Microsoft Access", [purple][b]BackDB.Name[/b][/purple][/blue]

In the second render the full path:

[blue]DoCmd.TransferDatabase acExport, "Microsoft Access", [purple][b]"C:\BE\MGB\Survey\Survey.mdb"[/b][/purple][/blue]

If no path is given I'm not sure if it defaults to the current path. However, the two renders you've provided appear to say this is not so.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[lameid]

TheAceMan1,

Name returns the full path and filename for the specified DAO.Database. That much seems functional. Although I asked because if it returns correctly then I would recommend a /decompile.

I think Golom nailed it on the opendatabase. It seemed suspicious to me although I didn't mention it as I thought I might be confused with opencurrentdatabse.

I find it curious that the code works for Remou but not Golom.

Help says it OPTIONALLY used off a workspace object.

So the long syntax would be...

Set BackDB = DAO.DBEngine.Workspaces(0).OpenDatabase("C:\BE\MGP\Survey\Survey.mdb")

samotek,
All that said, I am still curious as to what Backdb.Name is returning. I expect it to be "C:\BE\MGB\Survey\Survey.mdb" if the database is being open correctly.

Also, it may be worthwhile to set that literal to a string and use it to open BackDB and use it instead in the opendatabase line. There is no need to open the database in the code snippet but you may have another use for the database elsewhere in your code.

 

[Golom]

I find it curious that the code works for Remou but not Golom.

After due reflection, I traced my problem to an antiquated reference.

The DBMS in which I tested is a scratch database that I use for proof of concept stuff. Turned out it was using a reference to Microsoft DAO 2.5/3.51 Compatibility Library. When I got rid of that and choose Microsoft DAO 3.6 Object Library then the original code ran without modifications.

For me at least, "BackDB.Name" is returning the full path and file name. I stuck a breakpoint in the code to check that.