Connect to SQL Server using DAO & VBA

To the best of my knowledge DAO is better suited for Access databases . I'd use ADO as the better library for SQL Server connections these days.

You have more than one option.

1)
You can use Data | New Database Query... as a tool right in Excel. This will use a Data Source Name that you can save and re-use. See Zapawa's book Excel Advanced Report Development for the excellent resources on this, or keep posting your questions here.

2)
For ADO, there's a lot to learn at first although the basics are extremely simple. The first thing you'll need is a connection string and the proper authentications. Try this connection string trick and see if it works for you (otherwise, you can google connection strings and you'll find more info):

There's a lot of good info on ADO at W3 Schools. ADO has some three main objects (the Recordset object, the Connection object, and the Command object) and they can all be used to accomplish your task. The simplest is to use the Recordset object, but the Command object is capable of updates, deletes, and so on.

I also found Kimmel et al. Excel 2003 VBA to be very helpful with their chapter on ADO (specifically deals with SQL server as well - also where I found the trick posted in the link above).

Here's an example of an ADO routine. The only difference for you would be the connection string and the the SQL statement:

HTH

Hi,

Thanks for your reply. i'm going to check out W3 Schools and keep checking things out on the web. In the meantime, I have found a lot of things on the web that show me how to connect to an Access File (Database) but I have yet to find a string that would enable me to connect to a SQL Server database.


Do you know how to modify this string to connect to a SQL Server database?

Const MY_DATABASE_PATH As String = _
"\\Emc_nas\Users\David.Pecot\Ultra_Inventory 2007.mdb"


If I discover the answer, I'll post it here.

Thanks

These are from the site below and match other information on connection strings I’ve seen--there’s various options but what I have here is fairly standard I believe. Trusted connection would be if your windows ID authenticates you, rather than entering a password/ID to logon:
http://www.sqlstrings.com/SQL-Server-connection-strings.htm

Did you try my UDL connection string trick from my earlier post?

Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

Click to expand...

I would add (habit I guess):
"Persist Security Info=False;”

As promised I found a way to do what I wanted and so I'm posting my response. I can't take credit for the response, as it was a professor who sent it to me from a school I attended. The response that he provided me. This is a working solution but you need to put in the username and password of the MS SQL Server you are trying to access and the code should be run locally on the machine/server where the database is stored. If you are trying to use access a machine on a remote server, I have included instructions in the comments on how to do so. This code will connect to the database Northwind but can be modified to connect to any database fairly easily by simply changing the name of the database and updating the sql statement.


Thanks for your help Alex. I did try the shortcut method but wasn't able to get it to work right. I might continue to play around with it in my spare time because it seems like a neat trick to know all the same.

Thanks for posting back. It's good material to add to the board for future reference. --AB

Alexander Barnes said:

Thanks for posting back. It's good material to add to the board for future reference. --AB

Click to expand...

I agree! Just used it. Thanks!

gduron said:

As promised I found a way to do what I wanted and so I'm posting my response. I can't take credit for the response, as it was a professor who sent it to me from a school I attended. The response that he provided me. This is a working solution but you need to put in the username and password of the MS SQL Server you are trying to access and the code should be run locally on the machine/server where the database is stored. If you are trying to use access a machine on a remote server, I have included instructions in the comments on how to do so. This code will connect to the database Northwind but can be modified to connect to any database fairly easily by simply changing the name of the database and updating the sql statement.

Code:

Sub getData()
     Dim conn As Variant
     Dim rs As Variant
     Dim cs As String
     Dim query As String
     Dim row As Integer
 
     Set conn = CreateObject("adodb.connection")
     Set rs = CreateObject("adodb.recordset")
 
    'The database in this instance has been set as Northwind.
    'you will need to update the database for what yours is called.
    'The IP Address '127.0.0.1' represents localhost.  If you
    'are trying to connect to a remote sql server then you will
    'either need to enter the ip address or URL of that server.
    'In the connection string below, 1433 is the port number
    'the SQL server is listening on.  If your sql server is
    'listening on a different port you'll have to change it.
    '1433 is the default port for SQL Server.
    
 
          cs = "DRIVER=SQL Server;"
     cs = cs & "DATABASE=Northwind;"
     cs = cs & "SERVER=127.0.0.1,1433"
 
     'parameters here are connectionSring, username, password
     'you will need to put the actual username and password in
     'quotes here for this code to work.
     conn.Open cs, "<PUT_USER_NAME_HERE>", "<PUT_PASSWORD_HERE>"
 
     query = "select * from Customers"
     rs.Open query, conn
 
     row = 0
     Do Until rs.EOF
       row = row + 1
       Cells(row, 1).Value = rs.fields("ContactName").Value
       rs.movenext
     Loop
 
     rs.Close
     Set rs = Nothing
 
     conn.Close
     Set conn = Nothing
 
End Sub

Thanks for your help Alex. I did try the shortcut method but wasn't able to get it to work right. I might continue to play around with it in my spare time because it seems like a neat trick to know all the same.

Click to expand...

Over 5 years are you posted this, but was great for me!

thanks a lot