TransferDatabase and run-time error 3024 (1 Viewer)

gaidheal

Registered User.
Local time
Today, 04:19
Joined
Oct 7, 2008
Messages
18
I am creating an archive method for a database that has grown too large. I have queries that generate the data I need to archive, and I have created code that creates a new database that I want to export the archive data to. I then use DoCmd.TransferDatabase to send the results of the queries to the new database.
I am getting a run-time error 3024 which tells me the destination database cannot be found. Yet it does exist, in precisely the same path and name as it says it cannot find.
I know nothing is misspelled as the path name used to create the new database is passed to the routine that uses transferdatabase.
Does anyone know if this is a bug and how to get around it?

Access2007 - writing to an .accdb, not an .mdb

Private Sub ArchiveTables(ArcDB As String)
DoCmd.TransferDatabase acExport, "Microsoft Access", ArcDB, acTable, "ArchiveBasicDataQuery", "Basic_Data", 0

End Sub
 

boblarson

Smeghead
Local time
Today, 04:19
Joined
Jan 12, 2001
Messages
32,059
What is the code that creates the database, how are you calling the ArchiveTables and what are you passing for ArcDB?
 

gaidheal

Registered User.
Local time
Today, 04:19
Joined
Oct 7, 2008
Messages
18
Thanks for the reply, I hope you can see something I'm missing.

Private Sub CreateArchiveDB()
Dim dbnew As DAO.Database
Dim strfile As String
Dim dbPath As String
Dim ArcPath As String
Dim ArchiveDB As String
dbPath = GetBackEndPath()
ArcPath = dbPath & "\Archive"
If Dir(ArcPath, vbDirectory) = "" Then
MkDir (dbPath & "\Archive")
End If
ArchiveDB = ArcPath & "\WorkRequestBE.accdb)"
Set dbnew = DBEngine(0).CreateDatabase(ArcPath & "\WorkRequestBE.accdb", dbLangGeneral)
ArchiveTables (ArchiveDB)

dbnew.Close
Set dbnew = Nothing
End Sub

While the code is still active I can put my cursor over the variable containing the pathname and it matches exactly the location of the destination database
 

boblarson

Smeghead
Local time
Today, 04:19
Joined
Jan 12, 2001
Messages
32,059
I believe the problem is that isn't actually creating the database. I could be mistaken but you might try this code instead for the creation:
Code:
Dim acc As Access.Application
 
Set acc = New Access.Application
acc.NewCurrentDatabase ArchiveDB 
acc.CloseCurrentDatabase
acc.Quit
 

gaidheal

Registered User.
Local time
Today, 04:19
Joined
Oct 7, 2008
Messages
18
No, the database is there and I am able to open it. So it does exist.
 

boblarson

Smeghead
Local time
Today, 04:19
Joined
Jan 12, 2001
Messages
32,059
Okay, I just found it. It is this line:

ArchiveDB = ArcPath & "\WorkRequestBE.accdb)"


you need to remove the paren at the end:

ArchiveDB = ArcPath & "\WorkRequestBE.accdb"
 

gaidheal

Registered User.
Local time
Today, 04:19
Joined
Oct 7, 2008
Messages
18
Bingo, that was it. Good eye on that one. I'm surprised Access didn't catch the syntax error, but I'm glad you saw it. Everything is working just it is supposed to.

Thanks for your help!
 

Users who are viewing this thread

Top Bottom