getting database results into Excel

[Tarheel]

Hi all,

Until now, I have always used MSQuery to create queries with parameters, and then embedded the query into an Excel spreadsheet. This would allow me to anchor my parameters to certain cells on the spreadsheet. My report users could then change the values in those cells and click a button to refresh the query data. This works wonderfully with simple queries.

Now I have created a very complex query, specifically a query that uses a subquery as its SELECT FROM. When I try to set up my parameters in MSQuery for this query... it says "parameters are not allowed in queries that can't be displayed graphically." Without being able to put parameters in my query, I can't automate this using Excel.

Is there some other way to accomplish what I want to do? Is it possible to open a connection to the database another way, and pass it straight SQL? Any ideas or suggestions?

Thanks,
Richard

 

[moodleyr]

I believe you may be able to use OpenDatabase to make a connection to your database. Thereafter a query can be run using SQLRequest and manipulated with recordsets.
e.g.
Dim oldDbName As String
Dim wspDefault As Workspace
Dim dbsNorthwind As Database
Dim strSQL As String
Dim rstFromQuery As Recordset

'Set the path to the database
oldDbName = "C:\Msoffice\access\samples\Northwind.mdb"

'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)

'Create a Database object
Set dbsNorthwind = wspDefault.OpenDatabase(oldDbName)

'The SQL statement
strSQL = "SELECT Employees.LastName, Employees.FirstName, " & _
"Employees.Country FROM Employees Employees " & _
"WHERE (Employees.Country='USA')"

'Create a Snapshot Type Recordset from the SQL query
Set _
rstFromQuery = dbsNorthwind.OpenRecordset(strSQL,dbOpenSnapshot)


If you want to allow the user to enter parameters on the worksheet, create the sql statement using Cells().Value.

Hope this helps.

 

[Mark W.]

Hi all,

Until now, I have always used MSQuery to create queries with parameters, and then embedded the query into an Excel spreadsheet. This would allow me to anchor my parameters to certain cells on the spreadsheet. My report users could then change the values in those cells and click a button to refresh the query data. This works wonderfully with simple queries.

Now I have created a very complex query, specifically a query that uses a subquery as its SELECT FROM. When I try to set up my parameters in MSQuery for this query... it says "parameters are not allowed in queries that can't be displayed graphically." Without being able to put parameters in my query, I can't automate this using Excel.

Is there some other way to accomplish what I want to do? Is it possible to open a connection to the database another way, and pass it straight SQL? Any ideas or suggestions?

Thanks,
Richard

Is it possible to use a simple join instead of a subquery? Without the subquery does your query return more that 65,535 rows?

 

[Tarheel]

Hi Mark and moodleyr,

Mark... yes, without the subquery, there are WAY more than 65,000 rows.


moddleyr,

I tried the code you supplied, coupled with some code I found another place. Take a look at what I have and tell me what you think.

Sub dbconnect()
Dim oldDbName As String
Dim wspDefault As Workspace
Dim dbsGreenway As Database
Dim strSQL As String
Dim rstFromQuery As Recordset

'Set the path to the database
oldDbName = "path removed for security reasons"

'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)

'Create a Database object
Set dbsGreenway = wspDefault.OpenDatabase(oldDbName)

'The SQL statement
strSQL = "SELECT CAL_DT, BRAND_NAME, CHANNEL_NAME, PAGE_ID,
PAGE_DESC, PAGE_URL, SUM(IMPRESSIONS) as TOTAL_IMPRESSIONS,
SUM(CLICKS) as TOTAL_CLICKS" & _

"FROM (SELECT USAGE_DAILY_0.CAL_DT as CAL_DT,BRAND_0.BRAND_ID,
trim(BRAND_0.BRAND_NAME) as BRAND_NAME,CHANNEL_0.CHANNEL_ID,
trim(CHANNEL_0.CHANNEL_NAME) as CHANNEL_NAME,
PAGE_0.PAGE_ID as PAGE_ID,trim(PAGE_0.PAGE_DESC) as PAGE_DESC,
trim(PAGE_0.PAGE_NAME) as PAGE_URL,
USAGE_DAILY_0.ADSPACE_POSITION_ID,
USAGE_DAILY_0.CLUSTER_ID,
Min(USAGE_DAILY_0.TOTAL_IMPRESSIONS) as IMPRESSIONS,
Sum(USAGE_DAILY_0.TOTAL_RESPONSES) As CLICKS" & _

"FROM BRAND BRAND_0, CHANNEL CHANNEL_0, PAGE PAGE_0, USAGE_DAILY
USAGE_DAILY_0" & _

"WHERE BRAND_0.BRAND_ID = USAGE_DAILY_0.BRAND_ID AND
CHANNEL_0.CHANNEL_ID = USAGE_DAILY_0.CHANNEL_ID AND
PAGE_0.PAGE_ID = USAGE_DAILY_0.PAGE_ID AND
((USAGE_DAILY_0.CAL_DT>={d '2003-09-01'} And
USAGE_DAILY_0.CAL_DT<={d '2003-09-30'}) AND
(USAGE_DAILY_0.BRAND_ID=3) AND (USAGE_DAILY_0.CHANNEL_ID=49))" & _

"GROUP BY USAGE_DAILY_0.CAL_DT,BRAND_0.BRAND_ID,
BRAND_0.BRAND_NAME,CHANNEL_0.CHANNEL_ID,CHANNEL_0.CHANNEL_NAME,
PAGE_0.PAGE_ID,PAGE_0.PAGE_DESC,PAGE_0.PAGE_NAME,
USAGE_DAILY_0.ADSPACE_POSITION_ID,
USAGE_DAILY_0.CLUSTER_ID) as sum_min" & _

"GROUP BY CAL_DT,BRAND_NAME,CHANNEL_NAME,PAGE_ID,PAGE_DESC,PAGE_URL" & _
"ORDER BY CAL_DT,BRAND_NAME,CHANNEL_NAME,PAGE_DESC,PAGE_ID"

'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery = dbsGreenway.OpenRecordset(strSQL, dbOpenSnapshot)
intMaxCol = rstFromQuery.Fields.Count
intMaxRow = rstFromQuery.RecordCount

Set objXL = New Excel.Application
    With objXL
      .Visible = True
      Set objWkb = .Workbooks.Add
      Set objSht = objWkb.Worksheets(1)
      With objSht
        .Range(.Cells(1, 1), .Cells(intMaxRow, _
            intMaxCol)).CopyFromRecordset rstFromQuery
      End With
    End With

End Sub

When I run this code, I get the following error:
"Run-time error '3055': Not a valid file name"

The debugger is pointing at this line:
Set dbsGreenway = wspDefault.OpenDatabase(oldDbName)

Will this code work for a database that is on a network server, rather than on my local machine? So, rather than my path being "C:\Windows\etc"

it is "http://server.domain.comort #".

What do you think?

Thanks,
Richard

 

[Mark W.]

Is this inline view the subquery that was causing you so much grief in MSQuery?

...FROM (SELECT USAGE_DAILY_0.CAL_DT as CAL_DT,BRAND_0.BRAND_ID, 
trim(BRAND_0.BRAND_NAME) as BRAND_NAME,CHANNEL_0.CHANNEL_ID, 
trim(CHANNEL_0.CHANNEL_NAME) as CHANNEL_NAME, 
PAGE_0.PAGE_ID as PAGE_ID,trim(PAGE_0.PAGE_DESC) as PAGE_DESC, 
trim(PAGE_0.PAGE_NAME) as PAGE_URL, 
USAGE_DAILY_0.ADSPACE_POSITION_ID, 
USAGE_DAILY_0.CLUSTER_ID, 
Min(USAGE_DAILY_0.TOTAL_IMPRESSIONS) as IMPRESSIONS, 
Sum(USAGE_DAILY_0.TOTAL_RESPONSES) As CLICKS 
FROM BRAND BRAND_0, CHANNEL CHANNEL_0, PAGE PAGE_0, USAGE_DAILY 
USAGE_DAILY_0
WHERE BRAND_0.BRAND_ID = USAGE_DAILY_0.BRAND_ID AND 
CHANNEL_0.CHANNEL_ID = USAGE_DAILY_0.CHANNEL_ID AND 
PAGE_0.PAGE_ID = USAGE_DAILY_0.PAGE_ID AND 
((USAGE_DAILY_0.CAL_DT>={d '2003-09-01'} And 
USAGE_DAILY_0.CAL_DT<={d '2003-09-30'}) AND 
(USAGE_DAILY_0.BRAND_ID=3) AND (USAGE_DAILY_0.CHANNEL_ID=49))...

If so, why not incorporate these joins into your SELECT's WHERE clause and reference the tables directly in your FROM clause?

Better yet are you allowed to CREATE VIEWs?

BTW, are you analyzing click stream data?

 

[Tarheel]

Hi again Mark,

There are a couple of things that were giving me grief in MSQuery. The first is that once I added the subquery to cut down on my results, it can't display the query graphically. Since it can't display it graphically, it won't let me set parameters so that I can prompt for input. This isn't the only reason, either. Since I am trimming some fields, it can't display those graphically either, so I'm in the same situation.

I am no SQL expert, it took me a while to figure out how to write that query and make it work, and I had some help. If you have suggestions as to how I can improve my query, then I am all ears.

I do work for a major ISP, and this data is logged from our members' visits to a certain group of web pages.

Thanks again for the help!
Richard

 

[lord of the badgers]

i'm finding this loads useful.. (i use MSQuery stuff a lot) - i have a quick slightly O/T (maybe ) question:

I have a formula in my formula list called SQL.Request - dunno what add-in, er, added it (but i'll bet it's a standard one), has anyone used it? Perhaps it can do the above, I don't know...

 

[Mark W.]

Hi again Mark,

There are a couple of things that were giving me grief in MSQuery. The first is that once I added the subquery to cut down on my results, it can't display the query graphically. Since it can't display it graphically, it won't let me set parameters so that I can prompt for input...

Just a quick response... I'll come back to the rest when I have some more time. You code a parameter query as follows...

SELECT...
FROM...
WHERE table.field=?

The "?" is the place keeper for the parameter. The only thing that "[ text ]" does in the criteria section of the GUI is allow you to provide prompt, but that's unnecessary if you plan on obtaining the parameter's value from an Excel worksheet cell.

 

[Mark W.]

i'm finding this loads useful.. (i use MSQuery stuff a lot) - i have a quick slightly O/T (maybe ) question:

I have a formula in my formula list called SQL.Request - dunno what add-in, er, added it (but i'll bet it's a standard one), has anyone used it? Perhaps it can do the above, I don't know...

Have you reviewed the online Excel Help topic for "SQL.REQUEST worksheet function"?

 

[lord of the badgers]

nope.. where dat den?