query for sql queries in vb


I am after a vb script that will access a sql database and copy the contents to an excel sheet.

I am still in the early planning of this so a generic script with generic tables is OK.

Can anyone help

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Something like this?

Public Const sCapexSQLConnection As String = "PROVIDER=SQLOLEDB;" & _
            " INTEGRATED SECURITY=sspi;"

' Procedure : GetRecordset
' Date      : 5/9/2012
' Purpose   : This returns a recordset in a specified range (specify only the starting cell), with or without headers

Sub GetRecordset(rgDest As Range, sSQLCommand As String, Optional bHeaders As Boolean = False, Optional lCmdType As Long = adCmdText)

Dim conn As ADODB.Connection, rs As ADODB.Recordset, strConn As String
Dim cmd As ADODB.Command
Dim lLoop As Long

On Error Resume Next

'initialize connection
strConn = sCapexSQLConnection

Set conn = New ADODB.Connection
conn.ConnectionString = strConn

'initialize command
Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = conn
    .CommandType = lCmdType
    .CommandText = sSQLCommand
    Set rs = .Execute()
End With

If Not rs.EOF Then

    rgDest.CopyFromRecordset rs
    If bHeaders Then
        For lLoop = 0 To rs.Fields.Count - 1
            rgDest.Parent.Cells(rgDest.Row - 1, rgDest.Column + lLoop) = rs.Fields(lLoop).Name
    End If
    Err.Raise 985
End If

Set conn = Nothing
Set rs = Nothing
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor Commented:
I think so how can I use this?

as it doesn't seem to run in my excel vb window

example sql server = hts440417\mssql_testdev
database = Darren_test

sql statement  = select * from Darren_test_view

DarrenJacksonAuthor Commented:
Hi I have managed to work it out

it may not be elegant but its doing the job

Sub Test()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = Sheets("Sheet1").Cells(1, 1)
'Dim source As String
Dim user As String
Dim password As String
Dim Company As String
Dim rg As Range
    Source = Login.TextBox1.Value
    'user = Login.TextBox2.Value
    'password = Login.TextBox3.Value
    Database = Login.TextBox4.Value
    'Toggle the worksheets
    Sheets("Sheet1").Visible = True
    ' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=sqloledb;" & _
           "Data Source=" & Source & ";" & _
            "Initial Catalog=" & Database & ";" & _
            "INTEGRATED SECURITY=sspi;"
           '"User Id=" & user & ";" & _
           '"Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
    sSQL = "SELECT * from dbo.fixcode"
    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    Set rs = Nothing
    Set cn = Nothing
End Sub
DarrenJacksonAuthor Commented:
Awarding points for helping

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.