Hi there,
I have come across the following code on www.excelguru.ca and I was hoping that someone here might be able to help with a small problem that I am having. (Ken Puls, suggested asking here on his website....)
[vba]
Option Explicit
'Set reference to the Microsoft ActiveX Data Objects x.x Library!
'Global constants required
Const glob_sdbPath = "S:\BTeams\Ford\DST\Call Log Database\Dealer Support Database.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
Public Sub PopulateResults()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Populate the listbox with all values from the Access database
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim sSQL As String
'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT Manual_Covernote_Log.DealerNumber, Manual_Covernote_Log.CustomerName " & _
"FROM Manual_Covernote_Log ORDER BY Manual_Covernote_Log.DealerNumber;"
'Open connection to the database
cnt.Open glob_sConnect
'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows
'Place data in the listbox
With frmCNSearch.lbxResults
.Clear
.ColumnCount = 2
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With
'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
[/vba]
When I run the above code I get an error : 'Run-time error 13: Type Mismatch' and the following is highlighted :
[vba]
.List = Application.Transpose(rcArray)
[/vba]
Can anyone suggest why this would be occuring?
Thanks,