docmd is very difficult

[Mathematician]

hi guys,

do you know how to call the function "docmd" but in another DB.
for example I am working on my.mdb
and have the code inside:
sub TransferSpreadsheet()
set db=opendatabase("C:\another.mdb")
with db
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tbl.Name, DirectoryPathMain & "final.xls"
DoCmd.OpenQuery ("my_query")
.close
end with
end sub

everything is ok but the
Docmd is executing in currentdb and not in db=opendatabase("C:\another.mdb") and executing transfer and query in currentdb.
Do you know how to make him execute docmd in another database? Or maybe do you know other function that will do the same as: DoCmd.TransferSpreadsheet

Thank you in advance for any ideas.

Mathematician

 

[DJkarl]

hi guys,

do you know how to call the function "docmd" but in another DB.
for example I am working on my.mdb
and have the code inside:
sub TransferSpreadsheet()
set db=opendatabase("C:\another.mdb")
with db
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tbl.Name, DirectoryPathMain & "final.xls"
DoCmd.OpenQuery ("my_query")
.close
end with
end sub

everything is ok but the
Docmd is executing in currentdb and not in db=opendatabase("C:\another.mdb") and executing transfer and query in currentdb.
Do you know how to make him execute docmd in another database? Or maybe do you know other function that will do the same as: DoCmd.TransferSpreadsheet

Thank you in advance for any ideas.

Mathematician

The reason it isn't working is because DoCmd is part of the application object and not the database object.

Try

Dim msApp as Access.Application

Set msApp = GetObject("PathToYourDatabaseFile")

msApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tbl.Name, DirectoryPathMain & "final.xls"

set msApp = Nothing

 

[Mathematician]

Thank You very much DJkarl.