SQL and recordset.Open (still having problems)

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Thanks for all the help so far. (Please read below for
my problem.) But I still get the same error. I tried the
SQL statement 3 different ways. I have pasted the
Debug.Print results below:

SELECT ACCOUNT_LIST.COMPLEMENTOR FROM REP_LIST INNER JOIN
(CS_LIST INNER JOIN ACCOUNT_LIST ON CS_LIST.id =
ACCOUNT_LIST.CS) ON REP_LIST.id = ACCOUNT_LIST.REP WHERE
(((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'));

"SELECT ACCOUNT_LIST.COMPLEMENTOR FROM REP_LIST INNER
JOIN (CS_LIST INNER JOIN ACCOUNT_LIST ON CS_LIST.id =
ACCOUNT_LIST.CS) ON REP_LIST.id = ACCOUNT_LIST.REP WHERE
(((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'));"

"SELECT ACCOUNT_LIST.COMPLEMENTOR FROM REP_LIST INNER
JOIN (CS_LIST INNER JOIN ACCOUNT_LIST ON CS_LIST.id =
ACCOUNT_LIST.CS) ON REP_LIST.id = ACCOUNT_LIST.REP WHERE
(((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'))"

As for the database type, the book I'm reading told me
that "rst.ActiveConnection = CurrentProject.Connection"
will set the connection type to a Jet DB. All I want the
statement to do is allow me to search a table to find
records matching the search criteria. Can anyone tell me
what the problem is with the above SQL statements?

Also, I have the SQL statement variable declared as:
Dim strstring As String
in the Declarations section of Module1 to make it public
to everything. Would this cause a problem?


Thanks,
Brian
Thanks for the reply. I added the format function on my
dates and the debug.print result looks good. All single
quotes and the # symbols around dates and the ; at the
end. Is there a restriction as to where you can call the
rst.Open command? I have it in my Private Sub Form_Load ()
procedure. It is still giving me the same error as
before.
Click to expand...

The error is telling you that the SQL command is
malformed: perhaps it
would help if you are able to post the actual debug.print
result. The
suggestions that (the other!) Tim gave you were right for
a Jet database,
but may need to be different for another database
provider.

This error (and yes, it is in English: have you read it?)
is extremely
unlikely to be the result of opening the recordset in any
particular event,
as you know since you successfully opened the
simple "SELECT * FROM" query.
What are you going to be doing with the result anyway --
some things that
come later may be happening at the wrong time, but they
will not tbe source
of this error.

Hope that helps


Tim F

..


-----------------------------------------------------


Thanks for the reply. I added the format function on my
dates and the debug.print result looks good. All single
quotes and the # symbols around dates and the ; at the
end. Is there a restriction as to where you can call the
rst.Open command? I have it in my Private Sub Form_Load()
procedure. It is still giving me the same error as
before.

Brian
-----Original Message-----
Make sure your SQL (within that variable) is just
Click to expand...
looking for data, not manipulating data (like delete,
append, etc.).
One possible error would be the punctuation of your
Click to expand...
strsearch variable. That means using the proper #'s for
dates, and single-quotes for text values that are needed
as criteria.
Example:

Brian_Value1 = "ABCDEFG"
Brian_Value2 = Datevalue("06/01/04")
strsearch = "Select * from [Some Table Name] Where
Click to expand...
[Product Name] = '" & Brian_Value1 & "' and [Date
Purchased] > #" & Format(Brian_Value2, "MM/DD/YYYY")
& "#;")
' Note the single & double quotes within the SQL
Click to expand...
statement, allowing for variables
Tim


.
Click to expand...
..
 
My news reader seems to have lost the thread on this one, but I think I
remember right:
Thanks for all the help so far. (Please read below for
my problem.) But I still get the same error. I tried the
SQL statement 3 different ways. I have pasted the
Debug.Print results below:
Click to expand...

I have taken the trouble to reformat these so they are a bit more human-
readable, but the actual code is unchanged:
SELECT ACCOUNT_LIST.COMPLEMENTOR
FROM REP_LIST
INNER JOIN (CS_LIST
INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS)
ON REP_LIST.id = ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'));
Click to expand...

This looks fine to me: the single quotes are okay for Jet as well as for
SQL Server. I assume that the joining fields are all compatible? Does this
work if you copy it straight into the SQL view of a new query?
"SELECT ACCOUNT_LIST.COMPLEMENTOR
FROM REP_LIST
INNER JOIN (CS_LIST
INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS)
ON REP_LIST.id = ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'));"
Click to expand...

If the surrounding quotes are actually in the string passed to the DB
engine, then this will definitely choke.

"SELECT ACCOUNT_LIST.COMPLEMENTOR
FROM REP_LIST
INNER JOIN (CS_LIST
INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS)
ON REP_LIST.id = ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='buygfd'))"
Click to expand...

Is this different from number 2?

The query pattern for this is, at a guess:

RepList AccountList
======= =========== CS_List
ID ---------< Rep =======
CS >---------- ID
Complementor

If this is a many-to-many query, the joins seem a bit redundant, since you
are asking to return a value you are specifying in the WHERE criterion:
doesn't this just do a

SELECT Complementor
FROM AccountList
WHERE Complementor = 'buygfd'

I would suggest making up the query in the query design window and checking
it gives the right answer: and then copy the whole stuff into the VBA code.

Okay: things like dates, string delimiters, and wildcards change when you
move up to real RDBMSs -- can't see it's a problem here.

Best of luck


Tim F
 
Back
Top