Solved

How to apply an action query to a second database.

Posted on 2014-11-19
7
102 Views
Last Modified: 2015-02-02
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.
0
Comment
Question by:CRB1609
  • 4
  • 2
7 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40454365
Hi,

I suppose you have an ADO connection

then pls try

.Execute

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"
.Execute
End With

End Sub

Open in new window


Regards
0
 

Author Comment

by:CRB1609
ID: 40454455
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
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40454521
You can continue:

Dim qdy As QueryDef

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

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

Set qdy = db2.CreateQueryDef("")

/gustav
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:CRB1609
ID: 40463247
It looks like I can't do what I want to do but points for the effort.
0
 

Author Comment

by:CRB1609
ID: 40463265
I give up.  I wanted to award the points to the nexperts but now it seems I can't.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40463270
?

> 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.

/gustav
0
 

Author Closing Comment

by:CRB1609
ID: 40585617
Thanks for trying.  Sorry I didn't close the question, earlier.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question