Parameter in Excel for ODBC connection?

Posted on 2010-08-13
Last Modified: 2012-05-10
I have an Excel 2007 workbook that has a worksheet connected to an MS SQL Express DB.  I have an ODBC connection set up on my PC which I use to access this DB.  I've attached a screen shot of the Connection Properties Definition.  You'll see that the second to last line in the command string references a date.  I would like to have it instead reference a parameter to be input by the user.  I tried replacing the date with a question mark but Excel comes back with an error saying that I have an Invalid Parameter Number first and then an Invalid Descriptor Index after that.  When I tried to edit the query (with the embedded date) Excel told me "This query can not be editted by the query wizard."  Apparently the query cannot be graphically portrayed by MS Query and from what I can determine if MS Query cannot graphically represent a query then you cannot use the question mark to represent a parameter in that query.  This is all I have found out.

So, is there anyone who can tell me how I can get this connection string in the connection properties to have an input parameter for the date that is in the query?

Thanks,  Rich

PS  Here's the text of the query:

SELECT "inventory_transactions"."transaction_id", "users"."user_logon", "item"."item_number", "transaction_types"."trans_description", "inventory_transactions"."trans_date", "inventory_transactions"."entry_date", "inventory_transactions"."quantity", "inventory_transactions"."lot", "sites"."site_name", "location"."description", "inventory_transactions"."pallet", "notes"."table_name", "notes"."note_text"
 FROM   ((((("WaspTrackInventory"."dbo"."inventory_transactions" "inventory_transactions" INNER JOIN "WaspTrackInventory"."dbo"."location" "location" ON "inventory_transactions"."location_id"="location"."location_id") INNER JOIN "WaspTrackInventory"."dbo"."transaction_types" "transaction_types" ON "inventory_transactions"."trans_type"="transaction_types"."trans_type_no") INNER JOIN "WaspTrackInventory"."dbo"."users" "users" ON "inventory_transactions"."user_id"="users"."user_id") INNER JOIN "WaspTrackInventory"."dbo"."item" "item" ON "inventory_transactions"."item_id"="item"."item_id") LEFT OUTER JOIN "WaspTrackInventory"."dbo"."notes" "notes" ON "inventory_transactions"."transaction_id"="notes"."note_id") INNER JOIN "WaspTrackInventory"."dbo"."sites" "sites" ON "location"."site_id"="sites"."site_id"
 WHERE  ("transaction_types"."trans_description"='Add' OR "transaction_types"."trans_description" = 'Remove') AND "inventory_transactions"."entry_date">'8/1/2010'
 ORDER BY "inventory_transactions"."entry_date", "item"."item_number" Connection properties command string
Question by:RichNH
  • 4
  • 2
  • 2
  • +1

Accepted Solution

ebooyens earned 125 total points
ID: 33429085
Hi Rich, I would highly recommend doing your SQL logic in SQL - so download SQL Management Studio for the version of SQL Express you've got (if you haven't already), go in on that database and create a new view with your query string, except for the date requirement in the WHERE clause.  The call from Excel can then just be select * from viewname where "entry_date">parameter

Hope that makes sense.  If you do a lot of this stuff it's worth spending some time on SQL views and stored procedures.  Stored procedures are the preferred way of doing these types of things but SQL views are a bit easier to work with.

Author Comment

ID: 33429388
Normally, that is how I would do it.  However the DB is "closed" to me for updating (as in adding a new view).  The DB is the backbone of a canned package we have installed here.  To get new views made for it I would have to get the company we bought the package to do it and I don't think that the powers that be are going to spring for that.  However I will check out that option.


Expert Comment

ID: 33429437
O bugger.  I would set up an Analysis Services cube then (not sure if it's supported with express though) and pull all their data in on a schedule.  From there you can do with it whatever you like.
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 125 total points
ID: 33435140
I agree with ebooyens that the best solution is to create a view in SQL Server and call the view from MS-Query.  If that is not possible and you are comfortable with VBA then you might consider updating the QueryTable from VBA and pulling in the Date Parameter from a cell or Named Range.
I have included a simple example that may point you in the right direction.  It is an ODBC connection to an Access database so the SQL syntax may be slightly different.
In the sample code, the variable "DateParm" is pulled from a cell on a worksheet and is used as the Date Parameter in building the "CommandText" SQL string for the QueryTable before updating.

Sub Refresh_Query()

    Dim DateParm As Range

    Dim qt As QueryTable

    Set DateParm = Range("DateParm")

    SQLStr = "SELECT Table1.ID, Table1.Name, Table1.Age, Table1.Entry_Date " & _

             "FROM `C:\EE\Sample.accdb`.Table1 Table1 " & _

             "WHERE Table1.Entry_Date = #" & DateParm & "#"

    Set qt = Sheets("Sheet2").ListObjects(1).QueryTable

    With qt

        .CommandText = SQLStr

        .Refresh BackgroundQuery:=False

    End With

End Sub

Open in new window

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.


Expert Comment

ID: 33435267
convert date format in excel to numeric (Format cells -> number) then sort cells ASC, save and read
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33436177
Steve - I think you may have accidently posted in the wrong thread.
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33663765
The solution I posted is correct and allows for the parameter but if the asker is not familier with VBA then it will not be a solution for him specifically.  I am OK with whatever you decide on how to close this one.

Author Comment

ID: 33673121
My appologies, I was kidnapped at work and my life is gone now.  I do not have the time right now (nor the permission) to try both solutions but I do understand the necessity to close the question.  Both responses are excellent leads.  I started to pursue the first but was then kidnapped and haven't been able to get back to the original task.  I definitely would like to try the second too.

Do either of the experts mind if I split the points as both solutions appear to be good ones?

LVL 16

Expert Comment

by:Jerry Paladino
ID: 33673191
Thanks Rich - No problem from my end - I am OK with whatever you would like to do.
- Jerry

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now