Opening Another Database from WITHIN a Database

[Andi Plotsky]

I have a database (2000.mdb) which links to my data tables (Data2000.mdb).
The database has a Main Menu screen (not a switchboard - a custom one). I
need to import records from 5 text files into the 5 different Tables in
Data2000.mdb.

Here was my plan:
Create various Import specs in Data2000.mdb
Create a Macro (in Data2000.mdb) which calls each of the specs
sequentially.

Place a button on the Main Menu (2000.mdb), which closes the current
database (optional), opens Data2000.mdb, runs the Macro (Import Reg), then
returns the user to the original Menu in the original database (2000.mdb)

I can't seem to get past running the Macro. See my code below:
Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Dim wrkJet as Workspace
Dim dbsAnother as Database

Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _
"", dbUseJet)
Workspaces.Append wrkJet

Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb")

With dbsAnother
DoCmd.RunMacro "Import Reg"
End With

End Sub

I keep getting a "Can't find Macro Import Reg" error.
Any suggestions? Is there an easier way to automate this? Is part of the
problem is having 2000.mdb open (and linked) to Data2000.mdb when I try to
execute this?

Thanks in advance for any assistance.
--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)

[Larry Linson]

In 2000.mdb, link the tables in the Data2000.MDB, and import the data into
the linked tables from 2000.mdb. You are complicating a simple matter trying
to _run_ the update in the data database.

Larry Linson
Microsoft Access MVP

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:wF*************@bignews5.bellsouth.net...

I have a database (2000.mdb) which links to my data tables (Data2000.mdb).
The database has a Main Menu screen (not a switchboard - a custom one). I
need to import records from 5 text files into the 5 different Tables in
Data2000.mdb.

Here was my plan:
Create various Import specs in Data2000.mdb
Create a Macro (in Data2000.mdb) which calls each of the specs
sequentially.

Place a button on the Main Menu (2000.mdb), which closes the current
database (optional), opens Data2000.mdb, runs the Macro (Import Reg), then
returns the user to the original Menu in the original database (2000.mdb)

I can't seem to get past running the Macro. See my code below:
Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim wrkJet as Workspace
Dim dbsAnother as Database

Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _
"", dbUseJet)
Workspaces.Append wrkJet

Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb")

With dbsAnother
DoCmd.RunMacro "Import Reg"
End With

End Sub

I keep getting a "Can't find Macro Import Reg" error.
Any suggestions? Is there an easier way to automate this? Is part of the
problem is having 2000.mdb open (and linked) to Data2000.mdb when I try to
execute this?

Thanks in advance for any assistance.
--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)

[Andi Plotsky]

That's what I tried at first, but when it was time to specify which existing
table to import it to, the linked tables weren't listed. Is there a way
around this? (I was using TransferText in the Macro and a saved Spec.)

Andi



"Larry Linson" <bo*****@localhost.not> wrote in message
news:vP******************@nwrddc03.gnilink.net...

In 2000.mdb, link the tables in the Data2000.MDB, and import the data into
the linked tables from 2000.mdb. You are complicating a simple matter trying to _run_ the update in the data database.

Larry Linson
Microsoft Access MVP

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:wF*************@bignews5.bellsouth.net...

I have a database (2000.mdb) which links to my data tables (Data2000.mdb). The database has a Main Menu screen (not a switchboard - a custom one). I need to import records from 5 text files into the 5 different Tables in
Data2000.mdb.

Here was my plan:
Create various Import specs in Data2000.mdb
Create a Macro (in Data2000.mdb) which calls each of the specs
sequentially.

Place a button on the Main Menu (2000.mdb), which closes the current
database (optional), opens Data2000.mdb, runs the Macro (Import Reg), then returns the user to the original Menu in the original database (2000.mdb)
I can't seem to get past running the Macro. See my code below:
Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As Integer,

X

As Single, Y As Single)

Dim wrkJet as Workspace
Dim dbsAnother as Database

Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _
"", dbUseJet)
Workspaces.Append wrkJet

Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb")

With dbsAnother
DoCmd.RunMacro "Import Reg"
End With

End Sub

I keep getting a "Can't find Macro Import Reg" error.
Any suggestions? Is there an easier way to automate this? Is part of

the problem is having 2000.mdb open (and linked) to Data2000.mdb when I try to execute this?

Thanks in advance for any assistance.
--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)

[david epsom dot com dot au]

Why use a saved spec? Link the tables, then use a saved query
(an append query) to transfer records from one table to another.

In a macro, use RunQuery to do the transfer.
In code use RunQuery or DB.Execute

(david)

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:jX*****************@bignews2.bellsouth.net...

That's what I tried at first, but when it was time to specify which existing table to import it to, the linked tables weren't listed. Is there a way
around this? (I was using TransferText in the Macro and a saved Spec.)

Andi



"Larry Linson" <bo*****@localhost.not> wrote in message
news:vP******************@nwrddc03.gnilink.net...

In 2000.mdb, link the tables in the Data2000.MDB, and import the data into

the linked tables from 2000.mdb. You are complicating a simple matter trying

to _run_ the update in the data database.

Larry Linson
Microsoft Access MVP

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:wF*************@bignews5.bellsouth.net...

I have a database (2000.mdb) which links to my data tables

(Data2000.mdb). The database has a Main Menu screen (not a switchboard - a custom one). I

need to import records from 5 text files into the 5 different Tables

in Data2000.mdb.

Here was my plan:
Create various Import specs in Data2000.mdb
Create a Macro (in Data2000.mdb) which calls each of the specs
sequentially.

Place a button on the Main Menu (2000.mdb), which closes the current database (optional), opens Data2000.mdb, runs the Macro (Import Reg),

then returns the user to the original Menu in the original database (2000.mdb)
I can't seem to get past running the Macro. See my code below:
Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As Integer,

X

As Single, Y As Single)

Dim wrkJet as Workspace
Dim dbsAnother as Database

Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _
"", dbUseJet)
Workspaces.Append wrkJet

Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb")

With dbsAnother
DoCmd.RunMacro "Import Reg"
End With

End Sub

I keep getting a "Can't find Macro Import Reg" error.
Any suggestions? Is there an easier way to automate this? Is part of

the problem is having 2000.mdb open (and linked) to Data2000.mdb when I

try to execute this?

Thanks in advance for any assistance.
--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)

[Andi Plotsky]

Actually, I ended up with the simplest solution possible - I gave my import
files a different name (e.g. Events1.txt, instead of Events.txt, which would
have overwritten the table) and did a TransferText macro (into Events
Table)and it worked fine. But I'll keep the AppendQuery option in mind when
I don't have the "power" to dictate the filename specs.

Thanks for your help, Larry and David -

Andi
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:40***********************@news.syd.swiftdsl.c om.au...

Why use a saved spec? Link the tables, then use a saved query
(an append query) to transfer records from one table to another.

In a macro, use RunQuery to do the transfer.
In code use RunQuery or DB.Execute

(david)

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:jX*****************@bignews2.bellsouth.net...

That's what I tried at first, but when it was time to specify which

existing

table to import it to, the linked tables weren't listed. Is there a way
around this? (I was using TransferText in the Macro and a saved Spec.)

Andi



"Larry Linson" <bo*****@localhost.not> wrote in message
news:vP******************@nwrddc03.gnilink.net...

In 2000.mdb, link the tables in the Data2000.MDB, and import the data into the linked tables from 2000.mdb. You are complicating a simple matter

trying

to _run_ the update in the data database.

Larry Linson
Microsoft Access MVP

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:wF*************@bignews5.bellsouth.net...
> I have a database (2000.mdb) which links to my data tables

(Data2000.mdb).

> The database has a Main Menu screen (not a switchboard - a custom one).

I

> need to import records from 5 text files into the 5 different Tables in > Data2000.mdb.
>
> Here was my plan:
> Create various Import specs in Data2000.mdb
> Create a Macro (in Data2000.mdb) which calls each of the specs
> sequentially.
>
> Place a button on the Main Menu (2000.mdb), which closes the current > database (optional), opens Data2000.mdb, runs the Macro (Import

Reg), then

> returns the user to the original Menu in the original database

(2000.mdb)

>
> I can't seem to get past running the Macro. See my code below:
>
>
> Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As

Integer,

X
> As Single, Y As Single)
>
> Dim wrkJet as Workspace
> Dim dbsAnother as Database
>
> Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _
> "", dbUseJet)
> Workspaces.Append wrkJet
>
> Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb")
>
> With dbsAnother
> DoCmd.RunMacro "Import Reg"
> End With
>
> End Sub
>
> I keep getting a "Can't find Macro Import Reg" error.
> Any suggestions? Is there an easier way to automate this? Is part

of the

> problem is having 2000.mdb open (and linked) to Data2000.mdb when I

try

to

> execute this?
>
> Thanks in advance for any assistance.
>
>
> --
> Andi Plotsky
> IRIS, LLC
> 2859 Galahad Drive
> Atlanta, GA 30345
>
> 404-321-9459 (office)
> 404-636-2331 (fax)
>
>