Access query feeding spreadsheet control in a user form

[rjchristensen]

Hello all,

I have made some headway in setting up my "dummy proof" user
application. I need some help accessing data from a query in an
Access 2003 database. Whenever I try to run the code, I get the
following error message:

Run-time error '3265';

Item cannot be found in the collection corresponding to the requested
name or ordinal. I searched some of the tech support sites and tried
different things, but nothing seems to help. I want to do the same
type of query to populate a number of listboxes as well. The code is
included below. 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
'Database Information
DBFullName = "P:\Software\Internal\KOB-SI-MapPoint.mdb"
'Open the connection
' This was one selected way of opening the connection
Set SIAccessConnection = New ADODB.Connection
'This was another way
Set SIAccessConnection = CreateObject("ADODB.Connection")
'This was the "Standard" connection to use
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & DBFullName & ";"
'This was the "Alternative" ODBC connection that I was told to try
strConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
DBFullName
'These were the two different "Open" strings I was told to try
SIAccessConnection.Open ConnectionString:=strConnection
SIAccessConnection.Open strConnection
,
'
Set DispBoardRecordset = New ADODB.Recordset
With DispBoardRecordset
SIQuery = "SELECT * FROM tblCounties;"
.Open Source:=SIQuery, ActiveConnection:=SIAccessConnection
'This is where I populate the column headings
For Col = 0 To DispBoardRecordset.Fields.Count
ssDispBoard.ActiveSheet.Range("A1").Offset(0, Col).Value =
DispBoardRecordset.Fields(Col).Name
Next
'This is where I populate the remainder of the spreadsheet control
ssDispBoard.ActiveSheet.Range("A1").Offset(1,
0).CopyFromRecordset DispBoardRecordset
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