CODEWITHSUNDEEP
excelms-accessvba
thebiglebowski11
thebiglebowski11

Reputation: 1461

excel vba sql statement returning a single string

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

Upvotes: 2

Views: 3441

Answers (1)

markblandford
markblandford

Reputation: 3193

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

Upvotes: 5

Related Questions