excel vba sql statement returning a single string

Question

I am writing an sql statement for an access database that will return a unique value regardless of the inputs. I am using this code however I am getting a type mismatch on the execute statement.

strSQL = "SELECT FilePath " _
    & "FROM ToolFiles " _
    & "WHERE Project_Num = '" & theSelectedProj & "'" _
    & "AND Tool_Name = '" & theSelectedProjName & "'"

filePath = cn.Execute(strSQL)

Is there a way to return a string from an sql statement?

Thanks

markblandford · Accepted Answer · 2012-03-12 14:16:40Z

5

The quick answer is No. The ADO Execute() method returns a recordset object which you will need to read into your string variable. Something like this should do it:

Dim rs As ADODB.Recordset

....
Set rs = cn.Execute(strSQL)

If Not (rs Is Nothing) Then
    With rs
        If Not (.BOF) And Not (.EOF) Then
            strFilePath = Format$(.Fields(1).Value)
        End If
    End With
End If

answered Mar 12, 2012 at 14:16

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gaffi Over a year ago

Shouldn't this also include a check at some point to verify the number of records ==1? Even if we know (think) that only one record is returned, I'd think it better to take that into account.

markblandford Over a year ago

@Gaffi normally I would agree but from the example SQL given, I think it's a fair assumption that there will only be one record. Finally, with the implicit .Execute the recordset is opened with a adOpenForwardOnly cursor with which I believe it is not possible to use the RecordCount property with anyway.

Collectives™ on Stack Overflow

excel vba sql statement returning a single string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related