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 cant find an example in the help file :(

Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=[Forms]![frmMainQuote]![QuoteLoadSize]));

Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@.Forms___frmMainQuote___CboLorr varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=@.Forms___frmMainQuote_ __CboLorr)))

Any comment gratefully receivedUp-sizing problems

I still havent 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:--

[Forms]![frmSeeTown]![cboSelectCounty]

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 Im 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 (Dont 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.. :||||Found this just now!!!

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=AxxessXP&Number=137340&Forum=AxxessXP&Words=access%20project&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=allposts&Main=137340&Search=true#Post137340

So I'm outa the woods at last (Famous last words) :) :)

Whos a happy bunny then!!!|||After several days of head scratching, Ive 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.sql

MDAC error

I have upgraded MDAC to vers 2.7 SP1(winXP).

On checking via the MDAC Cmponent Checker that one file has an error or maybe currupt.

I have tried to reinstall with no joy. The error description is as follows:

Item Name,Item Type,ErrorString,Status
oledb32.dll,File,Field 'FileVersion' mismatch: Expect '2.71.9030.0 built by: Lab06_N(dagbuild)' Got '2.71.9031.4 built by: Lab06_N(dagbuild)',Error

Could you shed any light on what might be the problem is?

Cheers PetermJust uninstall everything related to MDAC and remove registry keys, reboot the box.

For information on MDAC issues refer to this Article (http://www.8wire.com/articles/index.asp?AID=2318) and this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;324520).

Make sure to install latest MDAC and SP from MS.

MDAC 2.8 seems cause SQL backup fail, can anyone send us a MDAC 2.8 latest hotfix ?

Dear all,
After our team has upgraded backup software of one server to "Veritas
BackupExec 9.1",
We found our routine SQL backup jobs (as part of our maintenance plan) fail
on it.
From our investigation, we suspect it is caused by the MDAC 2.8 installed
with BackupExec 9.1
The problem seems already documented in microsoft support article :
http://support.microsoft.com/defaul...kb;en-us;827452
However, as our company has not join MS support service, I have no formal
channel
to receive hot fix for the MDAC.
(Actually the article suggest to update dbnmpntw.dll to a version after
09-17-2003 (2000.85.1022.42),
but it is already 2 years but still no MDAC 2.8 SP1 out there, which is
quite disappointed)
Can anyone give me a hand ?
Thanks for anyone's kind help in advance ... EricSorry, I forget to mention my environment.
Our server is running Win2000 Std Server (English) with SP3.
"Eric Fung" <Pls type eric.fung plus @.hld.com> bl
news:uQ$IZf0PFHA.244@.TK2MSFTNGP12.phx.gbl g...
> Dear all,
> After our team has upgraded backup software of one server to "Veritas
> BackupExec 9.1",
> We found our routine SQL backup jobs (as part of our maintenance plan)
fail
> on it.
> From our investigation, we suspect it is caused by the MDAC 2.8 installed
> with BackupExec 9.1
> The problem seems already documented in microsoft support article :
> http://support.microsoft.com/defaul...kb;en-us;827452
> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
> (Actually the article suggest to update dbnmpntw.dll to a version after
> 09-17-2003 (2000.85.1022.42),
> but it is already 2 years but still no MDAC 2.8 SP1 out there, which
is
> quite disappointed)
> Can anyone give me a hand ?
> Thanks for anyone's kind help in advance ... Eric
>|||Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:

> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
You don't need to "join" the support service. Just ring the service number
for your country, explain the issue, and they send you a location to
download the fix from and a password to access the download. So long as it's
an issue covered by the article that requires a file(s) from MS support they
won't charge.
Dan|||Dear Daniel,
Thanks a lot for your advice, I will try.
Eric
"Daniel Crichton" <msnews@.worldofspack.co.uk> bl
news:udnoPP3PFHA.2584@.TK2MSFTNGP15.phx.gbl g...
> Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:
>
formal[vbcol=seagreen]
> You don't need to "join" the support service. Just ring the service number
> for your country, explain the issue, and they send you a location to
> download the fix from and a password to access the download. So long as
it's
> an issue covered by the article that requires a file(s) from MS support
they
> won't charge.
> Dan
>sql

MDAC 2.8 seems cause SQL backup fail, can anyone send us a MDAC 2.8 latest hotfix ?

Dear all,
After our team has upgraded backup software of one server to "Veritas
BackupExec 9.1",
We found our routine SQL backup jobs (as part of our maintenance plan) fail
on it.
From our investigation, we suspect it is caused by the MDAC 2.8 installed
with BackupExec 9.1
The problem seems already documented in microsoft support article :
http://support.microsoft.com/default...b;en-us;827452
However, as our company has not join MS support service, I have no formal
channel
to receive hot fix for the MDAC.
(Actually the article suggest to update dbnmpntw.dll to a version after
09-17-2003 (2000.85.1022.42),
but it is already 2 years but still no MDAC 2.8 SP1 out there, which is
quite disappointed)
Can anyone give me a hand ?
Thanks for anyone's kind help in advance ... Eric
Sorry, I forget to mention my environment.
Our server is running Win2000 Std Server (English) with SP3.
"Eric Fung" <Pls type eric.fung plus @.hld.com> bl
news:uQ$IZf0PFHA.244@.TK2MSFTNGP12.phx.gbl g...
> Dear all,
> After our team has upgraded backup software of one server to "Veritas
> BackupExec 9.1",
> We found our routine SQL backup jobs (as part of our maintenance plan)
fail
> on it.
> From our investigation, we suspect it is caused by the MDAC 2.8 installed
> with BackupExec 9.1
> The problem seems already documented in microsoft support article :
> http://support.microsoft.com/default...b;en-us;827452
> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
> (Actually the article suggest to update dbnmpntw.dll to a version after
> 09-17-2003 (2000.85.1022.42),
> but it is already 2 years but still no MDAC 2.8 SP1 out there, which
is
> quite disappointed)
> Can anyone give me a hand ?
> Thanks for anyone's kind help in advance ... Eric
>
|||Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:

> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
You don't need to "join" the support service. Just ring the service number
for your country, explain the issue, and they send you a location to
download the fix from and a password to access the download. So long as it's
an issue covered by the article that requires a file(s) from MS support they
won't charge.
Dan
|||Dear Daniel,
Thanks a lot for your advice, I will try.
Eric
"Daniel Crichton" <msnews@.worldofspack.co.uk> bl
news:udnoPP3PFHA.2584@.TK2MSFTNGP15.phx.gbl g...[vbcol=seagreen]
> Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:
formal
> You don't need to "join" the support service. Just ring the service number
> for your country, explain the issue, and they send you a location to
> download the fix from and a password to access the download. So long as
it's
> an issue covered by the article that requires a file(s) from MS support
they
> won't charge.
> Dan
>

MDAC 2.8 seems cause SQL backup fail, can anyone send us a MDAC 2.8 latest hotfix ?

Dear all,
After our team has upgraded backup software of one server to "Veritas
BackupExec 9.1",
We found our routine SQL backup jobs (as part of our maintenance plan) fail
on it.
From our investigation, we suspect it is caused by the MDAC 2.8 installed
with BackupExec 9.1
The problem seems already documented in microsoft support article :
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
However, as our company has not join MS support service, I have no formal
channel
to receive hot fix for the MDAC.
(Actually the article suggest to update dbnmpntw.dll to a version after
09-17-2003 (2000.85.1022.42),
but it is already 2 years but still no MDAC 2.8 SP1 out there, which is
quite disappointed)
Can anyone give me a hand ?
Thanks for anyone's kind help in advance ... EricSorry, I forget to mention my environment.
Our server is running Win2000 Std Server (English) with SP3.
"Eric Fung" <Pls type eric.fung plus @.hld.com> ¦b¶l¥ó
news:uQ$IZf0PFHA.244@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> Dear all,
> After our team has upgraded backup software of one server to "Veritas
> BackupExec 9.1",
> We found our routine SQL backup jobs (as part of our maintenance plan)
fail
> on it.
> From our investigation, we suspect it is caused by the MDAC 2.8 installed
> with BackupExec 9.1
> The problem seems already documented in microsoft support article :
> http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
> (Actually the article suggest to update dbnmpntw.dll to a version after
> 09-17-2003 (2000.85.1022.42),
> but it is already 2 years but still no MDAC 2.8 SP1 out there, which
is
> quite disappointed)
> Can anyone give me a hand ?
> Thanks for anyone's kind help in advance ... Eric
>|||Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:
> However, as our company has not join MS support service, I have no formal
> channel
> to receive hot fix for the MDAC.
You don't need to "join" the support service. Just ring the service number
for your country, explain the issue, and they send you a location to
download the fix from and a password to access the download. So long as it's
an issue covered by the article that requires a file(s) from MS support they
won't charge.
Dan|||Dear Daniel,
Thanks a lot for your advice, I will try.
Eric
"Daniel Crichton" <msnews@.worldofspack.co.uk> ¦b¶l¥ó
news:udnoPP3PFHA.2584@.TK2MSFTNGP15.phx.gbl ¤¤¼¶¼g...
> Eric wrote on Tue, 12 Apr 2005 18:10:10 +0800:
> > However, as our company has not join MS support service, I have no
formal
> > channel
> > to receive hot fix for the MDAC.
> You don't need to "join" the support service. Just ring the service number
> for your country, explain the issue, and they send you a location to
> download the fix from and a password to access the download. So long as
it's
> an issue covered by the article that requires a file(s) from MS support
they
> won't charge.
> Dan
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA Poseur
Hmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
--
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>