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

 

[Tarheel]

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.

Yes, that's how I wrote my SQL statement, placing "?" in the place where I will pull values in for my criteria. However, when you click OK to set the query, it responds with an error... "Parameters are not allowed in queries that can't be displayed graphically."

It will not let you proceed to save the query until you remove the "?" from the SQL statement. This isn't a matter of me not knowing how to set up a parameter, it is a matter of MSQuery not allowing me to do so.

-- Richard

 

[Tarheel]

Hi everyone...

After googling and looking through some of the Microsoft Knowledge base... I have managed to put together some code that works, well almost.

Here's my code:

Sub dbconnect3()
Dim wkrJet As DAO.Workspace, wrkODBC As DAO.Workspace
Dim conODBCDirect As DAO.Connection
Dim rsODBCDirect As DAO.Recordset
Dim strConn As String

strConn = "ODBC;DATABASE=dbname;UID=username;PWD=password;DSN=dbname;LOGINTIMEOUT=50;"

Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set conODBCDirect = wrkODBC.OpenConnection("", , , strConn)
SqlStr = "SELECT CHANNEL_ID, CHANNEL_NAME FROM CHANNEL"

Set rsODBCDirect = conODBCDirect.OpenRecordset(SqlStr, dbOpenDynamic)

intMaxCol = rsODBCDirect.Fields.Count
intMaxRow = rsODBCDirect.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 rsODBCDirect
      End With
    End With

'close recordset
rsODBCDirect.Close
wrkODBC.Close
Set rsODBCDirect = Nothing
Set wrkODBC = Nothing
Set conODBCDirect = Nothing
End Sub

My problem is that for this simple query, it does not return all of the values. There are over 10,000 channels listed in that database (all unique), but I only get a result set of 100 channels.

Also, if I try to put in my query that this post originally started about, it waits for a few seconds and then gives me an error message: "Run-time error '3669': Execution Cancelled." I am assuming this is because it is timing out... but I don't know where to change the default timeout value? The query runs if I put it into MSQuery, so I know it's not a syntax problem.

Can anyone help? I am making baby steps, I know we are going to get there!!!