Open Object Error

[DEI]

I am trying to open an ADODB recordset with the following code

Dim SQL As String

Dim Conn As ADODB.Connection
Dim rss As New ADODB.Recordset

Set Conn = CurrentProject.Connection

'Source table (Query1)

Select Case STAFF

Case "ALL"

SQL = "SELECT AUDIT.ADATE, AUDIT.STAFFID, AUDIT.AUDITID, AUDIT.ACTID,
AUDIT.TIME " & _
"FROM AUDIT " & _
"WHERE (((AUDIT.LOCID) = " & ASC & ")) " & _
"ORDER BY AUDIT.ADATE, AUDIT.STAFFID, AUDIT.TIME;"

Case "RN"

SQL = "SELECT AUDIT.ADATE, AUDIT.STAFFID, AUDIT.AUDITID, AUDIT.ACTID,
AUDIT.TIME " & _
"FROM STAFF INNER JOIN AUDIT ON STAFF.STAFFID = AUDIT.STAFFID "
& _
"WHERE (((AUDIT.LOCID) = " & ASC & ") And ((STAFF.POSITION) = '"
& STAFF & "')) " & _
"ORDER BY AUDIT.ADATE, AUDIT.STAFFID, AUDIT.TIME;"

End Select

'Open source table

rss.Open SQL, Conn, adOpenKeyset, adLockOptimistic

When I use the first SQL statement, the recordset opens fine. When I use
the second (which is very similar, but has a join) I get the following
run-time error: Method 'Open' of object "-Recordset' failed. I can not
figure out what the problem is. Do I need to use different arguments for the
Open method?

Thanks in advance.

DEI

 

[JimBurke via AccessMonster.com]

Syntax-wise it looks OK to me. Are you sure that STAFFID from the STAFF table
and STAFFID from the AUDIT table are the same data type? And AUDIT.LOCID is
defined as a text field? When I see ID in a field name I tend to think of a
Long type of field. Is it possible that LOCID corresponds to a string value,
i.e. LOCID of 1 represents "ALL", LOCID of 2 represents "RN", etc.? If the
syntax is valid, which from what I can see it is, then it has to have
something to do with those fields in STAFF.

 

[DEI]

Jim,

The STAFFID fileds in both tables have the same datatype; a one-to many
relationship has been defined in the database between the 2 tables on this
field.

ASC is a long variable. I actually pereviously used a number in SQL string
instead of the reference to the variable and had the same result.

When I copy and paste the query statement into the query builder it works
fine.

Thanks for your assistance.

DEI

 

[AccessVandal via AccessMonster.com]

ASC happens to be an Access built-in function as well as query syntax "ORDER
BY ASC". If you named a variable or a control, I would suggest that you
rename these immediately and save as well as do a compact and repair.

VBA might take this one as a function and without it's string argument, it
will throw an error, or the SQL misunderstood it as an order by query.

 

[DEI]

Thanks for the tip re: the ASC function. I fised that.

The SQL statement still does not work, however, even when I eliminate all
string variables.

I eventually defined a query in the database and created an SQL statement to
access that query (using SELECT *, etc.). It works fine. I still can not
determine why the original statement generates the error.

Thanks,

DEI

 

[AccessVandal via AccessMonster.com]

Unfortunately, "TIME" is also a reserved word in Access.

Did your code include

Set rss = New ADODB.Recordset