Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access query feeding spreadsheet control in a user form
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate protected worksheets feeding into one control sheet | Excel Worksheet Functions | |||
Adding a control to a User Form | Excel Programming | |||
user form & control properties | Excel Programming | |||
user form and frame control | Excel Programming | |||
Spreadsheet Control on User Form | Excel Programming |