• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

How to apply an action query to a second database.

I have opened a second database which I can update with code.  (.addnew .update, etc.) but how can I do it with a query?

Thanks in advance.
Clive Beaton
Clive Beaton
  • 4
  • 2
2 Solutions

I suppose you have an ADO connection

then pls try


Sub test()

Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command

With objCommand
.ActiveConnection = ADOConn
.CommandText = "UPDATE Clients " & _
"set ClientName = 'John' " & _
"Where ClientID = 10"
End With

End Sub

Open in new window

Clive BeatonAccess DeveloperAuthor Commented:
Thanks, but I should been more clear.  I want to create the query in Query Design and apply the completed query by name.    I was hoping to do something like this:

  Dim dbe As PrivDBEngine, wrk As Workspace, db1 As Database, db2 As Database
   Dim, OutputFilePath As String

   OutputFilePath = "C:\Celebrant Assist V3\CelaRept.accdb"
   Set dbe = New PrivDBEngine
   Set wrk = dbe.Workspaces(0)
   Set db2 = wrk.OpenDatabase(OutputFilePath, False, False)

  '  Run a named querythat updates a table in db2
Gustav BrockCIOCommented:
You can continue:

Dim qdy As QueryDef

Set qdy = db2.CreateQueryDef("NameOfQuery")
qdy.SQL = strSQL ' Your action SQL code.

If you like, you can create a temp query this way:

Set qdy = db2.CreateQueryDef("")

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Clive BeatonAccess DeveloperAuthor Commented:
It looks like I can't do what I want to do but points for the effort.
Clive BeatonAccess DeveloperAuthor Commented:
I give up.  I wanted to award the points to the nexperts but now it seems I can't.
Gustav BrockCIOCommented:

> I have opened a second database which I can update with code.  ..but how can I do it with a query?

I showed you exactly that.

Clive BeatonAccess DeveloperAuthor Commented:
Thanks for trying.  Sorry I didn't close the question, earlier.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now