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
'TableNameInHer e'. 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(strDBNa me 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).Cre ateDatabase(str DBName, dbLangGeneral)
db.Close
Set db = DBEngine.OpenDa tabase("C:\Docu ments and
Settings\SSh\De sktop\Copy of Crack2.mdb")

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.TransferD atabase 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]

Re: Link external Databases

The DoCmd.TransferD atabase 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" <DookieBloke@ho tmail.com> wrote in message
news:28eac5f3.0 310130221.618e0 4a1@posting.goo gle.com...[color=blue]
> 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
> 'TableNameInHer e'. 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(strDBNa me 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).Cre ateDatabase(str DBName, dbLangGeneral)
> db.Close
> Set db = DBEngine.OpenDa tabase("C:\Docu ments and
> Settings\SSh\De sktop\Copy of Crack2.mdb")
>
> For Each tdf In db.TableDefs
> If Left(tdf.Name, 4) <> "MSys" Then
> DoCmd.TransferD atabase 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[/color]

[Sam]

Re: Link external Databases

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_djsteel e@NOSPAM_canada .com> wrote in message news:<rowib.314 548$Lnr1.272348 @news01.bloor.i s.net.cable.rog ers.com>...[color=blue]
> The DoCmd.TransferD atabase 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" <DookieBloke@ho tmail.com> wrote in message
> news:28eac5f3.0 310130221.618e0 4a1@posting.goo gle.com...[color=green]
> > 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
> > 'TableNameInHer e'. 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(strDBNa me 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).Cre ateDatabase(str DBName, dbLangGeneral)
> > db.Close
> > Set db = DBEngine.OpenDa tabase("C:\Docu ments and
> > Settings\SSh\De sktop\Copy of Crack2.mdb")
> >
> > For Each tdf In db.TableDefs
> > If Left(tdf.Name, 4) <> "MSys" Then
> > DoCmd.TransferD atabase 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[/color][/color]