UtterAccess Discussion Forums

Full Version: .execute

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

PaulBrand

Apr 9 2012, 03:43 AM

Public Sub SubOpen()

Hi guys,

Is this the right way to populate a variable from an SQL statement in Excel? I get a type mismatch

Dim strvalue As Date
'strvalue = ""
'strvalue = getStr(6, 73, 8)

Dim strSQL As String
Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")

With cnn
.ConnectionString = "DSN=RDBWC;User ID=estgpb;PWD=B4ptist5;"
.Open
strSQL = "SELECT BUS_DAY_BFO_001_DT from PDB2I.HI_BUS_DAY_01 WHERE CAR_DAY_DT = '2012-04-01' "
Debug.Print strSQL
strvalue = .Execute(cnn, strSQL)
Debug.Print strvalue
End With

End Sub

DanielPineault

Apr 9 2012, 05:57 AM

Typically dates in Access ger surrounded with # and are formatted m/d/yyyy, so I'd try

CODE

    Dim strvalue        As Date
    'strvalue = ""
    'strvalue = getStr(6, 73, 8)

    Dim strSQL          As String
    Dim cnn             As Object
    Set cnn = CreateObject("ADODB.Connection")

    With cnn
        .ConnectionString = "DSN=RDBWC;User ID=estgpb;PWD=B4ptist5;"
        .Open
        strSQL = "SELECT BUS_DAY_BFO_001_DT from PDB2I.HI_BUS_DAY_01 WHERE CAR_DAY_DT = #4/1/2012#"
        Debug.Print strSQL
        strvalue = .Execute(cnn, strSQL)
        Debug.Print strvalue
    End With

cheekybuddha

Apr 9 2012, 06:05 AM

Hi,

The Execute() method of an ADODB connection returns a recordset. I guess you need something like:

CODE

    Dim dtValue As Date
    Dim strSQL As String
     Dim cnn             As Object
     Set cnn = CreateObject("ADODB.Connection")

     With cnn
         .ConnectionString = "DSN=RDBWC;User ID=estgpb;PWD=B4ptist5;"
         .Open
         strSQL = "SELECT BUS_DAY_BFO_001_DT from PDB2I.HI_BUS_DAY_01 WHERE CAR_DAY_DT = #4/1/2012#"
         Debug.Print strSQL
         With cnn.Execute(strSQL)
             If Not (.BOF And .EOF) Then
                 dtValue = .Fields(0)
             End If
             .Close
         End With
     End With
     Debug.Print "dtValue = " & dtValue

hth,

d

arnelgp

Apr 9 2012, 06:42 AM

dim rs as ADODB.Recordset
With cnn
.ConnectionString = "DSN=RDBWC;User ID=estgpb;PWD=B4ptist5;"
End With
strSQL = "SELECT BUS_DAY_BFO_001_DT from PDB2I.HI_BUS_DAY_01 WHERE CAR_DAY_DT = #4/1/2012#"

'Open a record set
rs.Open strSQL, cnn

'copy recordset data to worksheet
objWorkSheet.copyFromRecordSet rs

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.