PDA

View Full Version : [SOLVED] Adding A Reference



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

JKwan
04-18-2017, 06:59 AM
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
04-18-2017, 07:27 AM
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
04-18-2017, 09:57 AM
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
04-18-2017, 11:51 AM
Pease do not quote.

mdmackillop
04-18-2017, 11:57 AM
I believe you need to insert the numerical values for such; in this case 3 as googled (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/cursortypeenum): adLockOptimistic (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/locktypeenum) would also be 3

JKwan
04-18-2017, 12:04 PM
When you use Late Bind, you need to supply your own constants, I do believe.

bilbo85
04-18-2017, 02:24 PM
I believe you need to insert the numerical values for such; in this case 3 as googled (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/cursortypeenum): adLockOptimistic (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/locktypeenum) would also be 3

Thank you so much, this now works.

snb
04-19-2017, 12:33 AM
Did you test http://www.vbaexpress.com/forum/showthread.php?59202-Adding-A-Reference&p=359891&viewfull=1#post359891 ?