Running Remote Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written code that creates a query in a remote database. It does this
from my primary database.
I have opened the remote database and run the query (which was set up from
my database) manually and it does what I want it to do. I can delete that
query from my database which I must be able to do if I am planning to run
that query many times.

Now I want to run that query from my program (it appends records to my main
database). What is the syntax? (I tried docmd.runSQL(".....") with no luck)

Bill Andersen
 
Hi, Bill.

You might want to think about creating a remote query, instead of creating a
query in a remote database and running it from the current database. It's
much easier to work with. Example SQL syntax for a remote query that appends
records from a query is:

INSERT INTO tblMyStuff
SELECT *
FROM [;DATABASE=C:\Work\OtherDB.mdb].qryOtherStuff;

The code to run this remote query in VBA is:

CurrentDB().Execute "QueryName", dbFailOnError

When you need to delete this query from code, try:

DoCmd.DeleteObject acQuery, "QueryName"

This is less work than managing it in the remote database.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Bill said:
I have written code that creates a query in a remote database. It does this
from my primary database.
I have opened the remote database and run the query (which was set up from
my database) manually and it does what I want it to do. I can delete that
query from my database which I must be able to do if I am planning to run
that query many times.

Now I want to run that query from my program (it appends records to my main
database). What is the syntax? (I tried docmd.runSQL(".....") with no luck)
Click to expand...

You need to open the other database so the query knows which
db to operate in.

Set dbOther = OpenDatabase("path to other database")

You probably have the other database opened when you created
the query. If it's stoll open, then you only need this one
line to run the query:

dbOther.Execute "mam of query", dbFailOnError

Note: if all you really want to do is run the query, it is
not necessary to create the query in the other database.
You can run a local query that uses the IN prhase to specify
the other database.
 
Here is the final code which works perfectly. Incidently, the magic word was
'execute'.

Public Sub AppendArchive()
Dim mydb As DAO.Database, recs As DAO.Recordset, qds As DAO.QueryDef
Dim Pointer As Long
Dim mydb1 As DAO.Database, recs1 As DAO.Recordset, qdf As DAO.QueryDef
Set mydb = CurrentDb()
Set recs = mydb.OpenRecordset("Archive")
recs.Index = "Invoice_no"
recs.MoveLast
Pointer = recs.Fields("Invoice_no")

Set mydb1 = OpenDatabase("C:\Andersen Services\Cash Receipts\Anpro
Invoicing\Invoicing Data.mdb")
mydb1.QueryDefs.Delete "AppendData"

Set qdf = mydb1.CreateQueryDef("AppendData", "INSERT INTO Archive " & _
"IN 'C:\Andersen Services\Cash Receipts\Receipts 2005.mdb'" & _
"SELECT * FROM Archive " & _
"WHERE ((([Archive].[Invoice_no]) > " & Pointer & ")) " & _
"ORDER BY [Archive].[Invoice_no];")

mydb1.Execute "AppendData"
DoCmd.Close acQuery, "AppendData"

End Sub

Thanks for your help.

Bill Andersen
 
Back
Top