VBA (Copying Tables)

[AJHY]

Hi

I am trying to copy tables from one DB to another. That's fine with the code below, but could someone explain how the code would work without having the C:\My_Documents\All_Weeks.mdb code in which is the same path as the DB I have open.

I have tried Currentdb etc but nothing working.

Thanks in advance

Andy

Private Sub Test()
 
Dim strDBPath As String, strTableName As String, appAccess As Access.Application, dbsCurrent As Database
 
 
strDBPath = "C:\My_Documents\BS.mdb"
strTableName = "Users"
 
'Create a New Instance of Access
Set appAccess = CreateObject("Access.Application")
Set dbsCurrent = CurrentDb
 
 
'Open the External Database in Access Window
appAccess.OpenCurrentDatabase strDBPath
 
'Copies the Users table from Batley Spen DB and pastes into the current All_Users DB
appAccess.DoCmd.CopyObject "C:\My_Documents\All_Weeks.mdb", "TBL_BS", acTable, "Users"
 
End Sub

[nspader]

It should look something like this:

strDBPath = curpath & "\Barwon.mdb"

Hope it works for you.

Nick

[NeoPa]

You can find the folder of the current database from CurrentDb.Name. This includes the db name too of course, but the folder can easily be extracted.