Code To Export Table Not Working

[SpankYou]

Hey,

Basically the code below, should first create a database and then copy the tables from one database to another. At the moment I am using another access database file to perform this task, (I would code it in VB, but we don't currently own a version). This code works a treat if you copy the CurrentDB where the code is running to a new database, but I need the code to access another database, the code I have used is below. As you can see I have replaced the CurrentDB with the necessary path name. The error I get though is...

The Microsoft Jet database engine could not find the object 'TableNameInHere'. Make sure the object exists and that you spell its name and the path name correctly.

I understand the problem but as it works using currentDB i'm stuck Any help is greatly appreciated,

Cheers

Sam

Code Is here

Sub sExport(strDBName As String)
On Error GoTo E_Handle
Dim db As DAO.Database
Dim tdf As DAO.TableDef
If Dir(strDBName) <> &quot;&quot; Then Kill strDBName
Set db = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
db.Close
Set db = DBEngine.OpenDatabase(&quot;C:\Documents and Settings\SSh\Desktop\Copy of Crack2.mdb&quot
'Set db = CurrentDb

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> &quot;MSys&quot; Then
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, strDBName, acTable, tdf.Name, tdf.Name
End If

Next tdf
MsgBox &quot;Tables copied OK&quot;, vbOKOnly, &quot; &quot;
sExit:
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly, Err.Number
Resume sExit
End Sub


&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[SBendBuckeye]

I think you need to reference your db object within it.
I think this might do it:

db.DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, strDBName, acTable, tdf.Name, tdf.Name

If that doesn't work, try:

db.Application.DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, strDBName, acTable, tdf.Name, tdf.Name

Good Luck!


Have a great day!

j2consulting@yahoo.com

 

[SpankYou]

Hey SBendBuckEye,

I did as you said and replaced my code line...

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, strDBName, acTable, tdf.Name, tdf.Name

with your first code line and then your second code line. Unfortunately neither work and come up with the same error. &quot;Method or Data Member not found!&quot;. The first line highlights the .Docmd, and the second highlights the .Application. Any ideas on where to go from here?

Cheers for your help

Sam



&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[combo]

DoCmd is an access application method, so you need to reference access and create access application object before you use it.

combo