asked on

Pass-through query to make-table query at the server

Hi there!

I have an MS Access 2003 database at the (not-my-own) server where Access is not installed (nor it will be).

I would like to run a make-table query (very "complex") included in it, via (ASP) code, to create a table in the same Access database that is linked with many other "common queries" (I need this because of time-of-processing issues).

The process should be: the user presses a button in the website, the ASP code deletes the "table X" (it already works) , the code triggers the make-table query and the (potentially different, updated) "table X" is (re)created at the database and instantaneously used for the "common queries" related to that...

In another EE post, I was suggested to "convert my macro into a store procedure that can be executed via ADO" and looking a bit more on the matter, it seems to me that a pass-through query from the Access database to itself could do the magic. Is is so?

In the positive, could you please help me to fill the "Select Data Source" Access dialog? I have no idea what should I put in "Data Sources" or in "DSN name", for instance.

In the negative (that is, if I should not use the pass-through query method) , how could I proceed? (Provide me with some instructive links, for instance.)

Thanks a lot,
fskilnik.

Leigh Purvis

What is your backend database that the passthrough would connect to?

And are you wanting the new table to be created in the Access (Jet) backend - rather than the server?

fskilnik

ASKER

Hello, LPurvis!

Glad I´m "at your expert hands". :)

> What is your backend database that the passthrough would connect to?

The very same database where it will be created. I think the passthrough would be suitable because I read this would make the SQL statement (got from one of its queries) executable on the back-end database server.

> And are you wanting the new table to be created in the Access (Jet) backend - rather than the server?

Nope. I would like the new table to be created in the exactly same database at the server. Then I can use recordsets to get info from this new table indirectly, that is, from some queries that also use this table as part of the info they are able to manage.

You could ask "why then all this mess", right ? Why you don´t simply create other queries based on the "basic query" , without using this "basic query" to create a table that will be used by the other queries? The answer is: because "queries using this basic query" would be great because they would be always "updated" but... at a very demanding timing restriction (say 6min to run the "final query" based on consecutive ones...) and with my "queries based on this newly created table" approach, I could run the "final query" in (say) 1.2 min... (Yes, I will have to run it to have the info updated but... this is not a restriction for the project...)

I hope you got the picture.

Fábio.

fskilnik

ASKER

Sorry, LPurvis. Now I re-read your question and I must say I got the "the Access (Jet) backend - rather than the server" wrong. I don´t know what you mean by "Access (Jet) backend" , I simply understand the new table must be created (as I wrote before) at the same Access database presented at the server. All these "activities" should be made "internally", in the sense "internally at the server"... I am sorry I know too little to be more especific.

fskilnik

ASKER

Hello, Mr. Purvis.

I tried the code below and got the following error message (also below). Could you please tell me if I am in the right track and, if so, do you have any idea why the error in SQL? (The same code is working when I use it at my local database...)

Thank you,
Fábio.

----------------------- Error Message ------------------

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/PATH and PageName.asp, line 46 <<------------------- This line is the sql one... (that works inside Access)

strSQL = "SELECT qryEqtyTransactions .... ;" (see below)

--------------------- Code -------------------------------

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
objConnection.ConnectionString = "PATH\DatabaseName.mdb"
objConnection.open

'THIS WILL DELETE THE TABLE PRIOR TO RECREATING IT '=========================================
SQL = "DELETE FROM tblTesting"
objConnection.execute SQL
'=========================================

strSQL = "SELECT qryEqtyTransactions.StockID, qryEqtyTransactions.EqtyTransID, qryConnTickersEtc.Symbol, qryEqtyTransactions.TransDate, qryEqtyTransactions.PriceShare, qryEqtyTransactions.SgnQtyShares, IIf([SgnQtyShares]>0,[SgnQtyShares],0) AS NumbShares, (Select SUM ( [qryEqtyTransactions_aux]![SgnQtyShares] ) FROM qryEqtyTransactions_aux
Where (([qryEqtyTransactions_aux]![SgnQtyShares] >0) AND ( [qryEqtyTransactions_aux]![StockID] = [qryEqtyTransactions]![StockID] ) AND (qryEqtyTransactions_aux.TransDate <= qryEqtyTransactions.TransDate))) AS AccumShares, (Select SUM ( [qryEqtyTransactions_aux]![SgnQtyShares] ) FROM qryEqtyTransactions_aux
Where (([qryEqtyTransactions_aux]![SgnQtyShares] >0) AND ( [qryEqtyTransactions_aux]![StockID] = [qryEqtyTransactions]![StockID] ) )) AS FinalAccumShares, [AccumShares]-[NumbShares] AS PrevAccumShares, (Select SUM ( [qryEqtyTransactions_aux]![SgnQtyShares] ) FROM qryEqtyTransactions_aux
Where (([qryEqtyTransactions_aux]![SgnQtyShares] < 0) AND ( [qryEqtyTransactions_aux]![StockID] = [qryEqtyTransactions]![StockID] ) AND (qryEqtyTransactions_aux.TransDate <= qryEqtyTransactions.TransDate))) AS AccumNegSharesAux, IIf(IsNull([AccumNegSharesAux]),0,[AccumNegSharesAux]) AS AccumNegShares, (Select SUM ( [qryEqtyTransactions_aux]![SgnQtyShares] ) FROM qryEqtyTransactions_aux
Where (([qryEqtyTransactions_aux]![SgnQtyShares] < 0) AND ( [qryEqtyTransactions_aux]![StockID] = [qryEqtyTransactions]![StockID] ) )) AS TotalAccumNegShares, (Select Min(qryEqtyTransactions_auxNeg.TransDate) FROM qryEqtyTransactions_auxNeg
WHERE ([qryEqtyTransactions_auxNeg]![StockID] = qryEqtyTransactions.StockID)) AS FirstNegDate, (Select Max(qryEqtyTransactions_auxPosit.TransDate) FROM qryEqtyTransactions_auxPosit WHERE ([qryEqtyTransactions_auxPosit]![StockID] = [qryEqtyTransactions]![StockID])) AS LastPositDate, (Select Max(qryEqtyTransactions_auxNeg.TransDate) FROM qryEqtyTransactions_auxNeg WHERE ([qryEqtyTransactions_auxNeg]![StockID] = [qryEqtyTransactions]![StockID])) AS LastNegDate, IIf([LastPositDate]>[LastNegDate] Or IsNull([LastNegDate]),[LastPositDate],[LastNegDate]) AS LastGenDate, (Select Max(qryEqtyTransactions_auxPosit.TransDate) FROM qryEqtyTransactions_auxPosit WHERE ([qryEqtyTransactions_auxPosit]![StockID] = [qryEqtyTransactions]![StockID]) ) AS LastBuy, IIf([TransDate]=[LastGenDate],[AccumShares]+[AccumNegShares],0) AS LastOnHand, IIf((qryEqtyTransactions!TransDate=[LastBuy]) And ([LastNegDate]>[LastBuy]),1,0) AS NegIsLast, Date() AS DateOfRunning, Time() AS TimeOfRunning INTO tblTesting
FROM qryEqtyTransactions LEFT JOIN qryConnTickersEtc ON qryEqtyTransactions.StockID = qryConnTickersEtc.StockID
GROUP BY qryEqtyTransactions.StockID, qryEqtyTransactions.EqtyTransID, qryConnTickersEtc.Symbol, qryEqtyTransactions.TransDate, qryEqtyTransactions.PriceShare, qryEqtyTransactions.SgnQtyShares
ORDER BY qryEqtyTransactions.StockID, qryEqtyTransactions.TransDate;"

objConnection.execute(strSQL)
objConnection.close

%>

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

And as a general comment (based on my rough understanding of what's going on) you're wanting to execute a Maketable query in an Access backend.
It's not linked to, say SQL Server or Oracle - you're only in Access?

In which case there is no possibility (or possible use) for a Passthrough query.

You can execute a saved Update query without problem - no need to for further complexity.
However there must be no expressions within that can't be evaluated (no form references).

fskilnik

ASKER

Great, Mr. Purvis! It is 100% working! Cheers!!

> wanting to execute a Maketable query in an Access backend.
> You can execute a saved Update query without problem - no need to for further complexity.
> However there must be no expressions within that can't be evaluated (no form references).

That´s it. You got the whole picture. It´s done!
(We don´t use Access forms because every hard-input is made by code via web.)

> It's not linked to, say SQL Server or Oracle - you're only in Access?

Yes, only Access! We are only 4 people at the office and most of the "hard programming stuff" is related to internal use, only. On the other hand, I will start studying some SQL Server 2005, just in case.

I will leave below comments on your suggestions, to help potential readers with similar difficulties.

Thanks a lot and see you in my next difficulties, I hope! :)
Fábio.

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

> Your Delete statement would only empty that table of records - not remove it.
> And any maketable would then fail...

Perfect! I could have imagined that... but I didn´t and I would not, probably!
I changed it and it worked after I corrected the sql statement (see below).

> objConnection.ConnectionString = "Data Source=PATH\DatabaseName.mdb"

I left the way it was (without the "Data Source" words) and it worked ok.

> ----------------------- Error Message ------------------
Microsoft VBScript compilation error '800a0409'
Unterminated string constant

The problem here was a < " > inside the SQL statement, created by the Access query itself, I had to concatenate like: " ... " & "...." to get it work and I realized this was the problem only when I tried to put the whole SQL as a comment < ' > and saw that parts of it where still "green" , not "gray"! I guess this is a good way of checking problems related to < " >´s positions!

Leigh Purvis

Welcome - glad you're sorted.