VBA to Log into SQL Server Login

[mab9]

I current have an Excel that is built to query off an SQL server. Where I am stuck is access to the server required a login. When the query runs, and the user hasn't entered the username/password, its coming up with a prompt. I'm trying to figure out how the first step I can do it have VBA automatically log in since the people who are going to be using this won't know the login detail. Looking around, I've found:

Sub sql_login()

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=MyDatabase;" & _
           "Uid=MyUserID;" & _
           "Pwd=MyPassword"

End Sub

It seems simple enough but when I try to run it, I'm getting run-time error 424, object required. I believe it has something to do with oConn not being declared as anything but I'm not sure what to set it as.

Any ideas?

 

[blazonQC]

You are right, the run-time error you are getting is because you didn't declare the oConn object. The first thing you need to do is add a Reference to the 'Microsoft ActiveX Data Objects Library' .

Just in case you don't know how to set a reference:

Open the VBE (Alt+F11) and on the menu goto >Tools>References...

Once you have your references set you need to declare 'oConn' as an ADO connection:

Dim oConn as ADODB.Connection

Set oConn = New ADODB.Connection

If you add the reference and the Object declaration to your existing code snippit, It should actually do something..... but not much. Here is something I threw together based on your code. As a forewarning, I haven't looked up connection strings for SQL Server yet, so i just used yours:

Sub SQLConnect(ServerName As String, dbName As String, uName As String, pWord As String)
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
 

 

    On Error GoTo ErrHand

     With cn

        
        .ConnectionString = "Driver={SQL Server};Server=" & ServerName & _
                            ";Database=" & dbName & ";" & _
                            "Uid=" & uName & ";" & _
                            "Pwd=" & pWord
        .Open

    End With

        Call MsgBox("Connection to DB successful!", vbOKOnly + vbInformation, "Success!")

ExitHere:

    On Error Resume Next

    cn.Close
    Set cn = Nothing

    Err.Clear

    Exit Sub

ErrHand:


    MsgBox "Connection not propertly defined.", vbExclamation

    Resume ExitHere

End Sub

Note: Untested

I set this up to accept all the connection string variables as arguments passed to the Sub, so you don't have to hardcode the connection settings. i.e Username, Password, Dbase, etc....

As it is, this sub will only Connect to the server and disconnect, but it will let you know whether it is successful or not. Once you get everything set up correctly you can add more to it.

HTH,
Chad

 

[mab9]

Ah yes, it didn't even dawn on me to look at the reference library. It looks like its properly connecting to the server. It's returning the "connect to db successful" message so I would imagine its connect & logging in successfully. However, when it tries to refresh the query, its still coming up with that login pop-up.

Think it might have something to do with the query being built in MS Query (*.dgy) instead of direct SQL code? It's a simple query but right now its being refreshed using:

Selection.QueryTable.Refresh BackgroundQuery:=False

I tried putting the refresh call before the cn.Close, etc, but no luck there yet. Can you embed SQL queries inside the VBA? If so, I'll try writing it that way & see what happens.

 

[blazonQC]

Can you embed SQL queries inside the VBA? If so, I'll try writing it that way & see what happens.

You sure can, and that was actually going to be my suggestion before you even asked it. .

One way would be to build a query, and use the connection execute function:

Dim sqlString as String

sqlString = "SELECT * FROM MyTable"

...

cnn.Execute(sqlString)

But even inserting data into the table with queries like that causes a lot of problems sometimes. The best method I find is to use the ADO Recordset. The recordset is basically temporary local storage of the query return. You can create the recordset object like this (reference is the same):

Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset

Now that you have your recordset object and your query string built you can utilise them like so:

Dim rs as ADODB.Recordset
Dim sqlString as String

Set rs = New ADODB.Recordset
...

(Connection)

..

sqlString = "SELECT * FROM MyTable"
rs.Open sqlString, cnn, adOpenStatic, adLockOptimistic
...

rs.close

Set rs = Nothing

End Sub

The rs.Open Method is structured like this:

rs.Open Source, ActiveConnection, CursorType, LockType, Options

You can find more info about ADO at http://msdn2.microsoft.com/en-us/library/ms678086.aspx

If you want I can also post up an entire working example of this routine for an Access DB that can be easily modified for use with SQL? It also shows how to manipulate the recordset as well. Hope this helps, and sorry if these posts are really long.

Chad

 

[mab9]

I really appreciate the help on this. I think I've got the SQL parts working. The sub right now looks like:

Sub SQLConnect(servername As String, dbname As String, uname As String, pword As String)

    '******************************************************
    ' Logs into SQL Server
    '******************************************************
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    On Error GoTo ErrHand
        With cn
            .ConnectionString = "Driver={SQL Server};Server=" & servername & _
                ";Database=" & dbname & ";" & _
                "Uid=" & uname & ";" & _
                "Pwd=" & pword
                .Open
        End With
    
        Call MsgBox("Connection to DB successful!", vbOKOnly + vbInformation, "Success!")
        
    '******************************************************
    ' Performs SQL Query
    '******************************************************
    Dim rs As ADODB.Recordset
    Dim sqlString As String
    Set rs = New ADODB.Recordset
    
    sqlString = "SELECT * from MyTable"
    rs.Open sqlString, cn, adOpenStatic, adLockOptimistic
    cn.Execute (sqlString)
        
        
ExitHere:
    On Error Resume Next
    cn.Close
    Set cn = Nothing
    Err.Clear
    Exit Sub

ErrHand:
    MsgBox "Connection not propertly defined.", vbExclamation
    Resume ExitHere

End Sub

It isn't returning any errors when it run, but how do I return the query results to a specifc cell? Maybe that access example would help!

 

[blazonQC]

Well, it depends on how much data your query returns. If you don't have a lot of data being transfered at once, you could always loop the recordset like this:

i = 1    

With rs.Fields 
   
  Do until rs.EOF
              
         Worksheets(1).Cells(i, 1).Value = .Item("User").Name 
         Worksheets(1).Cells(i, 2).Value = .Item("User").Value 
         i = i + 1
         rs.MoveNext

   Loop

End With

This Method tends to be rather inefficient when it comes to larger tables/returns.

If you had a large quantity of data to transfer directly to a spreadsheet w/o processing you could use the .CopyFromRecordset method in excel:

Dim fld as ADODB.Field

 'create field headers 
   i = 0 
   With Worksheets(1).Range("A1") 
     For Each fld In rs.Fields 
      .Offset(0, i).Value = fld.Name 
      i = i + 1 
    Next fld 
   End With 

   'transfer data to Excel 
   Worksheets(1).Range("A2").CopyFromRecordset rs

Like i said, it all depends on what you are trying to retrieve/accomplish in the end.

One other thing, I notice that the code you posted still has the Query set up like this:

sqlString = "SELECT * from MyTable"

Do you actually have a table called MyTable? I was just using that as an example. :P

For instance, the table I use for the first example on this post was called "User_Info" . I just wanna make sure you have your query set up properly.

Chad

 

[mab9]

Yeah, the MyTable is just the example. I have the real names in the VBA locally. I'm going to try out the .CopyFromRecordset method & see how that goes. It's a small set of information being returned currently but that can change quickly.

*Edit*
Got it working! Thanks for the help on this, I really apreciate it.

 

[blazonQC]

It's np at all. I'm glad I could help you with this. If you have anymore problems or questions, I would be more than happy to help you out.

Chad