Using DODCMD transfer (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
I am trying to automate the exporting of several tables and queries. I have a button that you click that executes this code. I keep getting error messages. I was wondering if there is anything else wrong with my code. Micro_results_q is actually a query but I want it to export as a table. Can I leave it like that? I have several more tables and querys I want to export but haven't added to the code yet.
error (1).JPG


Code:
Private Sub CommandTransfer_Click()
On Error GoTo SubError

strPath = "M:\Research\Lung US\Data\Exporting_OHSU\OHSU.mdb"
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Admission_DX", "Admission_DX", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Micro_Results_q", "Micro_Results_q", True

MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"


SubExit:
      Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit


End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:32
Joined
Aug 30, 2003
Messages
36,118
The error would imply that the path doesn't exist. Have you verified that it does? M would have to be mapped, most of us would use a UNC path.
 

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
Yes the path exist, it's a network drive.
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
Are you able to 1) open both of those queries in datasheet mode with no errors, and 2) open the destination mdb , manually, with no errors?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:32
Joined
Aug 30, 2003
Messages
36,118
I just tested and this code worked:

Code:
Dim strPath As String
strPath = "C:\PaulFiles\Access databases\TestStuff.mdb"
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "tblWorkDates", "tblWorkDates", True
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:32
Joined
Aug 30, 2003
Messages
36,118
By the way, if the second is a query you'd need acQuery instead of acTable.
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
By the way, if the second is a query you'd need acQuery instead of acTable.
I think acTable would be correct to export the resultset of the query. (acQuery for the definition)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:32
Joined
Aug 30, 2003
Messages
36,118
I think acTable would be correct to export the resultset of the query. (acQuery for the definition)

You could be right, I haven't used this in a production app. It is supposed to be "The type of object to import or export." so I assumed it would have to be acQuery for a query. Perhaps that argument would drive whether you exported the query itself or the results?
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
I haven't used it hardly at all either, was just reading BOL
 

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
I might try moving the access file that I'm exporting to somewhere on my desktop
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
Yes, no errors with the tables or queries or the access files.
All I can think of is that that mapped drive and folder path isn't able to connect at the time the code runs.
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
I'm sorry to beat this possibly dead horse, but if it were me I would quadruple-check that path connectivity by clicking Start, Run, and pasting:
M:\Research\Lung US\Data\Exporting_OHSU\OHSU.mdb
and hit Enter and see if the database opens.

Worst case scenario, use the full UNC path maybe.
 

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
I'm sorry to beat this possibly dead horse, but if it were me I would quadruple-check that path connectivity by clicking Start, Run, and pasting:
M:\Research\Lung US\Data\Exporting_OHSU\OHSU.mdb
and hit Enter and see if the database opens.

Worst case scenario, use the full UNC path maybe.
There was an issue with how the drive was mapped. I fixed it. Also the file extension wasn't correct and I fixed it. But now there is a different issue. It's not exporting the tables it only exporting the link. I'm trying to create a copy of part of the database so I can send it to someone not on my network.

The querys are actually exporting to the database as tables but are empty, but they are actually in the table instead of just being linked.

The file that I am using to export the tables is a frontend file. The actual tables are linked. Do I have to export the tables from the backend? Or do I need to do something different with the VBA?
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
Congrats on getting the path solved! Sometimes it really is that simple.
So when it finishes running, what exactly do you end up with in the destination database? Just a new, local, empty, table?
 

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
Congrats on getting the path solved! Sometimes it really is that simple.
So when it finishes running, what exactly do you end up with in the destination database? Just a new, local, empty, table?
I end up with a Access database that has linked tables. (I need a copy of the tables, not links) Then the querys that I exported show up as empty tables, but actually in the table, not linked.
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
Then the querys that I exported show up as empty tables, but actually in the table, not linked.
Hmm. This is the part I am focusing on for now. It seems to me that your code ought to end up with the query's resultset as a table and I am not sure why the table would be empty. Is this a regular Select query that you can open and it returns records?
 

Isaac

Lifelong Learner
Local time
Today, 04:32
Joined
Mar 14, 2017
Messages
8,738
Thanks for that, Paul. I missed that as I was focusing on the acTable argument, which should export the resultsets as a table but I think you're right - change True to False.
 

Db-why-not

Registered User.
Local time
Today, 06:32
Joined
Sep 17, 2019
Messages
159
Here is the full code I have right now. The tables with (last 5) q at the end are querys.
Code:
strPath = "M:\Research\Lung Us\Data\Exporting_OHSU\OHSU.accdb"
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Admission_DX", "Admission_DX", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Discharge_DX", "Discharge_DX", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "ICU_Admit_yes_no", "ICU_Admit_yes_no", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Micro_reports", "Micro_reports", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Micro_test_Yes_No", "Micro_test_Yes_No", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "New_Patients", "New_Patients", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Patient_Status", "Patient_Status", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Pre_Existing_Conditions", "Pre_Existing_Conditions", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Pre_existing_Yes_No", "Pre_existing_Yes_No", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Pulm_proc_yes_no", "Pulm_proc_yes_no", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_backup", "US_backup", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_CXR_CT_Findings", "US_CXR_CT_Findings", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_Demographics", "US_Demographics", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_Imaging_Reports", "US_Imaging_Reports", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_Imaging_yes_no", "US_Imaging_yes_no", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_OHSU_review", "US_OHSU_review", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_Vitals", "US_Vitals", True

DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Demographics_q", acQuery, "Demographics", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Pulmonary_Proc_q", "Pulmonary_Proc", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_ID_Main_q", "US_ID_Main", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "Micro_Results_q", "Micro_Results", True
DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, "US_CXR_CT_Imaging_q", "US_CXR_CT_Imaging", True


MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"


SubExit:
      Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit


End Sub
 

Users who are viewing this thread

Top Bottom