Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run SQL using parameters defined on Excel worksheet

Posted on 2005-02-24
11
Medium Priority
?
143 Views
Last Modified: 2013-12-25
I am currently designing a Excel spreadsheet that will display queries on an Access database.  What I would like is the flexibilty of pasting in SQL direct from Access into a single Excel cell (forming a column of SQL) with the parameters needed to run the query and where to populate it to in the adjacent columns.  

What I am having trouble in doing is finding creating a VB script that will run the SQL and parameters and populate the results in a specified sheet and cell.  The idea being that the user is able to create new queries in Access, paste the SQL from it into the sheet, define the inputs (dates, names etc.), specify the sheet and cell the query is to populate and run this code.

The Database location will also be specified on the Excel sheet so the sheet can be pointed to (and run) on different databases or be cloned to other users.

Thanks
0
Comment
Question by:Jimmyyip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 13393790
There are a number of ways to do this.  The two you should consider are Excel SQLRequest function or running ADO from Excel VBA.  That have you tried so far?

BTW, this question should be placed in the Excel TA.

Leon
0
 

Author Comment

by:Jimmyyip
ID: 13393865
I have been using the functions in Microsoft Data Access Objects (DAO) 3.6 library but was unsuccessful when attaching my string of text (SQL) to the openrecordset function.  Either I'm defining the paramenters incorrectly or have not used the function correctly.

Thanks for the rapid response.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13393948
Lets see your code and the place you are having the problem. What version of Excel are you using?

Leon
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:Jimmyyip
ID: 13394162
In this code I have called the query by the name it has been saved in Access rather than using the SQL in the excell cell (I have yet to figure that out)

I can use a simplier version of this code to return queries that need no input definition but I want the sheet to define the records it returns.

Thanks again


Sub Test_Get_Data()
Dim DB As Database
Dim RS As Recordset
Dim i As Integer
Dim qName As String
Dim sDate1 As String
Dim eDate1 As String
Dim qSQL As String
Dim rDatabase1 As String


rDatabase1 = Range("rDatabase").Value   ' the database path is saved into a range called "rDatabase"
qName = ActiveCell.Offset(0, 3).Value ' name of query in Access
'the below defines the parameters (in this case the range oif dates the query runs)
sDate1 = ActiveCell.Offset(0, 4).Value  'cell reference where parameter1 is located
eDate1 = ActiveCell.Offset(0, 5).Value
'qSQL = ActiveCell.Offset(0, 6).Value    'qSQL is the SQL in the Excell cell


Set DB = Workspaces(0).OpenDatabase(rDatabase1)
'*********************************
'This is where I need to define paramters to the DB and the errors start.

'*********************************
Set RS = DB.OpenRecordset(qName)

For i = 0 To RS.Fields.Count - 1
ActiveCell.Offset(0, i) = RS.Fields(i).Name
Next i
ActiveCell.Offset(1, 0).Select

Do
For i = 0 To RS.Fields.Count - 1

ActiveCell.Offset(0, i) = RS.Fields(i)
Next i
RS.MoveNext 'move to next rec
ActiveCell.Offset(1, 0).Select 'move to next row
Loop Until RS.EOF


End Sub
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13394285
>'*********************************
>'This is where I need to define paramters to the DB and the errors start.

Put a Debug.Print qName before the error line to see what values you are passing.  You may not be passing what you think.

Leon
0
 

Author Comment

by:Jimmyyip
ID: 13394673
The query I tried to run returned the error "Too few parameters expected 2."  I guess its because the query required a input for the start and end dates (sDate and eDate).  Also what line of code is needed to parse SQL rather than the query name to the db?

Jimmy
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13394923
>I guess its because the query required a input for the start and end dates (sDate and eDate).  

Don't guess.  Look at the string in the immedeate window before you pass it to the db.  You will be able to easily see many problems this way.

> Also what line of code is needed to parse SQL rather than the query name to the db?

You can pass SQL in exactly like you are passng the query name.  Your cell should contain something like:

="SELECT * FROM myTable WHERE sDate1 = #"&B1&"# AND eDate1 = #"&C1&"#"

Leon
0
 

Author Comment

by:Jimmyyip
ID: 13395198
I have viewed the results in the immediate window and it just show the values I have assigned each variable to without problem.  When a value is shown in this window does VB recognise it as a parameter or will it need some sort of VB function to specifically define it?

When I run this code now it debugs on

Set RS = DB.OpenRecordset(qName)

with the error Run-time error '13':
Type mismatch.

My sql is cut and pasted from Access and does not follow the format of your sample sql above.  Is this a issue?

Will using the below function in this way cause a problem?  I.e replacing the query name with the entire string of sql?

Set RS = DB.OpenRecordset(qSQL) ' where qSQL is the text from the Access sql in an excel cell

Cheers
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 750 total points
ID: 13395689
>I have viewed the results in the immediate window and it just show the values I have assigned each variable to without problem.

Can I take alook at them?  I maybe able to spot a problem, which you do not.

> When a value is shown in this window does VB recognise it as a parameter or will it need some sort of VB function to specifically define it?

VB does not know anything about these values, it just passes the string to the database and lets it handle things.

>with the error Run-time error '13':
Type mismatch.

I need to see the value of qName.  You also never answered my question as to what version of Excel you are using.

Leon
0
 

Author Comment

by:Jimmyyip
ID: 13401997
Leon,

Thanks for the pointers.  I have resolved my issue now.

Jimmy


0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13403264
Jimmy,

What was the problem?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

705 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