ADO querying

[justindl]

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.

[peet]

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

try that and let me know how it goes

[peet]

one more thing...

when posting code, use the vbcode tag


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

will look Like this

MsgBox "u'r code goes here"

[brucevde]

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

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

[justindl]

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.