Hi Forum,

Could someone point me in the right direction here, I need to understand this once and for all.
I want to run a series of Access queries from Excel.

I have created an Access db with about 12 stored queries which produces what I need, in Access.

The first one takes a command line parameter (a customer number) this is used to return customer data and as the queries are run one the last one produces an table with the customers prices which I need in Excel.

I am using the following for the first query, this passes the customer number, it works fine and I get my data into Excel. It does not however save the query in Access with the changed customer number, which I am guessing is where the problem lies.

I used the same construction to run the last query, thinking that this would run the intermediate queries and pass the results I needed to Excel - it did not work as I expected (but I guess you know that ;-)

What do I need to do to achieve that?
Do I need to explicitly run all the queries from Excel, if so how?
Or do I just need to use a method which saves the first query and then run the last one?

Thanks for your consideration ..... Nick

Here is the Excel Vba.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Set ShDest = Sheets("Tabelle2")

sSQL = "SELECT dbo_ADRESSEN.KDNR, dbo_ADRESSEN.ADRNR, dbo_ADRESSEN.KURZNAME, dbo_ADRESSEN.VORNAME, dbo_ADRESSEN.NAME, dbo_ADRESSEN.NAME2, dbo_ADRESSEN.NAME3, dbo_ADRESSEN.STRASSE, dbo_ADRESSEN.PLZ, dbo_ADRESSEN.ORT, dbo_ADRESSEN.LAENDERKZ, dbo_ADRKUNDEN.KDGRUPPE, dbo_ADRKUNDEN.PREISKZ, dbo_ADRKUNDEN.VERBAND, dbo_ADRLB.BEZEICHNUNG AS LB, dbo_ADRZB.KURZBEZEICHNUNG AS ZB " & _
"FROM ((dbo_ADRESSEN INNER JOIN dbo_ADRKUNDEN ON dbo_ADRESSEN.ROWADRESSEN=dbo_ADRKUNDEN.ROWADRESSEN) INNER JOIN dbo_ADRLB ON dbo_ADRKUNDEN.LBNR=dbo_ADRLB.LBNUMMER) INNER JOIN dbo_ADRZB ON dbo_ADRKUNDEN.ZBNR=dbo_ADRZB.ZBNUMMER " & _
"WHERE (((dbo_ADRESSEN.KDNR)=" & KdNr & "));"

Set cnn = New ADODB.Connection
'MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
MyConn = "\\STATION6\PasstProReloaded\Excel.mdb"
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdText

'clear existing data on the sheet
ShDest.Activate
'Range("A1").CurrentRegion.Offset(1, 0).ClearContents
Cells.Select
Selection.ClearContents
'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset rst

' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

-----------------------------------------------------------------------------------------------------------------------------------------------------

This

I'm not sure I can answer your queston properly as I'm not really sure what you are asking for.

So this is just a guess at what you are saying...(I shall only use 3 queries , not 12)

If you have

qry1:
Select * from table

and then
qry2:
Select something form qry1

and then

qry3:
Select somethingelse into mytable from qry2

then the only query YOU run is qry3. YOU don't run qry1 nor qry2 at any stage of this process. Access takes care of the lower level queries. There is no explicit storage of results except for the final query.

The impression i have got from your question is that you are trying to run qry1 and then qry2 and so on.

Thanks for the quick reply!

you wrote: then the only query YOU run is qry3. YOU don't run qry1 nor qry2 at any stage of this process. Access takes care of the lower level queries. There is no explicit storage of results except for the final query.


That is more or less how I understand it too, (I have to run the first Query as shown, it passes the customer number and the last one which returns the data) but I am not getting the results I expected.
As I said the Excel Vba as shown (the first query which passes the customer number) works, but-- the sql passed is not stored in the Access query in the db, therefore the queries that build upon the first one use the customer data from the original stored query.

This brings me to the conclusion that I have to either somehow save the first query or use a different method from Excel to achieve my aim.

I do hope I have explained this well enough!

Kind regards.... Nick

I can't write the code for you, but you might try using a Global Variable in the Access database to hold the value being passed to the database. You could then develop a code module in the access database that ran your queries for you.

You would need to develop code to set the global variable to the value being returned by the first query, then the other queries could use the global variable. You might probably will need to develop a function to return the value of the global variable to the queries.

Then in the code module in the Excel spreadsheet, just open the database as you are already doing, and run the code in the code module.

If you are concerned about the first query maintaining the variable that it was prompted for, look into using a query def. This will recreate the query each time you pass a variable. I think that we need a more clear understanding of what you are doing to be of more help.

Thanks for the tip!
Querydef was the way to do it.

The world is as it should be again! (until the next time ;-)