ADODB Recordset Error

[AlwaysFroosh!]

Hello,

I am trying to set a variable in my code to a value located in a table using
the following code:

Dim LengthRecord As New ADODB.Recordset
Dim SQL As String

SQL = "SELECT SETTINGS.VALUE FROM SETTINGS WHERE SETTINGS.ROOT='TAG' AND
SETTINGS.SECTION='Serial' AND SETTINGS.KEYNAME='Length';"

LengthRecord.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

TagLength = LengthRecord("VALUE")


I keep getting an error that says:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

What am I doing wrong? The Select statement only returns one value from the
table.

Thanks for the help,
Graham

 

[Douglas J. Steele]

Both Value and Section are reserved words, and shouldn't be used as a field
names. If you cannot (or will not) change the names of the fields, at least
put square brackets around them:

SQL = "SELECT SETTINGS.[VALUE] FROM SETTINGS WHERE SETTINGS.ROOT='TAG' AND
SETTINGS.[SECTION]='Serial' AND SETTINGS.KEYNAME='Length';"


For a great discussion of what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[AlwaysFroosh!]

I had a suspicion it was something simple, Thanks Douglas.
Graham

Both Value and Section are reserved words, and shouldn't be used as a field
names. If you cannot (or will not) change the names of the fields, at least
put square brackets around them:

SQL = "SELECT SETTINGS.[VALUE] FROM SETTINGS WHERE SETTINGS.ROOT='TAG' AND
SETTINGS.[SECTION]='Serial' AND SETTINGS.KEYNAME='Length';"


For a great discussion of what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I am trying to set a variable in my code to a value located in a table
using
the following code:

Dim LengthRecord As New ADODB.Recordset
Dim SQL As String

SQL = "SELECT SETTINGS.VALUE FROM SETTINGS WHERE SETTINGS.ROOT='TAG' AND
SETTINGS.SECTION='Serial' AND SETTINGS.KEYNAME='Length';"

LengthRecord.Open SQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly

TagLength = LengthRecord("VALUE")


I keep getting an error that says:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

What am I doing wrong? The Select statement only returns one value from
the
table.

Thanks for the help,
Graham