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.
fskilnikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
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?
0
fskilnikAuthor Commented:

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.


0
fskilnikAuthor Commented:

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.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fskilnikAuthor Commented:

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

%>
0
Leigh PurvisDatabase DeveloperCommented:
I'd have something more like

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

and then

    SQL = "DROP TABLE tblTesting"
    objConnection.execute SQL

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
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).
0
fskilnikAuthor Commented:

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!  
0
Leigh PurvisDatabase DeveloperCommented:
Welcome - glad you're sorted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.