Programmatically running SQL Query

  • Thread starter Thread starter Barb Reinhardt
  • Start date Start date
B

Barb Reinhardt

I have an SQL query that is defined programmatically which looks like this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?

Thanks,
Barb Reinhardt
 
=?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= said:
I have an SQL query that is defined programmatically which looks like this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?

Thanks,
Barb Reinhardt
Click to expand...

You don't have to create the query to run it.

Running it you do like this:
DoCmd.RunSQL "<place your SQL string here>"
 
XPS35 said:
=?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= said:
I have an SQL query that is defined programmatically which looks like
this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?
Click to expand...

You don't have to create the query to run it.

Running it you do like this:
DoCmd.RunSQL "<place your SQL string here>"
Click to expand...

Actuially, Peter, that's incorrect.

RunSQL only works with Action queries (UPDATE, INSERT INTO, DELETE), not
Select queries.

Barb: It's an unusual requirement to need to run a Select query. What's the
end result for which you're hoping?
 
Douglas J. Steele wrote:

Actuially, Peter, that's incorrect.

RunSQL only works with Action queries (UPDATE, INSERT INTO, DELETE), not
Select queries.
Click to expand...

You are right. I responded without thinking :-(
 
When I do what you suggest, I get the following error:

Run-time error '2342'

A RunSQL action requires an argument consisting of an SQL statement. In my
reading, I gather that the SQL statement that I have is a SELECT statement
and I need an ACTION statement of some kind for the

DoCmd.RunSQL "<place your SQL string here>"

to work. Any other suggestions?

Thanks,

Barb Reinhardt



XPS35 said:
=?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= said:
I have an SQL query that is defined programmatically which looks like this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?

Thanks,
Barb Reinhardt
Click to expand...

You don't have to create the query to run it.

Running it you do like this:
DoCmd.RunSQL "<place your SQL string here>"

--
Groeten,

Peter
http://access.xps350.com

.
Click to expand...
 
What I want to do is get the results of the UNION and do something with the
resulting table. Ultimately I want to review the fields in the resulting
table and compare values in like observations for fields with the same name
"PS.EMPLID" vs "Verification.EMPLID" for example and if they are different,
put a conditional format on them if I can do that in access. I'm able to
find the "matching" fields in an existing table now, but I need to create
that table programmatically and I haven't been able to accomplish that.

I'm fluent in Excel VBA but that's not helping a lot right now. I'm new
to Access altogether, so am still on a steep learning curve.

Thanks,

Barb Reinhardt



Douglas J. Steele said:
XPS35 said:
=?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= said:
I have an SQL query that is defined programmatically which looks like
this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?
Click to expand...

You don't have to create the query to run it.

Running it you do like this:
DoCmd.RunSQL "<place your SQL string here>"
Click to expand...

Actuially, Peter, that's incorrect.

RunSQL only works with Action queries (UPDATE, INSERT INTO, DELETE), not
Select queries.

Barb: It's an unusual requirement to need to run a Select query. What's the
end result for which you're hoping?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)



.
Click to expand...
 
Well, what are you trying to do?
-- Open the query and view the records
-- Open a recordset and process the records in a VBA routine
-- Use the query as the source for a report or form
-- Use the query as the source for a listbox or combobox
-- Something else?


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
To get the results of a query into a recordset and access them
programmatically, you would generally use the DAO.Recordset object. Example
below. Do note though, that what you are trying to do might be able to be
accomplished through queries... this would mostly likely be a better route if
it can be done (I'm not strong in queries by any means).

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT...." 'your sql string
Set rs = CurrentDb.OpenRecordset(strSQL)
'now use the methods and properties of the rs
'to navigate and analyize the records returned by the query
If rs.Recordcount <> 0 Then
rs.MoveFirst
While Not rs.EOF
Debug.Print rs.Fields("thisField")
rs.MoveNext
Wend
End If
'be sure to close the rs and set it to nothing
rs.Close
Set rs = Nothing


Again, depending on what you're doing, a query may be a much better choice
(they're generally always faster than using a recordset). Note that the
Recordset object has a lot of quirks to it... there's a bit of a learning
curve. The above example gives a basic idea how to navigate and reference
values from the a field. Also note that there is a Recordset object of the
ADO model, which, IMO, should not be used unless you have a specific need for
ADO. If you're not sure, use DAO. Be sure to disambiguate by including
"DAO" when you dim the recordset.

Good luck!
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



Barb Reinhardt said:
What I want to do is get the results of the UNION and do something with the
resulting table. Ultimately I want to review the fields in the resulting
table and compare values in like observations for fields with the same name
"PS.EMPLID" vs "Verification.EMPLID" for example and if they are different,
put a conditional format on them if I can do that in access. I'm able to
find the "matching" fields in an existing table now, but I need to create
that table programmatically and I haven't been able to accomplish that.

I'm fluent in Excel VBA but that's not helping a lot right now. I'm new
to Access altogether, so am still on a steep learning curve.

Thanks,

Barb Reinhardt



Douglas J. Steele said:
XPS35 said:
=?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= wrote:


I have an SQL query that is defined programmatically which looks like
this

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

How do I create this query programmatically in access and run it?

You don't have to create the query to run it.

Running it you do like this:
DoCmd.RunSQL "<place your SQL string here>"
Click to expand...

Actuially, Peter, that's incorrect.

RunSQL only works with Action queries (UPDATE, INSERT INTO, DELETE), not
Select queries.

Barb: It's an unusual requirement to need to run a Select query. What's the
end result for which you're hoping?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)



.
Click to expand...
Click to expand...
 
Let me tell you what I have right now.

I have two tables in the database that I want to join with an outer join (I
think that's the term) and retain all unmatched records. I then want to
review similar records from each source table to see if they are the same.
If they aren't, I want to reflect that somehow. The way we did this before
was to join the files in SAS JMP, save as an XL file and then put some
conditional formats on the non-matching cells. The user will need to use
the results to update PeopleSoft.

The person who will be doing this work on a regular does not have access to
SAS JMP, but has Access and I'm trying to learn Access (and programming in
Access) while coming up with something to streamline their process.

I don't really care how this is done. I have a UNION query written (with
help) that joins the files. I've taken some training on reports, but
haven't really gotten too far with them. Should I consider going in that
direction? Unfortunately, I'm not going to know which fields have records
that don't match until I test them.

Thanks for your assistance,

Barb Reinhardt
 
Back
Top