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


[code=vb]
Private Sub Test()

Dim strDBPath As String, strTableName As String, appAccess As Access.Applicat ion, dbsCurrent As Database


strDBPath = "C:\My_Document s\BS.mdb"
strTableName = "Users"

'Create a New Instance of Access
Set appAccess = CreateObject("A ccess.Applicati on")
Set dbsCurrent = CurrentDb


'Open the External Database in Access Window
appAccess.OpenC urrentDatabase strDBPath

'Copies the Users table from Batley Spen DB and pastes into the current All_Users DB
appAccess.DoCmd .CopyObject "C:\My_Document s\All_Weeks.mdb ", "TBL_BS", acTable, "Users"

End Sub[/code]

[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.