Automation error when trying to open an ADO recordset

[Julia B]

Hi, I've got the a function within a module that's called to find a record
(returns an integer). On the rs.Open line I get error "-2147467259 Automation
error Unspecified error" which is very unhelpful. I've tested the SQL which
works fine if I create a query using it in my database, so I'm really at a
loss. Can anyone help (it's Access 2003)? Here's the code:

Private Function findPlantRecordNo(Plant As String) As Integer
'called for when creating the new detail level record
Dim rs As New ADODB.Recordset
Dim strSQl As String
strSQl = "SELECT RecordID FROM Plants WHERE Plant = '" + Plant + "' AND
Current = True"
rs.Open strSQl, db, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If rs.RecordCount = 0 Then
findPlantRecordNo = 0
Else
findPlantRecordNo = rs!RecordID
End If
rs.Close
End Function

Thanks
Julia

 

[Julia B]

Solved it! As soon as I'd pressed Post I realised what it was. The SQL works
fine directly in Access but not through ADo because Current is a reserved
word. I've just rewritten it as follows and it works fine:

strSQl = "SELECT RecordID FROM Plants WHERE Plant = '" & Plant & "' AND
[Current] = True"

Sorry if I've wasted anyone's time!
Julia

 

[Jim Burke in Novi]

You're using + to concatenate when you create your SQL string. You should be
using & instead.

 

[Klatuu]

Glad you found the problem, but can I offer a more efficient solution that
executes faster and takes one line of code:

findPlantRecordNo = Nz(DLookup("[RecordID]", "Plants", "[Plant] = """ &
Plant & """ AND [Current] = " & True, 0))

--
Dave Hargis, Microsoft Access MVP

Solved it! As soon as I'd pressed Post I realised what it was. The SQL works
fine directly in Access but not through ADo because Current is a reserved
word. I've just rewritten it as follows and it works fine:

strSQl = "SELECT RecordID FROM Plants WHERE Plant = '" & Plant & "' AND
[Current] = True"

Sorry if I've wasted anyone's time!
Julia

Hi, I've got the a function within a module that's called to find a record
(returns an integer). On the rs.Open line I get error "-2147467259 Automation
error Unspecified error" which is very unhelpful. I've tested the SQL which
works fine if I create a query using it in my database, so I'm really at a
loss. Can anyone help (it's Access 2003)? Here's the code:

Private Function findPlantRecordNo(Plant As String) As Integer
'called for when creating the new detail level record
Dim rs As New ADODB.Recordset
Dim strSQl As String
strSQl = "SELECT RecordID FROM Plants WHERE Plant = '" + Plant + "' AND
Current = True"
rs.Open strSQl, db, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If rs.RecordCount = 0 Then
findPlantRecordNo = 0
Else
findPlantRecordNo = rs!RecordID
End If
rs.Close
End Function

Thanks
Julia