Unanswered: mdb query to adp function conversion failed
Good evening everybody! This is my first post here, necessary because I have recently upgraded from Access *.MDB to Access *.ADP
I have a problem with a query that does not appear to have converted correctly. Below is the SQL of the *.mdb and the *.adp. I would be very grateful if somebody could indicate the correct format for the ADP code. I can’t find an example in the help file
Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@Forms___frmMainQuote___CboLorr varchar (255)
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
WHERE (((tlkpLoadSize.LoadSizeID)=@Forms___frmMainQuote_ __CboLorr)))
I still haven’t found the correct syntax for a query in an adp file. However I did find two more upsizing problems for which I have found a cure, these can be seen in the attached Access 2000 *.mdb file. Run it in mdb then run the upsize wizard and note the problems.
Change “True” to 1 and “False” to 0
The first upsizing problem I had was that in an *.mdb file you can use “True” or “False” in an SQL statement. However when you upsize you need to change the “True” to 1, and the “False” to 0. It might pay, when you are designing your database to think about using 1 and 0 to indicate true or false situations.
Using forms collection can cause a problem
The other problem related nicely to the query problem mentioned above. I found that some code in my form ”frmSeeTown” did not work because the SQL contained a reference to a combo box via the forms collection in the form of:--
This relates to my query problem because the query with the problem also contained a reference to a combo box through the forms collection. Therefore I am reasonably sure that you cannot reference a combo box this way in an SQL string in an Access Project file. However I’m right at the bottom of the learning curve so please educate me.
Now down to the problem!
If you look at the attached *.mdb file you will see that there are three functions behind the form ”frmSeeTown” they are called:-
fBuildStr1() Assigns data from the combo box with “me.cboSelectCounty”
fBuildStr2() Assigns data using the forms collection (Don’t work in *.asp)
fBuildStr3() Assigns data from a variable.
To view the effect of the different methods change the called function name from fBuildStr3 to fBuildStr2
The following NOTES have been extracted from the form “frmSeeTown” module……
'Both of these functions "fBuildStr1" and "fBuildStr2" work OK in an mdb, but "fBuildStr2" won't work in an adp.
'So there are 4 possible options ----------------
'1) It maybe I'm using the wrong syntax? --- can't find anything in the help??? So I think it's something else.
'2) Reference the combo box control with the Me. function. --- Doing it this way could be a problem with sub-forms
'3) Assign the combo box value to variable and use that in the SQL --- this has got to be the way to do it
'4) Something else, embarrassingly simple that I don't know about yet Hell that's why I'm posting...
'Since writing the above, I have now added a further function fBuildStr3 which assigns the combo box value to a variable and then uses that variable in the SQL code. This appears to work OK….. :|
After several days of head scratching, I’ve discovered that my original suspicion that there was a problem with the syntax was correct. If you look at the two lines of code below taken from an SQL statement both line 1) and line 2) work in Access mdb format, but only line 2) works in Access adb format.
1) "WHERE (((tlkpUkTowns.TownOnlyCounty) = [Forms]![frmSeeTown]![cboSelectCounty] )) ORDER BY tlkpTowns.TownTown"
2) "WHERE (((tlkpUkTowns.TownOnlyCounty) = " & Forms![frmSeeTown].cboSelectCounty & ")) ORDER BY tlkpTowns.TownTown"
As you can see the SQL in line one is incorrectly formatted. It appears that the SQL engine for an mdb file is more forgiving, and corrects minor mistakes in the SQL string automatically. So I am inferring from this that an adp file uses a different engine to handle the SQL statements. This would make sense, as there are differences in the SQL between the two access formats. However I say again I am very low down on the learning curve and would welcome any input.