query for sql queries in vb

Guys

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

Thanks
DarrenJacksonAsked:
Who is Participating?
 
nutschCommented:
Something like this?

Public Const sCapexSQLConnection As String = "PROVIDER=SQLOLEDB;" & _
            "DATA SOURCE=DCC08SQL;INITIAL CATALOG=CAPEX;" & _
            " 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
conn.Open

'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
        Next
    End If
       
Else
    Err.Raise 985
End If

conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

Open in new window

0
 
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

Regards
0
 
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
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents
   
    ' 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
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

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

Thankyou
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.