bilbo85
04-17-2017, 10:41 AM
Hi,
I am trying to add a reference to Microsoft ActiveX Data Objects 6.1 Library automatically within my code so that it can be distributed to others without them having to add the reference manually. I have heard that late binding may be the solution but I am not sure how to implement it with my current code. Any ideas please?
Sub SQLQuery()
'Microsoft ActiveX Data Objects 6.1 Library is required
Dim rs As ADODB.Recordset 'holds data
Dim cnSQL As ADODB.Connection
Dim sqlString As String
Dim colOffset As Integer
Dim Cws As Worksheet
Dim qf As Object
colOffset = 0
Set cnSQL = New ADODB.Connection
cnSQL.Open "Provider=SQLOLEDB.1; Integrated Security = SSPI; Initial Catalog = Database1; Data source = Server1"
sqlString = Range("B1").Value
Set Cws = Worksheets.Add
Set rs = New ADODB.Recordset
rs.Open sqlString, cnSQL, adOpenStatic, adLockOptimistic
If rs.EOF Then
MsgBox ("The recordset is empty.")
End If
For Each qf In rs.Fields 'qf = query field
Cws.Range("A1").Offset(0, colOffset).Value = qf.Name
colOffset = colOffset + 1
Next qf
Cws.Cells(2, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
End Sub
I am trying to add a reference to Microsoft ActiveX Data Objects 6.1 Library automatically within my code so that it can be distributed to others without them having to add the reference manually. I have heard that late binding may be the solution but I am not sure how to implement it with my current code. Any ideas please?
Sub SQLQuery()
'Microsoft ActiveX Data Objects 6.1 Library is required
Dim rs As ADODB.Recordset 'holds data
Dim cnSQL As ADODB.Connection
Dim sqlString As String
Dim colOffset As Integer
Dim Cws As Worksheet
Dim qf As Object
colOffset = 0
Set cnSQL = New ADODB.Connection
cnSQL.Open "Provider=SQLOLEDB.1; Integrated Security = SSPI; Initial Catalog = Database1; Data source = Server1"
sqlString = Range("B1").Value
Set Cws = Worksheets.Add
Set rs = New ADODB.Recordset
rs.Open sqlString, cnSQL, adOpenStatic, adLockOptimistic
If rs.EOF Then
MsgBox ("The recordset is empty.")
End If
For Each qf In rs.Fields 'qf = query field
Cws.Range("A1").Offset(0, colOffset).Value = qf.Name
colOffset = colOffset + 1
Next qf
Cws.Cells(2, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
End Sub