DoCmd.TransferSpreadsheet for exporting table from another Access database than the current one (2 Viewers)

dianaha

New member
Local time
Today, 10:11
Joined
Jun 3, 2021
Messages
16
Hi everyone,

I am struggling with a new problem, when I want to export a Table into excel file.

Before it worked well as I have used "CurrentDb" (forms and tables in the same Access doucment).
but when I want to export a table from another Access DB (where there are only the table) by using the trick :
Set wrk = DBEngine(0)
Set dbX = wrk.OpenDatabase(data_base_name)

This tricks has worked for all my SQL statement because I usually use the "dbX.Execute" statement.

I want to do so in order to make frontend and backend App. (the forms where it would be possible to export are completly separated from the unique database located in another path)

The sub stops everytime at code line where there is the "DoCmd.TransferSpreadsheet" statement.
Error n°3011: the object "query_to_export" doesn't exist

I have verified in the data base where the table (I want to export) is located, the query "query_to_export" has been actually created
Plus, the Excel document has been created without nothing inside, but with the asked path "C:\Users\username\Documents\testexport.xlsx"


I fear that it is because the exportation is only possible when the tables are in the same Access document as the VBA modules where I execute the code...
Does actually the statement "DoCmd" work only in the current access project?...

Is tehre another way to export a table from extern database without using the code "DoCmd.TransferSpreadsheet" but rather CurrentDb.Execute ?

I thank you a lot in advance for your help/suggestions,

Kind regards,

Laura

Code:
 Sub export_table_to_excel()

Dim wrk As Workspace
Dim dbX As Database
Dim qdf As QueryDef
Set wrk = DBEngine(0)
Set dbX = wrk.OpenDatabase(data_base_name)

strSQL = "SELECT * FROM [TABLE_NAME]"

Set qdf = dbX.CreateQueryDef("query_to_export", strSQL)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query_to_export", "C:\Users\username\Documents\testexport.xlsx", True

'delete query in Access
dbX.QueryDefs.Delete "query_to_export"

Set qdf = Nothing
dbX.Close
Set dbX = Nothing
wrk.Close
Set wrk = Nothing

End Sub
 

dianaha

New member
Local time
Today, 10:11
Joined
Jun 3, 2021
Messages
16
*precision:
the string called data_base_name is a global variable which is set at the opening of the form, that is why I don't declare it in the sub.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:11
Joined
May 7, 2009
Messages
19,169
you don't need to open the other db. you can still use Currentdb:
Code:
 Sub export_table_to_excel()

'Dim wrk As Workspace
Dim dbX As Database
Dim qdf As QueryDef
'Set wrk = DBEngine(0)
Set dbX = Currentdb

strSQL = "SELECT * FROM [TABLE_NAME] IN '" & data_base_name & "'"

Set qdf = dbX.CreateQueryDef("query_to_export", strSQL)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query_to_export", "C:\Users\username\Documents\testexport.xlsx", True

'delete query in Access
dbX.QueryDefs.Delete "query_to_export"

Set qdf = Nothing
dbX.Close
Set dbX = Nothing
wrk.Close
Set wrk = Nothing

End Sub
 

dianaha

New member
Local time
Today, 10:11
Joined
Jun 3, 2021
Messages
16
I find a solution, not the best, but at least it works:

Import the table in the current Access database (which should contain only the forms..), export with the "DoCmd.TransferSpreadsheet" statement,
then clean up: delete the query and the table.

If someone has a better solution in order to avoid all those steps I would be glad to know it :)


Code:
Sub export_from_externDB_into_excel()
Dim qdf As QueryDef
Dim strSQL as String

'Import the table called "TABLE_NAME_BACKEND" from the principal database called data_base_name in the front end App:
DoCmd.TransferDatabase acImport, "Microsoft Access", data_base_name, acTable, "TABLE_NAME_BACKEND", "TABLE_NAME_FRONTEND", False

'Create query for selection of all (or specific) records
strSQL = "SELECT * FROM [TABLE_NAME_FRONTEND]" 'sql statement
Set qdf = CurrentDb.CreateQueryDef("query_to_export", strSQL)

' now export into Excel file (obviously possible only through the current database.....)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query_to_export", "C:\Users\username\Documents\test_export.xlsx", True

'delete query in Access
CurrentDb.QueryDefs.Delete "query_to_export"

'Delete the table because we keep only the forms in the front App
DoCmd.DeleteObject acTable, "TABLE_NAME_FRONTEND"

End sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:11
Joined
Sep 21, 2011
Messages
14,037
What was wrong with arnelgp's solution?
 

dianaha

New member
Local time
Today, 10:11
Joined
Jun 3, 2021
Messages
16
Because if the table is not in the CurrenDb it does not work. (the "DoCmd.TransferSpreadsheet")
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:11
Joined
Sep 21, 2011
Messages
14,037
That is not what I see from that code?:unsure:
 

dianaha

New member
Local time
Today, 10:11
Joined
Jun 3, 2021
Messages
16
I have already tried with the code of arnelgp but I needed to change "a little" but that causes actually the bug:
"Set dbX = wrk.OpenDatabase(path_to_the_data_base)" instead of "Set dbX = Currentdb"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,970
Import the table in the current Access database (which should contain only the forms..), export with the "DoCmd.TransferSpreadsheet" statement,
then clean up: delete the query and the table.
Importing the table just causes bloat that requires a C&R to fix. Instead, just link to the table
I have already tried with the code of arnelgp but I needed to change "a little" but that causes actually the bug:
"Set dbX = wrk.OpenDatabase(path_to_the_data_base)" instead of "Set dbX = Currentdb"
Try using the code as arnel wrote it. He is using a different technique than you are using. His solution takes advantage of the ability to include a full database name in a query's From clause. You don't need to actually link to the table to use it this way. I didn't test the code so I can't confirm that it works but you do need to use it as written. Just supply your database name and your table name in the expression below:

strSQL = "SELECT * FROM [TABLE_NAME] IN '" & data_base_name & "'"
 

Users who are viewing this thread

Top Bottom