CODEWITHSUNDEEP
ms-access
Mark Pelletier
Mark Pelletier

Reputation: 1359

MSAccess - TransferDatabase - File locked open?

I am attempting the following in an AccessDb via VBA:

  1. Export a single table from current DB into a new DB in the same directory, via a Query/TransferDatabase. This seems to work as expected.

    Dim ws As Workspace
Dim db_new as Database
strPath = CurrentProject.Path & "\Backend_Database\"
strDBFilename = strPath & Raw_Count_File.accdb"

Set ws = DBEngine.Workspaces(0)
Set db_new = ws.CreateDatabase(strDBFilename, dbLangGeneral)

DoCmd.TransferDatabase acExport, "Microsoft Access", _
   strDBFilename, acTable, "tmp_RawCountFile", "Raw_TblMatchedTB"

  2. Within the same function used above (to create the new file), I am attempting next to ZIP the new file into the same directory. The result is 1K Byte ZIP file (it's an empty ZIP ).

  3. If I breakout the code segment that creates the ZIP file into separate function (i.e., under another button), the function works as expected and the proper ZIP file is created.

My Question:

  1. I am guessing the new DB file and subsequent TransferDatabase is leaving the new_db file hanging open and inaccessible to the ZIP function. I attempted to set the various objects = nothing prior to the ZIP function, but same result. Only if I exit the first function and call a second function will it work as desired.

  2. Can I add something to the end of the TransferDatabase function to ensure the resulting file will be available for the ZIP task?

  3. My preference is not to add a secondary button press to this task...

Any suggestions to get me going?

Upvotes: 0

Views: 76

Answers (1)

iDevlop
iDevlop

Reputation: 25272

Try to Set db_new = Nothing before zipping, in order to close the newly created db.

Upvotes: 1

Related Questions