Run Time Error '3024' when creating archives

[polymatt]

I need to move data in a table to tables in new databases for archiving; & being able to click a button & have the data append & delete is my goal.

I made a test database to work on the code. I'm an Access beginner & so go easy:

This code creates the archive & runs ArchiveTables (below) when I click a button. The code works fine when I don't use the variable name (strDBName) & instead just point to specific locations but I need the names to be generated as of the date. It's for my boss & to him it's important that that's how it works (so he can just push a button & have it archive in to a new file depending on the date).

Private Sub Archive_Click()
Dim dbnew As DAO.Database
Dim strDBName As String
Dim ArchiveDB As String
strDBName = Format(Date, "mmm d yyyy") & " Archive"
ArchiveDB = "G:\" & strDBName
Set dbnew = DBEngine(0).CreateDatabase("G:\" & strDBName, dbLangGeneral)
ArchiveTables (ArchiveDB)

dbnew.Close
Set dbnew = Nothing
End Sub

This is the TransferDatabase code (I want it to delete, too, but don't know how & want to get the transfer to work first):

Private Sub ArchiveTables(ArcDB As String)
Dim strDBName As String
strDBName = Format(Date, "mmm d yyyy") & " Archive"
DoCmd.TransferDatabase acExport, "Microsoft Access", "G:\" & strDBName, acTable, "tbl_testTable", "tbl_testTable", 0
End Sub

When I run this with the strDBName it gives me a

Run Time Error '3024':

Could not find file 'G:\Nov 5 2013 Archive'.

Even though the file was created & I can see it on the server; but no data was transferred to it.

I'm at a loss, & as I said, new to Access & wanting to get better.

If anybody has any advice with my problem, I would sincerely appreciate it.

 

[CJ_London]

It looks to me live you need to add the file type to Archive - depending on what you are using that might be .mdb or .mde or .accdb etc

 

[AbsoluteAccess]

Have you you tried putting the extension on the file name? Eg DBName.accdb

 

[polymatt]

Adding the extension worked; thank you!

I'm going to mark the thread as "Solved", but if anybody's still reading, what would be the best way to delete the records? Adding "delete" to the code of the same button, or to a separate button?

 

[AbsoluteAccess]

Which records are you wanting to delete. The ones from the main database after you archive them?

 

[polymatt]

The ones from the main database. I figured it out, though; I just added VBA to a button to clear the table with a Yes/No confirmation.

However: I'm now having the first problem again. After updating my code to include the ".accdb" extension, the TransferDatabase code executes 90% of the time (with that annoying security pop-up, but whatever), but 10% of the time it still gives me the same '3024' Could not find file error.

It's totally intermittent & doesn't seem to matter what I do. I can click the button 10 times & have 4 work, then 1 error, then 5 work, or 2 work, then 2 error, then 6 work.

It's "good enough" I guess, but I don't want to have to explain to my boss (& whoever else uses the database) to "just keep clicking" if the error appears, you know?

My updated code is:

Archive (works well)

Private Sub CreateArchiveDB_Click()
Dim dbnew As DAO.Database
Dim strfile As String
Dim strDBName As String
Dim dbPath As String
Dim ArcPath As String
Dim ArchiveDB As String
strDBName = Format(Now, "mmm_d_yyyy-hh_nn_ss") & "_Archive"
ArchiveDB = "H:\Archives\" & strDBName & ".accdb"
Set dbnew = DBEngine(0).CreateDatabase("H:\Archives\" & strDBName & ".accdb", dbLangGeneral)
ArchiveTables (ArchiveDB)

dbnew.Close
Set dbnew = Nothing
End Sub

Transfer (where I get the error)

Private Sub ArchiveTables(ArcDB As String)
Dim strDBName As String
strDBName = Format(Now, "mmm_d_yyyy-hh_nn_ss") & "_Archive"
DoCmd.TransferDatabase acExport, "Microsoft Access", "H:\Archives\" & strDBName & ".accdb", acTable, "tbl_testTable", "tbl_testTable", 0
End Sub

(H:\ is a network drive where both the database & the Archives folder are stored.)

Thanks everyone for all of your continued help & advice!

 

[JHB]

Try to put in a DoEvents before the ArchiveTables.

Set dbnew = DBEngine(0).CreateDatabase("H:\Archives\" & strDBName & ".accdb", dbLangGeneral)
DoEvents
ArchiveTables (ArchiveDB)

 

[polymatt]

I added the DoEvents, but I'm still getting the error; it feels like it's occurring less often, but that's probably just my own bias.

 

[JHB]

What happen if you add a breakpoint at the ArchiveTables (ArchiveDB) line, and wait 5 seconds before you continues the code.
It is only to see if the code run faster as the system is able to create the new database?
If the problem is this, a loop could be a solution.
Another idea is to create the new database on C-drive, and see if you still get the error.