open a query from different databases

[Guest]

I want to know, if I can use some VBA code to open a query from a different
database. Say, for instances, I am in my main database, and I have a button
on a form. I want the button to open up the query that I created in my other
database. Is that possible? If so, can you help me with the code?

 

[Granny Spitz via AccessMonster.com]

I want the button to open up the query that I created in my other
database.

First, create a new query and open it in SQL view. Paste the following in it
and replace the path and names with yours:

SELECT *
FROM [;DATABASE=C:\PathToDB\db1.mdb;].Query1;

Save the new query and name it something like qryShowQryInOtherDB. Open your
form in design view and create a new button. In the button's click event,
paste this:

DoCmd.OpenQuery "qryShowQryInOtherDB"

 

[Guest]

It is not allowing me to execute the query. When I run the query in SQL view
it tells me that ,"An action query cannot be used as a row source." I don't
know, if this will make a difference, but my other database which is called
"EXAMPLE.mdb", I have an append query called, "Update". Is this the reason
why it will not allow me to use the code in my main database which is called,
"DATA.mdb"? Thanking you in advance for your help.

I want the button to open up the query that I created in my other
database.

First, create a new query and open it in SQL view. Paste the following in it
and replace the path and names with yours:

SELECT *
FROM [;DATABASE=C:\PathToDB\db1.mdb;].Query1;

Save the new query and name it something like qryShowQryInOtherDB. Open your
form in design view and create a new button. In the button's click event,
paste this:

DoCmd.OpenQuery "qryShowQryInOtherDB"

 

[Granny Spitz via AccessMonster.com]

Is this the reason
why it will not allow me to use the code in my main database

Yes. An action query (an append query is an action query) can be executed
from another database, but not with the OpenQuery method, which is used for
opening SELECT queries. You'll need to get the syntax of the action query
and modify it to work in another database.

Open your EXAMPLE.mdb database and then open your action query in SQL view.
Copy the text in SQL view and paste it into your button's click event of the
form in your main database. You need to do some modifications, so that the
correct database's tables are used. For example, if this was the text of the
query's SQL view:

INSERT INTO tblReceiver
SELECT *
FROM tblData
ORDER BY ID;

then you'd paste this into a VBA procedure (your button's click event) using
CurrentDb.Execute and modify it to use the path and file name for the tables
involved, like this (beware of word wrap since this is 4 lines, with an
ampersand and underscore at the end of every line except for the last line):

CurrentDb.Execute "INSERT INTO [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].
tblReceiver " & _
"SELECT * " & _
"FROM [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].tblData " & _
"ORDER BY ID;", dbFailOnError

If you have trouble with the syntax of your own query, please copy and paste
the SQL from SQL view into your next message, and I'll try to help you
translate it into VBA code.

 

[Guest]

I am having problems w/the syntax of my query. I have posted my append query
below. I glady appreciate your help.

INSERT INTO [Audit Data] ( [Date Entered], [Time], [User's Name], Division,
[Filing Date], [Officer Division], [Zone], [File ID], [Issue Date], [Issue
Time], [Last Name], [First Name], [Middle Name], [Street Number], [Street
Address], [Apt No], City, State, Zip, Race, Sex, Height, DOB, [DL Number],
[License Held?], [DL Exp Date], SSN, [DL State], [Vehicle Make], [Veh Year],
[Vehicle Color], [Vehicle Lic No], [Veh Lic State], [Veh Plate Type], [Veh
Special Code], VIN, [Comm Veh], [Haz Mat], [Location Number], [Location
Street], Accident, Airport, Charge1, Speed1, Charge2, Speed2, Charge3,
Speed3, Charge4, Speed4, Charge5, Speed5, [Officer ID], [Court Date], [Cont
on Motion of], Arrested, [Student Initial], Result, [Result Date] )
SELECT TEST.[Date Entered], TEST.Time, TEST.[User's Name], TEST.Division,
TEST.[Filing Date], TEST.[Officer Division], TEST.Zone, TEST.[File ID],
TEST.[Issue Date], TEST.[Issue Time], TEST.[Last Name], TEST.[First Name],
TEST.[Middle Name], TEST.[Street Number], TEST.[Street Address], TEST.[Apt
No], TEST.City, TEST.State, TEST.Zip, TEST.Race, TEST.Sex, TEST.Height,
TEST.DOB, TEST.[DL Number], TEST.[License Held?], TEST.[DL Exp Date],
TEST.SSN, TEST.[DL State], TEST.[Vehicle Make], TEST.[Veh Year],
TEST.[Vehicle Color], TEST.[Vehicle Lic No], TEST.[Veh Lic State], TEST.[Veh
Plate Type], TEST.[Veh Special Code], TEST.VIN, TEST.[Comm Veh], TEST.[Haz
Mat], TEST.[Location Number], TEST.[Location Street], TEST.Accident,
TEST.Airport, TEST.Charge1, TEST.Speed1, TEST.Charge2, TEST.Speed2,
TEST.Charge3, TEST.Speed3, TEST.Charge4, TEST.Speed4, TEST.Charge5,
TEST.Speed5, TEST.[Officer ID], TEST.[Court Date], TEST.[Cont on Motion of],
TEST.Arrested, TEST.[Student Initial], TEST.Result, TEST.[Result Date]
FROM TEST;

Is this the reason
why it will not allow me to use the code in my main database

Yes. An action query (an append query is an action query) can be executed
from another database, but not with the OpenQuery method, which is used for
opening SELECT queries. You'll need to get the syntax of the action query
and modify it to work in another database.

Open your EXAMPLE.mdb database and then open your action query in SQL view.
Copy the text in SQL view and paste it into your button's click event of the
form in your main database. You need to do some modifications, so that the
correct database's tables are used. For example, if this was the text of the
query's SQL view:

INSERT INTO tblReceiver
SELECT *
FROM tblData
ORDER BY ID;

then you'd paste this into a VBA procedure (your button's click event) using
CurrentDb.Execute and modify it to use the path and file name for the tables
involved, like this (beware of word wrap since this is 4 lines, with an
ampersand and underscore at the end of every line except for the last line):

CurrentDb.Execute "INSERT INTO [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].
tblReceiver " & _
"SELECT * " & _
"FROM [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].tblData " & _
"ORDER BY ID;", dbFailOnError

If you have trouble with the syntax of your own query, please copy and paste
the SQL from SQL view into your next message, and I'll try to help you
translate it into VBA code.

 

[Guest]

Also, why can't I use the " &_" to start a newline? When I do try to use &_,
it tells me invaild character? What am I doing wrong? Is it the version of
MS Visual Basic. I using MS Visual Basic 6.3.

Is this the reason
why it will not allow me to use the code in my main database

Yes. An action query (an append query is an action query) can be executed
from another database, but not with the OpenQuery method, which is used for
opening SELECT queries. You'll need to get the syntax of the action query
and modify it to work in another database.

Open your EXAMPLE.mdb database and then open your action query in SQL view.
Copy the text in SQL view and paste it into your button's click event of the
form in your main database. You need to do some modifications, so that the
correct database's tables are used. For example, if this was the text of the
query's SQL view:

INSERT INTO tblReceiver
SELECT *
FROM tblData
ORDER BY ID;

then you'd paste this into a VBA procedure (your button's click event) using
CurrentDb.Execute and modify it to use the path and file name for the tables
involved, like this (beware of word wrap since this is 4 lines, with an
ampersand and underscore at the end of every line except for the last line):

CurrentDb.Execute "INSERT INTO [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].
tblReceiver " & _
"SELECT * " & _
"FROM [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].tblData " & _
"ORDER BY ID;", dbFailOnError

If you have trouble with the syntax of your own query, please copy and paste
the SQL from SQL view into your next message, and I'll try to help you
translate it into VBA code.

 

[John Spencer]

The line continuation is & SPACE _

& _
NOT
&_

Also, why can't I use the " &_" to start a newline? When I do try to use
&_,
it tells me invaild character? What am I doing wrong? Is it the version
of
MS Visual Basic. I using MS Visual Basic 6.3.

Is this the reason
why it will not allow me to use the code in my main database

Yes. An action query (an append query is an action query) can be
executed
from another database, but not with the OpenQuery method, which is used
for
opening SELECT queries. You'll need to get the syntax of the action
query
and modify it to work in another database.

Open your EXAMPLE.mdb database and then open your action query in SQL
view.
Copy the text in SQL view and paste it into your button's click event of
the
form in your main database. You need to do some modifications, so that
the
correct database's tables are used. For example, if this was the text of
the
query's SQL view:

INSERT INTO tblReceiver
SELECT *
FROM tblData
ORDER BY ID;

then you'd paste this into a VBA procedure (your button's click event)
using
CurrentDb.Execute and modify it to use the path and file name for the
tables
involved, like this (beware of word wrap since this is 4 lines, with an
ampersand and underscore at the end of every line except for the last
line):

CurrentDb.Execute "INSERT INTO [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].
tblReceiver " & _
"SELECT * " & _
"FROM [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].tblData " & _
"ORDER BY ID;", dbFailOnError

If you have trouble with the syntax of your own query, please copy and
paste
the SQL from SQL view into your next message, and I'll try to help you
translate it into VBA code.

 

[Guest]

Thanks!

The line continuation is & SPACE _

& _
NOT
&_

Also, why can't I use the " &_" to start a newline? When I do try to use
&_,
it tells me invaild character? What am I doing wrong? Is it the version
of
MS Visual Basic. I using MS Visual Basic 6.3.

Lavatress wrote:
Is this the reason
why it will not allow me to use the code in my main database

Yes. An action query (an append query is an action query) can be
executed
from another database, but not with the OpenQuery method, which is used
for
opening SELECT queries. You'll need to get the syntax of the action
query
and modify it to work in another database.

Open your EXAMPLE.mdb database and then open your action query in SQL
view.
Copy the text in SQL view and paste it into your button's click event of
the
form in your main database. You need to do some modifications, so that
the
correct database's tables are used. For example, if this was the text of
the
query's SQL view:

INSERT INTO tblReceiver
SELECT *
FROM tblData
ORDER BY ID;

then you'd paste this into a VBA procedure (your button's click event)
using
CurrentDb.Execute and modify it to use the path and file name for the
tables
involved, like this (beware of word wrap since this is 4 lines, with an
ampersand and underscore at the end of every line except for the last
line):

CurrentDb.Execute "INSERT INTO [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].
tblReceiver " & _
"SELECT * " & _
"FROM [;DATABASE=C:\PathToDB\EXAMPLE.mdb;].tblData " & _
"ORDER BY ID;", dbFailOnError

If you have trouble with the syntax of your own query, please copy and
paste
the SQL from SQL view into your next message, and I'll try to help you
translate it into VBA code.