Open failed

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

Hi, can you please check the following code to find the
error that is causing the error message...

'Method Open of Object _Recordset failed'

I just can't see it and so I am hoping the you can spot
the error.

*** code snipet start ***

Public Function SizeDesc(SizeCategory As Integer) As String
On Error GoTo SizeDesc_Err

Dim strSQL As String
Dim rsData As ADODB.Recordset
Dim strDesc As String

strSQL = "SELECT Size " _
& "FROM tblSize " _
& "WHERE (((SizeCategory)=" _
& SizeCategory _
& "));"

Set rsData = New ADODB.Recordset
With rsData
.ActiveConnection = CurrentProject.Connection
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.Source = strSQL
.Open
Do Until .EOF

***** code snippet ends ***

The code fails at the open statement. The sql works when
copied from the debug window into a query design.

Any ideas or suggestions appreciated :-)

Cheers
Jonathan
 
Hi, the problem is the field name 'Size'. When I replaced
this in the sql with an asterisk (*) the recordset opened
as expected...

Jonathan
 
Hi, the problem is the field name 'Size'. When I replaced
this in the sql with an asterisk (*) the recordset opened
as expected...
Click to expand...

If you check help for "SQL Reserved words", you will see that SIZE is a
reserved word (although I confess that I don't know what it is used for).
Your choices are (a) to convert the SQL to

SELECT [Size] FROM tblSize...

or (b, and better) to rename the column to a non-reserved word.

In general it is good habit to steer away from common words like Date,
Type, Text, Size etc because of the likelihood of clashing. Using more
meaningful fieldnames will make maintenance easier: like SignUpDate,
TypeOfRegistration, EnglishDescription, OverallSize.

This applies also to naming of tables themselves: it pays off to name them
according to the entity involved rather than how it is to be described.
E.g. Elephants(Fullname, HeightToShoulder) makes more sense than Size(Size)
IYSWIM.


All the best


Tim F
 
Back
Top