0

First of all I'm new to VB so all help is very much appreciated.

I'm trying to populate a ComboBox in Excel with data from an SQL server.

The error is 'Dim cnt As ADODB.Connection' - Complie Error: User-defined type not defined

Sub Populate_ComboBox_From_SQL()
 Dim cnt As ADODB.Connection
 Dim rst As ADODB.Recordset
 Dim stDB As String, stConn As String, stSQL As String
 Dim xlCalc As XlCalculation
 Dim vaData As Variant
 Dim k As Long

 Set cnt = New ADODB.Connection

 stConn = "DSN=Backup;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=<database>"
 cnt.ConnectionString = stConn


 stSQL = "SELECT 'project no' FROM 'project register"

 With cnt
 .CursorLocation = adUseClient 
 .Open stConn 'Open connection.

 Set rst = .Execute(stSQL)
 End With

 With rst
 Set .ActiveConnection = Nothing 'Disconnect the recordset.
 k = .Fields.Count

 vaData = .GetRows
 End With


 cnt.Close


 With TEMPLATE
 With .ComboBox1
 .Clear
 .BoundColumn = k
 .List = Application.Transpose(vaData)
 .ListIndex = -1
 End With
 End With


 Set rst = Nothing
 Set cnt = Nothing
End Sub

The sheet is called TEMPLATE and the ComboBox is called ComboBox1. I have omitted the name of the SQL server in the connection string.

Thanks in advance.

Jackawan
  • 89
  • 10
  • Try using `GetRows` before you disconnect the recordset. Also, it's more efficient to use `.Column = vaData` rather than `.List = Application.Transpose(vaData)` – Rory Jun 15 '20 at 09:40
  • vaData = .GetRows before the Disconnection? – Jackawan Jun 15 '20 at 09:44
  • Does this answer your question? [Compiler Error: User-defined types not defined](https://stackoverflow.com/questions/5349580/compiler-error-user-defined-types-not-defined) – FunThomas Jun 15 '20 at 09:56
  • You've changed the question now, so my comment about moving that line is irrelevant. My second comment still applies though. – Rory Jun 15 '20 at 10:07

0 Answers0