SQL connection from Excel VBA

[jag7777777]

Hi Everyone,

Could somebody help me please with the code that I need to gain a connection with a SQL server? Assuming the following:

Server Name: Server
DB Name: DB
Login name: login
Password: password

Could someone please post what the procedure would look like in VBA so I could run a SELECT * FROM TABLE script.

Could you also please post how I could make this appear in a particular worksheet in a particular range within a workbook if I put this code in a standard module.

Thanks in advance,

jag7777777

 

[PHV]

Why not simply use MS-Query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Data > Get External Data...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

If you insist on using code, consider using a function that returns the connection string. I use one that I send in either a "D" for development or "P" for production.

Function GetConnectString(strEnviron As String) As String
    Select Case strEnviron
        Case "D" 'development
            GetConnectString = "driver={sql server};" & _
                      "server=DevServerNameHere;" & _
                      "Database=DatabaseNameHere;UID=UserName;PWD=Password;"
        Case "P" 'Production
            GetConnectString = "driver={sql server};" & _
                      "server=PrdServerNameHere;" & _
                      "Database=DatabaseNameHere;UID=UserName;PWD=Password;"
    End Select
End Function

Then code might be:

    Dim strConnect As String
    Dim cmd As ADODB.Command
    Dim strSQL as String
    strConnect = GetConnectString("D")  [green] 'SQL Server connection information (dev or prod)[/green]
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.ConnectionString = strConnect
    cn.Open
    strSQL = "SELECT * FROM TABLE"
    With rs
        .ActiveConnection = cn
        .Open strSQL
        Do Until .EOF
            .MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing
    Set cn = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

You can also do something similar via MS Query/VBA

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jag7777777]

Thanks for the replies everyone. dhookom - thanks for the time taken to post the code - extremely helpful post.

skipvought - can you point me in the direction of some good resource for MS Query - I know nothing about it!

I'll have a look at both options and then see which one is best for what we need.

I'd usually use SSIS to put something straight into an XL worksheet - but as we have a load of VBA spreadsheets with exact locations for datasources (that are currently being imported from a copy / paste SSRS report) I'm trying to evaluate various options to speed things up and remove the issues of upgraded SSRS reports and formatting issues!

Thanks again for the replies.

jag7777777