Solved: Listbox RowSource from Access

[Albrecht]

Hi guys

I'm making a Excel ADD-In application for a knowlede database at my work. All the data is located in an Access database and handled by a frontend in Excel (managed by ADO)

Until now I have put the data from Acces into a sheet and refered to it by the listbox rowsource property. And It worked fine in the .xls but when I create the ADD-In I can't get any data in the listbox. It is empty

Is there a simpler better way to do it?

Private Sub cbIdeData_Enter()

On Error GoTo FejlBehandler

'Åben forbindelse til Acces
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "Select * from IdeDB"
filenm = DBsti

Call GlobaleProcedurer.GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Workbooks("AluCoreBobleTank.xla").Sheets("Sheet3")
xlsht.Range("A1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing

Dim ListRange As Range
Set ListRange = Workbooks("AluCoreBobleTank.xla").Sheets("sheet3") .Range("A1", Sheet3.Range("C65000").End(xlUp))

'Initialiser Listbox
With cbIdeData
.ColumnCount = 3
.ColumnWidths = 30
.RowSource = ListRange.Address
.ListIndex = 0
End With

Set xlsht = Nothing

Exit Sub
FejlBehandler:
Select Case Err
Case -2147352571 'Der er NULL værdi i Access tabellen
Resume Next
Case -2147217913 'Der skrives NULL værdi til Access tabellen
Resume Next
Case -2147217900 'Der er ikke angivet et id nr
Exit Sub
Case Else
MsgBox ("Programmet er afsluttet pga udefineret fejl: " & Err.Number & " - " & Err.Description)
Exit Sub
End

[Bob Phillips]

Don't drop it onto a worksheet, put it into an array and load the array



Dim aryData As Variant

aryData = adors.GetRows

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing

'Initialiser Listbox
With cbIdeData
.ColumnCount = 3
.ColumnWidths = 30
.List = Application.Transpose(aryData)
.ListIndex = 0
End With

[Albrecht]

Thank you very much. :bow:

I had to change the SQL call a little bit and then it worked.

[Bob Phillips]

Thank you very much. :bow:

I had to change the SQL call a little bit and then it worked.

Why? Why does the SQL affect this technique?