Adding A Reference

[bilbo85]

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

[JKwan]

Give this a try:

Sub SQLQuery()
     
     'Microsoft ActiveX Data Objects 6.1 Library is required
     
    Dim rs As Object
    Dim cnSQL As Object
    Dim sqlString As String
    Dim colOffset As Integer
    Dim Cws As Worksheet
    Dim qf As Object
    colOffset = 0
     
    Set cnSQL = CreateObject("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 = CreateObject("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

[snb]

Sub M_snb()
    With CreateObject("ADODB.recordset")
        .Open Range("B1"),"Provider=SQLOLEDB.1; Integrated Security = SSPI; Initial Catalog = Database1; Data source = Server1"
        sheets.add ,sheets(sheets.count)
        Sheets(sheets.count).Cells(1).CopyFromRecordset .DataSource
    End With
End Sub

[bilbo85]

Give this a try:

Sub SQLQuery()
     
     'Microsoft ActiveX Data Objects 6.1 Library is required
     
    Dim rs As Object
    Dim cnSQL As Object
    Dim sqlString As String
    Dim colOffset As Integer
    Dim Cws As Worksheet
    Dim qf As Object
    colOffset = 0
     
    Set cnSQL = CreateObject("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 = CreateObject("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

This says that the variable is not defined when it gets to "adOpenStatic"....

[snb]

Pease do not quote.

[mdmackillop]

I believe you need to insert the numerical values for such; in this case 3 as googled: adLockOptimistic would also be 3

[JKwan]

When you use Late Bind, you need to supply your own constants, I do believe.

[bilbo85]

I believe you need to insert the numerical values for such; in this case 3 as googled: adLockOptimistic would also be 3

Thank you so much, this now works.

[snb]

Did you test http://www.vbaexpress.com/forum/show...l=1#post359891 ?