Access query not returning results  
Author Message
ColTom2





PostPosted: Top

Excel Programming >> Access query not returning results

Hello all,

Okay, it's Monday and my brain is still waking up. Why does the
following code return no results? I checked the database and the
table. Everything is there. I ran the code without checking for
returned results and I can get it to populate the first row with the
field names. And that was as far as I could get. Any help would be
greatly appreciated.

Thanks,

Rich



Private Sub UserForm_Initialize()
Dim DBFullName As String
Dim strConnection As String, SIQuery As String
Dim AllCells As Range, Cell As Range
Dim SIAccessConnection As ADODB.Connection
Dim DispBoardRecordset As ADODB.Recordset
Dim BoardRecordset As ADODB.Recordset
Dim TechnicianRecordset As ADODB.Recordset
Dim StateRecordset As ADODB.Recordset
Dim MapCoordRecordset As ADODB.Recordset
Dim CityRecordset As ADODB.Recordset
Dim ZipRecordset As ADODB.Recordset
Dim ServTypeRecordset As ADODB.Recordset
Dim Col As Integer, Row As Integer
Dim NumCols As Integer, NumRows As Integer
Dim VarOrderDate As Variant
'Database Information
DBFullName = "P:\Software\Internal\KOB-SI-MapPoint.mdb"
'Open the connection
Set SIAccessConnection = New ADODB.Connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; "
strConnection = strConnection & "Data Source=" & DBFullName & ";"
SIAccessConnection.Open ConnectionString:=strConnection
' Get all Open Orders
Set DispBoardRecordset = New ADODB.Recordset
With DispBoardRecordset
SIQuery = "SELECT * FROM tblCounties"
.Open Source:=SIQuery, ActiveConnection:=SIAccessConnection
If Not DispBoardRecordset Is Nothing Then
If DispBoardRecordset.RecordCount > 0 Then
NumCols = DispBoardRecordset.Fields.Count
NumRows = DispBoardRecordset.RecordCount
varOrderData = DispBoardRecordset.GetRows(NumRows)
For Col = 0 To NumCols - 1
ssDispBoard.ActiveSheet.Range("A1").Offset(0,
Col).Value = DispBoardRecordset.Fields(Col).Name
Next
For Row = 1 To NumRows
For Col = 1 To NumCols
ssDispBoard.ActiveSheet.Cells(Row + 1, Col) =
varOrderData(Col - 1, Row - 1)
Next Col
Next Row
End If
End If
End With
Set DispBoardRecordset = Nothing
Set BoardRecordset = Nothing
Set TechnicianRecordset = Nothing
Set StateRecordset = Nothing
Set MapCoordRecordset = Nothing
Set CityRecordset = Nothing
Set ZipRecordset = Nothing
Set ServTypeRecordset = Nothing
SIAccessConnection.Close
End Sub

Excel98