Connecting to sql 2008 db through excel 2010 vba

Hi Experts,

I am trying to create a connection in an Excel 2010 workbook to a sql server 2008 db so that the data can be refreshed in the worksheet entitled "Data".  The code below is what I have so far.  Can someone quickly assist me with what else I need to do so that I can move forward?

Sub RefreshData()

Dim cn As New ADODB.Connection _
      , rs As New ADODB.Recordset _
      , strServer, strDataBase, strSQL, strTOP, strWHERECondition As String _
      , intRow As Integer _
      , dtResultsDtTm As Date _
      , wsSheet As Worksheet _
      , rnStart As Range
   
    strServer = "server=newserver": strDataBase = "database=testdb"
       
    With cn
        .ConnectionString = "driver={SQL Server};" & strServer & ";Trusted_Connection=Yes;" & strDataBase & ";WSID=YTest
        .ConnectionTimeout = 500
        .CommandTimeout = 2500
        .Open
    End With
     
    strSQL = "select distinct" & _
         "a.field1,a.field2c,a.field3,a.field4,a.field5,a.field6t,a.field7," & _
         "a.field8,a.field9,b.field10 as 'Newfield',a.field20" & _
         "from testdb.dbo.testtbl as a" & _
         "inner join anothertestdb.dbo.anothertesttbl as b" & _
         "on a.field1 = b.field1" & _
         "where a.field3 = '209'" & _
         "and a.field5 >= '09/01/12'" & _
         "and a.field7 is not null"
         
    Set cn = New ADODB.Connection
daintysallyAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
OK, 3 main issues:
1) dimming of string variables is not correct:
this will dim the first 4 as variant data types (not string)
strServer, strDataBase, strSQL, strTOP, strWHERECondition As String

Open in new window

try:
strServer As String, strDataBase As String, strSQL As String, strTOP As String, strWHERECondition As String

Open in new window


2)  problem with the SQL you have built
the following code is off... eg "select distinct" & "a.field1,..." = ... distincta.field1...
strSQL = "select distinct" & _
         "a.field1,a.field2c,a.field3,a.field4,a.field5,a.field6t,a.field7," & _
         "a.field8,a.field9,b.field10 as 'Newfield',a.field20" & _
         "from testdb.dbo.testtbl as a" & _
         "inner join anothertestdb.dbo.anothertesttbl as b" & _
         "on a.field1 = b.field1" & _
         "where a.field3 = '209'" & _
         "and a.field5 >= '09/01/12'" & _
         "and a.field7 is not null"

Open in new window


try:
strSQL = "select distinct "
strSQL = strSQL & "a.field1, "
strSQL = strSQL & "a.field2c, "
strSQL = strSQL & "a.field3, "
strSQL = strSQL & "a.field4, "
strSQL = strSQL & "a.field5, "
strSQL = strSQL & "a.field6t, "
strSQL = strSQL & "a.field7, "
strSQL = strSQL & "a.field8, "
strSQL = strSQL & "a.field9, "
strSQL = strSQL & "b.field10 as 'Newfield', "
strSQL = strSQL & "a.field20"
strSQL = strSQL & vbCrLf
strSQL = strSQL & "from testdb.dbo.testtbl as a " & _
strSQL = strSQL & "inner join anothertestdb.dbo.anothertesttbl as b "
strSQL = strSQL & "on a.field1 = b.field1"
strSQL = strSQL & vbCrLf
strSQL = strSQL & "where a.field3 = '209' "
strSQL = strSQL & "and a.field5 >= #09/01/12# "
strSQL = strSQL & "and a.field7 is not null;"

Open in new window


3) the query table.add does not have the connection string in it.

I will try to take the time to go over the full code at some point, but these are some of the main errors.

added: the recordset and connections are (I belive) opened by the query table add so not needed as NEw or Set or as objects:

Try to just use MS Query from the dataother sources option and record yourself setting up the query. The recorder will record a starting point for you.
0
 
nishant joshiTechnology Development ConsultantCommented:
this is because you have double time set connection.

as set connection in last line:
    Set cn = New ADODB.Connection

Open in new window

0
 
daintysallyAuthor Commented:
ok, what do I need to do to get the query to run and paste the results into cell A10 of the 'Data' worksheet?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
daintysallyAuthor Commented:
PLEASE HELP!!  This is what I have so far, and I am getting this error: Object variable or With block variable not set

Sub RefreshData()

Dim cn As New ADODB.Connection _
      , rs As New ADODB.Recordset _
      , strServer, strDataBase, strSQL, strTOP, strWHERECondition As String _
      , intRow As Integer _
      , dtResultsDtTm As Date _
      , wsSheet As Worksheet _
      , rnStart As Range
   
    strServer = "server=newserver": strDataBase = "database=testdb"
       
    With cn
        .ConnectionString = "driver={SQL Server};" & strServer & ";Trusted_Connection=Yes;" & strDataBase & ";WSID=YTest
        .ConnectionTimeout = 500
        .CommandTimeout = 2500
        .Open
    End With
     
    strSQL = "select distinct" & _
         "a.field1,a.field2c,a.field3,a.field4,a.field5,a.field6t,a.field7," & _
         "a.field8,a.field9,b.field10 as 'Newfield',a.field20" & _
         "from testdb.dbo.testtbl as a" & _
         "inner join anothertestdb.dbo.anothertesttbl as b" & _
         "on a.field1 = b.field1" & _
         "where a.field3 = '209'" & _
         "and a.field5 >= '09/01/12'" & _
         "and a.field7 is not null"

If wsSheet.Name = "Data" Then
            wsSheet.Range("A:IV").Delete
            rs.Open strSQL, cn
            With wsSheet.QueryTables.Add(Connection:=rs, Destination:=wsSheet.Range("A10"))
                .Refresh BackgroundQuery:=False
            End With
            rs.Close
        End If
    cn.Close: Set cn = Nothing: Set rs = Nothing
0
 
daintysallyAuthor Commented:
Can someone please help me?
0
 
daintysallyAuthor Commented:
Is there anyone out there that help me get this to run properly?
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.