Viewing results of a recordset before pasting 2

[AngelB]

Hi all

I am importing data from an access database into an Excel spreadsheet based on a simple query. I can see that there is one record in the recordset, but upon trying to paste it into the spreadsheet no data is displayed. I initially assumed that this was because all the values were null or zero, but running the same query in access directly returns one row with numeric values in each field (some of which are zero)

I was wondering if there was a way of viewing the results of the recordset before pasting it so I can see if the same values I am seeing from the access query are in fact coming over to the spreadsheet.

Many Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[SkipVought]

Hi,

What method are you using?

Please post your code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AngelB]

Sorry, that was negligent on my part.

I am using VBA as below:

Sub GetData()

    Dim cnnMyConn As ADODB.Connection
    Dim rstMyData As ADODB.Recordset
    Dim sSQL As String

    FromDate = Format(Range("A24").Value, "dd/mm/yyyy")
    ToDate = Format(Range("B24").Value, "dd/mm/yyyy")

    sSQL = "SELECT SUM(NumFatalCas_All), SUM(NumKSI_All), " & _
                    "SUM(NumKSI_Pedestrian), SUM(NumKSI_Child), " & _
                    "SUM(NumKSICol_All), SUM(NumPICol_All), " & _
                    "SUM(NumPICol_Pedestrian), SUM(NumPICol_Child), " & _
                    "SUM(NumPICas_All), SUM(NumPICas_Pedestrian), " & _
                    "SUM(NumPICas_Child) " & _
                    "FROM tblCasualtyHistory WHERE SiteID = '" & CStr(ActiveSheet.Name) & "' " & _
                    "AND PeriodStartDate >= " & FromDate & _
                    "AND PeriodEndDate <= " & ToDate

    DBConn = "C:\Data Folder\MyDatabase.mdb"

    Set cnnMyConn = New ADODB.Connection
    With cnnMyConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open DBConn
    End With

    Set rstMyData = New ADODB.Recordset
    rstMyData.CursorLocation = adUseClient
    rstMyData.Open Source:=sSQL, ActiveConnection:=cnnMyConn, _
        CursorType:=AdForwardOnly, LockType:=adLockOptimistic, _
           Options:=adCmdText

    If rstMyData.EOF Then
        MsgBox "No Data Exists for this Site"
        Exit Sub
    End If

    Range("C24").CopyFromRecordset rstMyData

    rstMyData.Close
    cnnMyConn.Close

End Sub

The messagebox in the EOF check does not appear, but pasting the data into the spreadsheet with the copyfromrecordset method yields a blank row - implying that the recordset contains a row of blank values. When querying the database directly I get one row containing the numeric values I would expect.

Let me know if you need any more info
Thanks again


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[xlbo]

Hi Gez - something like this?

For i = 0 to rstMyData.Fields.count -1
debug.print rstMyData.Fields(i).Name
debug.print rstMyData.Fields(i).Value
Next i



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

    sSQL = "SELECT"
    sSQL = sSQL & "  SUM(NumFatalCas_All)"
    sSQL = sSQL & ", SUM(NumKSI_All)"
    sSQL = sSQL & ", SUM(NumKSI_Pedestrian)"
    sSQL = sSQL & ", SUM(NumKSI_Child)"
    sSQL = sSQL & ", SUM(NumKSICol_All)"
    sSQL = sSQL & ", SUM(NumPICol_All)"
    sSQL = sSQL & ", SUM(NumPICol_Pedestrian)"
    sSQL = sSQL & ", SUM(NumPICol_Child)"
    sSQL = sSQL & ", SUM(NumPICas_All)"
    sSQL = sSQL & ", SUM(NumPICas_Pedestrian)"
    sSQL = sSQL & ", SUM(NumPICas_Child)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM tblCasualtyHistory"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE SiteID          = '" & CStr(ActiveSheet.Name) & "'"[b]
    sSQL = sSQL & "  AND PeriodStartDate >= #" & FromDate & "#"
    sSQL = sSQL & "  AND PeriodEndDate   <= #" & ToDate & "#"[/b]

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Furthermore, I would change the ambiguous date string format that you posted...

    FromDate = Format(Range("A24").Value, "dd/mm/yyyy")
    ToDate = Format(Range("B24").Value, "dd/mm/yyyy")

to the UNAMBIGUOUS yyyy/mm/dd string format

    FromDate = Format(Range("A24").Value, "yyyy/mm/dd")
    ToDate = Format(Range("B24").Value, "yyyy/mm/dd")

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AngelB]

You wonderful wonderful people, feel like kicking myself now but thank you all the same!


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[SkipVought]

So what was the solution?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

I'm guessing it was the date definitions making it not reurn expected results...when the query was running in Access, the date prompts would've been properly recognised - probably not so when executed from Excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AngelB]

Yes, as Geoff says it was the missing hash characters that were preventing the dates being read into the SQL string correctly.

Thanks again


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[SkipVought]

The # delimiters CONVERT the date text, to date values which are pure NUMBERS.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}