SQL in a Recordset connection string

[salmonella]

Did not get an solution so I am reposting the problem. I am having trouble
opening a ADO recordset using a select query as the data source. It works
fine IF there is no where clause in the query however, the query with the
select clause works fine if ran by itself.

Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = Application.CurrentProject.Connection
Dim str As String
str = "select * from mail_send_q"
rs.Open str, cnn, adOpenKeyset, adLockOptimistic

if you strip out the WHERE criteria from "mail_send_Q " it works fine, but
with it it crashes.

Does any one know what I may be doing wrong?

Also, I suppose another way to solve the problem would be to dump the query
and just add the SQL stuff to the connection string. I am referencing user
data in a form’s fields so I need to account for a null value. When I do it
in a query it looks like:
((pw_registry.department_name)=forms!memo_board_super!department Or
forms!memo_board_super!department Is Null) And (then next criteria, and so on)

How to I write the ‘or is null’ part in the connection string in VBA? For
example str = "select * from pw_registry where department_name = " &
forms!memo_board_super!department & “ OR …………………. (then what?)

thanks

 

[Stefan Hoffmann]

hi,

Dim rs As New ADODB.Recordset
str = "select * from mail_send_q"
rs.Open str, cnn, adOpenKeyset, adLockOptimistic

if you strip out the WHERE criteria from "mail_send_Q " it works fine, but
with it it crashes.

This is interesting. Do you have an .adp or an .mdb? Have you already
tried a compact and repair?

Can you post the SQL of your query?

((pw_registry.department_name)=forms!memo_board_super!department Or
forms!memo_board_super!department Is Null) And (then next criteria, and so on)

".. OR IsNull(forms!memo_board_super!department) .."

btw, a connection string describes how to connect to a database. It is
not a SQL statement.


mfG
--> stefan <--

 

[salmonella]

Stefan

I am adding the query at the bottom of this. It is fine- if I run it it
returns what it is supose to return. However, it I try to open an ADO record
set with it as the data source, it returns an error. I would just add the SQl
stuff as part of the connection string however I don't know how to write the
"or is null" part like I can in the query below.
any ideas?


SELECT student.active, pw_registry.record_id, pw_registry.link_id,
pw_registry.faculty_name, pw_registry.department_name,
pw_registry.supervisor_name, pw_registry.sup_uid, pw_registry.fac_uid,
pw_registry.dept_uid, pw_registry.student_id_numb, pw_registry.status,
pw_registry.pw, pw_registry.fname, pw_registry.lname, pw_registry.res_cat,
pw_registry.ustudent_id, pw_registry.clasification, pw_registry.course,
pw_registry.section, pw_registry.program
FROM pw_registry LEFT JOIN student ON
pw_registry.ustudent_id=student.ustudent_id
WHERE (((pw_registry.department_name)=forms!memo_board_super!department Or
forms!memo_board_super!department Is Null) And
((pw_registry.supervisor_name)=forms!memo_board_super!supervisor Or
forms!memo_board_super!supervisor Is Null) And
((pw_registry.fname)=forms!memo_board_super!fn Or forms!memo_board_super!fn
Is Null) And
((pw_registry.lname)=forms!memo_board_super!n Or forms!memo_board_super!n
Is Null) And
((pw_registry.faculty_name)=forms!memo_board_super!faculty Or
forms!memo_board_super!faculty Is Null) And
((pw_registry.course)=forms!memo_board_super!course Or
forms!memo_board_super!course Is Null) And
((pw_registry.section)=forms!memo_board_super!section Or
forms!memo_board_super!section Is Null) And
((pw_registry.active)=forms!memo_board_super!active Or
forms!memo_board_super!active Is Null));

 

[Roger Carlson]

The problem is not that there is a Where clause but that your Where clause
has parameters in it. The ADO object cannot read the information directly
from the Access form.

I don't use ADO much, preferring DAO instead. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"TooFewParameters.mdb" which illustrates how to do this with DAO.

Another option would be to create the query in the SQL String. You had a
good start, but you have to delimit your different datatypes.

str = "select * from pw_registry where department_name = '" &
forms!memo_board_super!department & "' OR department_name Is Null) And
(pw_registry.supervisor_name = '" & forms!memo_board_super!supervisor & "'
Or pw_registry.supervisor_name Is Null) And ...


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[salmonella]

Hi Roger,
Thanks for the reply!
Well…….. I tried to solve it by adding the SQL to the conection string (
see below) and I got the same problem – it can’t see the user data entered in
a field. Here is a breakdown of what is going on

I have a form Called “memo_board_super†with a field called “departmentâ€.
The value of “department†can change depending on user. I want to open a
recordset using “pw_registry†as the data source and restrict the records
based on the value in the field called “department†on “memo_board_super†.
Simple, but here is the problem. if the "department" field is null (nothing
entered) I want the field to be ignored (the “or is null†part of the SQL
stuff below) and this is causing the problem. Putting “str†(see below) in
the connection string will return an error saying that “no value given for
one or more of the parameters†which is the "' OR
Forms!memo_board_super!department Is Null" part of the string. Basically if
there is not a value in “department†(i.e. null) I want it to read True and
move on. I think we are close becaue if i remove the "or is null" part of
the string, it works fine and will restrict the data set based on whatever is
in the field "department" so the only problem is how to deal with a null
value in "department" so that it ignores the field and does not return
records in "pw_registry" with a null value in "department". Any ideas what I
am doing wrong?

I tried using this and returned an error saying cant find a value (the “or
is null†stuff)

str = "select * from pw_registry where department_name = '" &
Forms!memo_board_super!department & "' OR Forms!memo_board_super!department
Is Null"

many thanks

 

[salmonella]

Saw your example, but it seems to have the same problem with null values,
i.e. you have to choose something or nothing gets returned.

I think, howevr, found a way around it. All I did was flip through each
combo box and if it was null (user did not select anything) I told it to
ignore it and if it had a value I took the value and incorporated it into a
sql string. As it when from box to box the SQL string got written. Then I
just referenced the final builtup SQL string (with values and not parameters)
into the conection string and it works great and handles null values.

Anyway, thanks alot for your help,