ADO querying

I have a grave problem that is probably simple to fix but not sure if I am seeing it! I am creating a program that I need to search through a table via query and simply list the results (simple enough) - but I can not get things to work. I any one can shed some light on the subject or share some code I would greatly appreciate it.


Here is what I am doing:

Form_Load event: (WORKS FINE)

'Database connection establishment
Set adoCon = New Connection
Connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & picDB
adoCon.Open Connect

Some_Click event: (PROBLEM)

'Want to query here:

Set adoRec = New Recordset
SQLQuery = ""
SQLQuery = "SELECT PictureAttributes.Photographer FROM PictureAttributes" & _
" WHERE (((PictureAttributes.Photographer)= SMITH))"

adoRec.Open SQLQuery, Connect, adOpenDynamic, adLockOptimistic, adCmdText

'It is here where I am getting this runtime-error:

Runtime-error '-2147217904(80040e10)'
No value given from one or more required parameters.


IF I MAKE SQLQuery THIS:
SQLQuery = "SELECT PictureAttributes.Photographer FROM PictureAttributes"

adoRec.Open SQLQuery, Connect, adOpenDynamic, adLockOptimistic, adCmdText

'I get no errors but I am missing WHERE clause


If I do this:

Some_Click event: (PROBLEM)

'Want to query here:

Set adoRec = New Recordset
SQLQuery = ""
SQLQuery = "SELECT PictureAttributes.Photographer FROM PictureAttributes"

'Or this SQLQuery

SQLQuery = "SELECT PictureAttributes.Photographer FROM PictureAttributes" & _
" WHERE (((PictureAttributes.Photographer)= SMITH))"

adoRec.Open SQLQuery, Connect, adOpenDynamic, adLockOptimistic, adCmdTable 'Changed from adCmdText

'Both strings I am getting this runtime-error:

Runtime-error '-2147217900(80040e14)'
Syntax error in FROM clause.

SQLQuery = "SELECT * FROM PictureAttributes" & _
" WHERE (((PictureAttributes.Photographer)= SMITH))"

try that and let me know how it goes :)

one more thing...

when posting code, use the vbcode tag


[vbcode]
MsgBox "u'r code goes here"
[/vbcode]

will look Like this

VB Code:

MsgBox "u'r code goes here"

Quote:

---

adoRec.Open SQLQuery, Connect, adOpenDynamic, adLockOptimistic, adCmdTable 'Changed from adCmdText

---

Why did you change it from adCmdText to adCmdTable. If you specify adCmdTable then the Source parameter (your SQLQuery variable) must be the name of a valid table.

It looks like you are just missing some quotes.

Try this out

VB Code:

SQLQuery = "SELECT PictureAttributes.Photographer FROM PictureAttributes" & _ 
" WHERE (((PictureAttributes.Photographer)= [b]'SMITH'[/b]))" 
 
adoRec.Open SQLQuery, Connect, adOpenDynamic, adLockOptimistic, adCmdText

Peet & Brucevde,

Thank you for both your help & the helpful hint about posting code. The lengthy SQL string was the problem. Everything seems to work now.

To answer Brucevde's reply if I leave adCmdTable I get an error
stating:
A syntax error in the FROM clause.

When I changed it to adCmdText - no error I will further experiment.