Link external Databases

[Sam]

Hey,

Basically the code below, should first create a database and then copy
the tables from one external database to this newly created database.
At the moment I am using a third 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) <> "" Then Kill strDBName
Set db = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
db.Close
Set db = DBEngine.OpenDatabase("C:\Documents and
Settings\SSh\Desktop\Copy of Crack2.mdb")

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

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

[Douglas J. Steele]

The DoCmd.TransferDatabase is working in your "local" database, not in the
one you opened using the OpenDatabase statement. That means it doesn't know
anything about the TableDef objects you're finding in your loop.

You could try using Automation. Take a look at
http://support.microsoft.com/?id=210111 If you look at the code associated
with the Method 3 example, you'll see how to use DoCmd and have it apply to
the other database.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Sam" <Do*********@hotmail.com> wrote in message
news:28**************************@posting.google.c om...

Hey,

Basically the code below, should first create a database and then copy
the tables from one external database to this newly created database.
At the moment I am using a third 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) <> "" Then Kill strDBName
Set db = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
db.Close
Set db = DBEngine.OpenDatabase("C:\Documents and
Settings\SSh\Desktop\Copy of Crack2.mdb")

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

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

[Sam]

Hey Douglas,

Thanks for that I'll have a browse through, currently I have managaed
to set it up so that all the tables are copied to the third database
and back out again, not to efficient but it works for now,

Cheers

Sam

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<ro**********************@news01.bloor.is.net .cable.rogers.com>...

The DoCmd.TransferDatabase is working in your "local" database, not in the
one you opened using the OpenDatabase statement. That means it doesn't know
anything about the TableDef objects you're finding in your loop.

You could try using Automation. Take a look at
http://support.microsoft.com/?id=210111 If you look at the code associated
with the Method 3 example, you'll see how to use DoCmd and have it apply to
the other database.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Sam" <Do*********@hotmail.com> wrote in message
news:28**************************@posting.google.c om...

Hey,

Basically the code below, should first create a database and then copy
the tables from one external database to this newly created database.
At the moment I am using a third 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) <> "" Then Kill strDBName
Set db = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
db.Close
Set db = DBEngine.OpenDatabase("C:\Documents and
Settings\SSh\Desktop\Copy of Crack2.mdb")

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

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