How do I populate a listbox in Excel with Access Data?

[keithaul]

Below is code where I want to retreive data from an access database and then populate that data into a Listbox in Excel. However when you look at the attached images you will see that is not happening. The list box is not fully populated and the other image shows that data does exist in the DB

For some reason, the ID field just gets populated but the other fields and headers do not show up in the listbox

How can I retrieve my fields from Access DB and then populate the data and headers into the listbox in Excel?

Sub ImportUserForm()
'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String
Dim i As Integer
Dim var


'add error handling
On Error GoTo errHandler:


'Disable screen flickering.
Application.ScreenUpdating = False


'clear the values from the worksheet
Sheet2.Range("A2:G10000").ClearContents


'get the path to the database
dbPath = Sheet1.Range("I3").Value


'set the search variable
var = Me.txtSearch
Set cnn = New ADODB.Connection ' Initialise the collection class variable

'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

'Create the SQL statement to retrieve the data from table.
If CheckBox1 = True Then
SQL = "SELECT * FROM PhoneList WHERE SURNAME = '" & var & "'"
Else
SQL = "SELECT * FROM PhoneList WHERE SURNAME LIKE '" & var & "%" & "'"
End If

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close


'clear memory
Set rs = Nothing
Set cnn = Nothing


'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Me.lstDataAccess.RowSource = ""
Exit Sub
End If

'Write the reocrdset values in the sheet.
Sheet2.Range("A2").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close


'clear memory
Set rs = Nothing
Set cnn = Nothing

'Enable the screen.
Application.ScreenUpdating = True

Me.lstDataAccess.RowSource = "DataAccess"
'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
End Sub

 

[Gasman]

You would need to add the data to the list box?

http://www.excel-easy.com/vba/examples/list-box.html

 

[keithaul]

thanks for your response. however the link doesn't' show me how to use the recordset object to write to the listbox.

how do i write code that loops through the recordset and add the rows to the list box and have headers in the list box

 

[Gasman]

Firstly I do not think you would get the headers by default, you would have to write those as well.
I am looking at exporting query data which will be a recordset in vba, to Excel for emailing to various people.
I have found an item of code which I will likely modify to suit my purposes. I cannot find it now at home, but have the link saved in work. I will post it from there.

From this thread http://www.access-programmers.co.uk/forums/showthread.php?t=122712 it would appear you need to separate the items with a ";"

So I would
Open the recordset (check for records existing)
Move first
Cocantenate fields with a ";" between each field.
Add to the list with .AddItem
move next until eof

HTH

 

[Gasman]

Here is the link I saved to be able to export my data to Excel.
This is actually placing the data in cells (which is what I need to do), but you could combine the logic with the additem method of the listbox?

https://office-watch.com/2014/exporting-filtered-data-to-excel/

HTH

 

[keithaul]

Below is what I had to do to fill in the listbox with the data from the DB

I first set a variable equal to my SQL Statement:
SQL = "SELECT [ID], [Surname], [FirstName], [Address], [Phone], [Mobile], [Value] FROM PhoneList

I then did the following after opening the recordset with my SQL statement:

With Me.lstDataAccess
.ColumnCount = rs.Fields.Count
.Column = rs.GetRows
End With

rs.MoveFirst
i = 0
With Me.lstDataAccess
'.Clear
Do


.AddItem

.List(i, 0) = rs![ID]
.List(i, 1) = rs![Surname]
.List(i, 2) = rs![FirstName]
.List(i, 3) = rs![Address]
.List(i, 4) = rs![Phone]
.List(i, 5) = rs![Mobile]
.List(i, 6) = rs![Value]
i = i + 1
rs.MoveNext
Loop Until rs.EOF
End With

At this point I still don't know how to access the field/column names in the recordset. Meaning how do I write a row in the listbox that display the actual column field names in the database?

Thanks

 

[Gasman]

Adjust to suit

   For i = 0 To rs.Field.Count
       .Cells(1, i+1) = rs.Fields(i).Name
   Next

Note I did not write this, but just googled and copied it from

http://stackoverflow.com/questions/8349474/the-best-possible-way-to-populate-vba/8356579#8356579

 

[Cronk]

There's a typo in
For i = 0 To rs.Field.Count

Should be
For i = 0 To rs.Field.Count -1