[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connecting to sql 2008 db through excel 2010 vba

Posted on 2012-09-06
10
Medium Priority
?
548 Views
Last Modified: 2012-10-21
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
0
Comment
Question by:daintysally
7 Comments
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38374884
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
 

Author Comment

by:daintysally
ID: 38374973
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
 

Author Comment

by:daintysally
ID: 38375799
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:daintysally
ID: 38380139
Can someone please help me?
0
 

Author Comment

by:daintysally
ID: 38404005
Is there anyone out there that help me get this to run properly?
0
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38410837
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38518514
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question