simple SELECT query to .mdb still fails :(

[fedefring]

Hi guys,
nice to meet you All!


I am struggling with Excel, trying to get data from an .mdb Access old db,
via SQL query "only".


I mean "only", cause I am able to get what I need by "looping" through records without
errors, but I assume using SQL is extremely better!


So, code you will see below, is ok in order to get connected to the db and
I can easily get recordset content by looping,
but I did a lot of tentatives with queries you will see and
in no one case (you see only two here) I was able to run correctly the query
cause I always get "query sintax error" when executed.


Where is my error ?


To be clear in advance, I cannot use ADO or DAO to connect, cause this is a very old
db and also a very old xls with dozens of macros with the same kind
of connection method you see below.


Thank you in advance,
Fred!


------------------
Private Function aggiorna_comm(stringa_connessione, valuex) As Boolean


On Error GoTo Err_aggiorna_comm


Dim db_destination As Database
Dim rs_destination As Recordset


Set db_destination = OpenDatabase(stringa_connessione)
Set rs_destination = db_destination.OpenRecordset(tabx)


1)
strQry = "SELECT * FROM '" & tabx & "' WHERE '" & fieldx & "' = '" & valuex & "'"
2)
strQry = "SELECT * FROM "&tabx&" WHERE "&fieldx&" = " & valuex & "' "


db_destination.Execute strQry


test = rs_destination!idComm


Err_aggiorna_comm_exit:
rs_destination.Close
Set rs_destination = Nothing


db_destination.Close
Set db_destination = Nothing
Exit Function


Err_aggiorna_comm:
MsgBox Error$
Resume Err_aggiorna_comm_exit


End Function

 

[jimrward]

Where are your values for tabx and fieldx

 

[fedefring]

Where are your values for tabx and fieldx

Hi Jim and thanks for support,

I am sorry, see the right copy and paste
as you imagine everything is passed by subs calling the function, bye :

Private Function aggiorna_comm(stringa_connessione, valuex, tabx, fieldx) As Boolean


On Error GoTo Err_aggiorna_comm


Dim db_destination As Database
Dim rs_destination As Recordset


Set db_destination = OpenDatabase(stringa_connessione)
Set rs_destination = db_destination.OpenRecordset(tabx)


1)
strQry = "SELECT * FROM '" & tabx & "' WHERE '" & fieldx & "' = '" & valuex & "'"
2)
strQry = "SELECT * FROM "&tabx&" WHERE "&fieldx&" = " & valuex & "' "


db_destination.Execute strQry


test = rs_destination!idComm


Err_aggiorna_comm_exit:
rs_destination.Close
Set rs_destination = Nothing


db_destination.Close
Set db_destination = Nothing
Exit Function


Err_aggiorna_comm:
MsgBox Error$
Resume Err_aggiorna_comm_exit


End Function

 

[Joe4]

What kind of values are valuex?

Since tabx is a table name and fieldx is a field name, they should not have any single-quotes around them. If they may have spaces, they will need square brackets around them.
If valuex is text, it should have single quotes around its value. If not, then it shouldn't.

If valuex text:

[COLOR=#333333]strQry = "SELECT * FROM " & tabx & " WHERE " & fieldx & " = '" & valuex & "'"[/COLOR]

If valuex numeric:

[COLOR=#333333]strQry = "SELECT * FROM " & tabx & " WHERE " & fieldx & " = " & valuex[/COLOR]

 

[fedefring]

Hi Joe,
valuex is a number and I tried both these without success, please see the strQry string result (before execute) :

strQry = "SELECT * FROM " & tabx & " WHERE " & fieldx & " = " & valuex
SELECT * FROM tTerminiPagamento WHERE TerminePagamento = 120

SELECT * FROM tTerminiPagamento WHERE [TerminePagamento] = 120

Error is 3065 "Cannot execute a select query"

I really do not know,
thanks!

 

[Norie]

Why are you using Execute here?

db_destination.Execute strQry

Execute should only be used for action queries, and a SELECT query isn't an action query.

 

[fedefring]

And, if I try this way instead of db.execute:

case1)
strQry = "SELECT * FROM " & tabx & " WHERE " & fieldx & " = " & valuex

case2)
fieldx = "[" & fieldx & "]"
strQry = "SELECT * FROM " & tabx & " WHERE " & fieldx & " = " & valuex


Set rs_destination = db_destination.OpenRecordset(strQry)

I get error 3464

Thanks!

 

[Norie]

That means there's a problem with the criteria.

What happens if you remove the criteria?

 

[fedefring]

this way works:

strQry = "SELECT * FROM " & tabx & " WHERE 1=1"
Set rs_destination = db_destination.OpenRecordset(strQry)


test = rs_destination(target_field)

but now... what ?

Thanks

 

[fedefring]

Ohhhh great!!

It was a text not a number and now after all these changes it works like a charm!!!

Thank you so much GUYS!!