SQL and recordset.Open (still having problems)
From: Brian (Brian_at_discussions.microsoft.com)
Date: 07/12/04
- Next message: Bob Piro: "Using VBA to modify linked table in a database"
- Previous message: Marshall Barton: "Re: Enlarging a sub-Form in Form View"
- Next in thread: Tim Ferguson: "Re: SQL and recordset.Open (still having problems)"
- Reply: Tim Ferguson: "Re: SQL and recordset.Open (still having problems)"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 12 Jul 2004 06:05:56 -0700
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
>>> Arguments are of the wrong type, are out of
>>> acceptable range, or are in conflict with
>>> one another.
>> One possible error would be the punctuation of your
>> strsearch variable. That means using the proper #'s
for
>> dates, and single-quotes for text values that are
needed
>> as criteria.
> 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.
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
looking for data, not manipulating data (like delete,
append, etc.).
>One possible error would be the punctuation of your
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
[Product Name] = '" & Brian_Value1 & "' and [Date
Purchased] > #" & Format(Brian_Value2, "MM/DD/YYYY")
& "#;")
>' Note the single & double quotes within the SQL
statement, allowing for variables
>
>Tim
>
>"Brian" wrote:
>
>> I am trying to use the recordset.Open command to run
an
>> SQL statement. The book a I am reading has this for
an
>> example:
>>
>> Dim rst As ADODB.Recordset
>> Set rst = New ADODB.Recordset
>>
>> rst.ActiveConnection = CurrentProject.Connection
>> rst.CursorType = adOpenStatic
>> rst.Open "Select * from tblClients"
>>
>> rst.Close
>> Set rst = Nothing
>>
>> I have no problem getting that to work, but i have an
SQL
>> statement saved as a string and i want to pass that
>> through the rst.Open command. I tried:
>>
>> rst.Open strsearch
>>
>> strsearch is the varible with my search string in it.
>> The string changes depending on what the user is
>> searching for, so i cant just type in an SQL statement
>> like in the example. But when i put the varible name
in
>> there i get an error:
>>
>> Run-time error '3001'
>>
>> Arguments are of the wrong type, are out of
>> acceptable range, or are in conflict with
>> one another.
>>
>> I'm not sure what that means in English. I hit debug
and
>> it highlights the 'rst.Open strsearch' line.
>>
>> Any help would be great. Thanks!
>>
>> Brian
>>
>.
>
.
- Next message: Bob Piro: "Using VBA to modify linked table in a database"
- Previous message: Marshall Barton: "Re: Enlarging a sub-Form in Form View"
- Next in thread: Tim Ferguson: "Re: SQL and recordset.Open (still having problems)"
- Reply: Tim Ferguson: "Re: SQL and recordset.Open (still having problems)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
