ODBC query (1 Viewer)

[Harry_38]

I have some very slow reports in Access program with mysql backend; and I am trying to improve that by building pass-through-queries (PTQ). I got a few of these queries working and the speed is ok; but I have the following questions:
1. Each time I activate a PTQ, I need to log on thru the ODBC connector, although I have connected already wuith the tables. This is troublesome; is there a way around this?
2. Is it possible to base a form or report to a PTQ, and how do you go about this?
Thanks for the help.

 

[Dynaweb]

Hi Harry,

I am working along the same path as you. Improving the performance of Access 03 queries but moving the processing to the back end DB.

Have you tried saving the authentication information within the ODBC connection string when you create the PTQ?

Essentially when you have the PTQ open in SQL View, right click the top of the window and select "Properties" from there you should be able to configure the "ODBC Connect Str" field. Which is just like you would normally do when adding an ODBC database!

Let me know how that works out for you...

 

[Harry_38]

I let the problem rest for some time, sorry for not replying earlier.
Dynaweb: I've been looking for "properties'" but I am unable to find them. Mind you I am working with Access2010.
A second question I have is the sql language. I am able to strugle my way through VBA but would have to start from scratch with Mysql. Any advice on a good tutorial; or could anybody helkp me converting a VBA query to a PTQ.
E.g. the following Access query (Dutch):
SELECT Algemeen.Vernaam, Debiteuren.*, Personen.*, [C-Status].*, Cursussen.CNaam, Personen_2.Vnaam AS Docent, Personen_1.Vnaam AS Begeleider, Cursussen.Eindetijd, Cursussen.Aanvangstijd, Cursussen.Aanvangsdatum, Debiteuren.CursusID, Cursussen.Min_aantal_cursisten, Cursussen.Max_aantal_cursisten, Cursussen.Breakeven, Cursussen.Doorgang, Cursussen.Besluit_doorgang, Cursussen.Inschrijfformulier_tonen, Algemeen.Verenigingsjaar
FROM Algemeen, Personen INNER JOIN (([C-Status] INNER JOIN (Personen AS Personen_2 INNER JOIN (Personen AS Personen_1 INNER JOIN Cursussen ON Personen_1.Persoonnr = Cursussen.BegeleiderID) ON Personen_2.Persoonnr = Cursussen.DocentID) ON [C-Status].[C-StatusID] = Cursussen.C_StatusID) INNER JOIN Debiteuren ON Cursussen.CursusNr = Debiteuren.CursusNr) ON Personen.Persoonnr = Debiteuren.Persoonnummer
WHERE (((Debiteuren.CursusID)>10) AND ((Debiteuren.Deelnemer)="Ja") AND ((Debiteuren.[Seizoen-id])=GetParameterWaarde('strActiefSeizoen')));

 

[Rx_]

A simple example:
create the SQL statement using the QBE grid.
Add a Parameter in the QBE grid such as USER_ID
Execute the query - to test for desired results.
View the QBE in the SQL text view.
Create a string variable to rebuild the SQL statement.
Add the Select statement on one line, the From statement on another, ...
The 'A' (for Active) in the QBE will be "A" - change them to single quotes.
Note how the User_ID was assigned to a variable of type string. These can be added in.

30 strsql = "SELECT tbl_Users.User_ID, tbl_Users.Activity, tbl_Users.Permit "
40 strsql = strsql & "FROM tbl_Users "
50 strsql = strsql & "WHERE (((tbl_Users.User_ID)= '" & User_ID & "') AND ((tbl_Users.Activity)='A') AND ((tbl_Users.Permit)='A'));"

 

[cjman]

In Access 2010 inside design on the query, press the design button. then press the property sheet button, then in the properties on ODBC connect str press the button with the ... You can then build your connection string for the pass through query easily.