Copy each table to a new database

[CheekyCharli]

Hi guys,

I'm afraid I'm a classic Access VBA joker - learned in Excel and making a bumpy transition.

I have this code, which, in my mind, should work - I littered it with debug.print commands whilst I built it and it's definitely identifying the right (user created tables in the current database). My problem is that it tells me the created database is in use when I try to copy the tables into it.

What am I missing?

Thanks in advance

Sub copytables()

Dim cOpieddBase As Database
Dim tDef As TableDef

Set cOpieddBase = CreateDatabase(Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "copy.mdb", dbLangGeneral)
For Each tDef In CurrentDb.TableDefs
If tDef.Attributes = 0 Then DoCmd.CopyObject cOpieddBase.Name, , acTable, tDef.Name
Next tDef

End Sub

[CreganTur]

Welcome to the forum- it's always good to see new members.

Try closing the connection to your created database, and then open a new connection to the same database. You can reopen the same connection (cOpieddBase).

I think the issue may be because your initial connection is a CreateDatabase connection. So closing that connection and then opening a new, normal connection to the external db may clear up this issue for you.

[CheekyCharli]

Thanks, that seems to make a lot of sense.

I'm afraid I can't test it until a mammoth query has finished but I wanted to give feedback & thanks