Exporting a 97 table to another 97 database

[JodyAmy]

I am trying to automate an export of a table in Access 97 to another 97 DB. The only catch is that I want the name of the file in the external DB to have a dynamic name created at the time of the export. I have been able to create the table in the external DB using Transfer Database, but the file is still linked to the original DB. I only want the new table to reside on the external DB w/out any links. That is my only catch. I'm not sure if I can do a Create Table query or a Select Into into an external DB.

Any suggestions?

Thanks,

Jody

 

[JodyAmy]

I've been able to answer my own problem. When I was creating a copy of the table w/ the new file name that I wanted, it was creating it linking into my application (storing my tables in another DB to avoid severe fragmentation). Therefore, when I exported the new table, the link was exported along w/ the new table.

To remedy the situation, I used a "Select ... into" statement to create a local copy of my file, and then I exported the table. Worked like a charm!! Here is the code:

Private Sub cmdOK_Click()

Dim varX As String
Rmonth = Me.txtMonth.Value
Ryear = Me.txtYear.Value
varX = "BIG RAW_" & Rmonth & "_" & Ryear

DoCmd.RunSQL "Select * into HOLD_RAW from raw"
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\_External_mdbs\RAW_HIST.mdb", acTable, "HOLD_RAW", varX
DoCmd.Close

End Sub

I will be updating this code so the the export file location is dynamic also. It will look for the file in a certain folder on a server location.

Jody