Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Results returned from Access query | Excel Discussion (Misc queries) | |||
Advanced Query not returning correct results?! | Excel Discussion (Misc queries) | |||
Why are access query results different in Excel | Excel Discussion (Misc queries) | |||
Access query results to Excel | Excel Discussion (Misc queries) | |||
SQL Query not returning correct results | Excel Programming |