Export table from a different database to excel

[jjq2r01h]

Hi
I have 2 databases, mymacros.mdb and otherdb.mdb
I am writing some vba code in mymacros.mdb to try and export a table from otherdb to excel. I do this becuase there is a new copy of otherdb created on a daily basis.

I have tried using docmd.output and docmd.transferspreadsheet to achieve this but dont know how to specify that the table I am exporting is in the otherdb.mdb file.

Any help would be appreciated.

Thanks / Jag

 

[pr2-eugin]

Hello, Welcome to AWF

Simple and quick work around would be to simply link the OtherDB.mdb tables to this MyMacros.mdb, so you can just export the table like a normal table.

 

[jjq2r01h]

Many thanks for this Paul. The following code works great to delete the link if one exists, establish the link again, export table to excel and then delete all records from the table.

Sub Macro3()
On Error GoTo Macro3_Err

If Len(CurrentDb.TableDefs("tblEvents").Connect) > 0 Then
DoCmd.DeleteObject acTable, "tblEvents"
End If

Macro3_Err:

DoCmd.TransferDatabase acLink, "Microsoft Access", _
otherdb path, acTable, _
"tblEvents", "tblEvents"

DoCmd.OutputTo acOutputTable, "tblEvents", "ExcelWorkbook(*.xlsx)", excel path, False, "", , acExportQualityPrint

CurrentDb.Execute "DELETE FROM tblEvents IN otherdbpath

End Sub

 

[pr2-eugin]

Good that you have found a way ! Although, I have some comments. Why would you have to delete and relink? When the table has been replaced in Other.mdb, the link will only pick what it is available. Adding/Checking for link and relink IMVHO is a overhead you are adding, which is not required !

 

[jjq2r01h]

OK, understood. Wasn't thinking straight. I guess once the link has been established, I dont have to redefine it even though the otherdb file gets replaced on a daily basis.

Many thanks. Appreciate all the help.

 

[pr2-eugin]

You're welcome ! Unless the OtherDB.mdb's name is changed on a daily basis ! Then you might need some code to relink the tables. Good Luck !