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

MD5 in SQL

Is there any MD5() type of function SQL Server to encrypt
a string?
Thanks,
LixinNothing built in. Take a look at wiHash and wiCrypt from www.whamware.com.
Tom
"Lixin Fan" wrote:
> Is there any MD5() type of function SQL Server to encrypt
> a string?
> Thanks,
> Lixin|||Tom,
Thanks a lot.
Lixin
>--Original Message--
>Nothing built in. Take a look at wiHash and wiCrypt from
www.whamware.com.
>Tom
>"Lixin Fan" wrote:
>> Is there any MD5() type of function SQL Server to
encrypt
>> a string?
>> Thanks,
>> Lixin
>
>.
>

MD5 function ?

Hi,
I am quite new to T-SQL and I'm facing a problem: how can I insert a
MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
call the md5 function, but in SQL Server I failed to find it. Does MD5
function exist in T-SQL ? How can I solve this problem?
Thanks in advance!
AndreiCandreic wrote:
> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
In SQL Server 2005:
INSERT INTO tbl (col)
SELECT HashBytes('MD5', 'foobar') ;
In SQL Server 2000 you'll have to create your own. .NET has classes for
encryption and hashing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can exttended stored procedure for MD5 in SQL Server 2000.
You can find a example below url.
http://www.codeproject.com/database/xp_md5.asp
"andreic"?? ??? ??:

> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
>|||If this is a secured application, you might consider using SHA-style hashes,
since MD5 is not considered secure anymore. An SHA-256/384/512 hash
function is available at
http://www.sqlservercentral.com/col...oolkitpart4.asp
"andreic" <andrei.croitoriu@.gmail.com> wrote in message
news:1150048549.960214.268210@.i40g2000cwc.googlegroups.com...
> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
>

May (or CAN) I use triggers ?

I need to call a procedure/function in VB when my DB is modified
with insert, update or delete. I know that its possible to do
something called Trigger, put how can my Triggers (in SQL Server or
MSDE) call a procedure in VB ?

Just to understand, this database catch information of users
connected to a support chat and I need to know when this user get
logged in (thru the rows of my table). When I notice that a new user
have logged in, I need to support him.

Thanks.You can try to create extended stored procedure but I think it is not a good idea to use something like this in triggers (performance). Trigger could save info about new users in special table and VB could monitor this table.|||but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?|||Originally posted by ralf_davi
but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?

Select from table is much faster (index, etc.) than running something no-native from trigger.|||What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman|||Originally posted by blindman
What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman

Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?|||All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman|||Originally posted by blindman
All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman

How can I work with stored procedures to notify my VB application ?
Can you give me a sign ?|||Hmmm...I'm not a VB expert, and a lot probably depends upon the particulars of your application, but some of the methods SQL Server can use to communicate with other applications are:

1) Writing files.
2) Calling xp_cmdshell to issue operating system commands.
3) Calling xp_logevent to write to the NT Event Log.
4) Creating your own extended stored procedure.
5) xp_Sendmail

blindman|||if you're carrying a pager you'd probably be better off calling an sp from the ASP page that can page you with an info pertinent to the newly logged on user. there are 2 (that I know of) methods that do not involve SQLMail, - xp_smtp_sendmail and cdo-based sendmail. i don't remember the web-sites, but google will find them.|||Originally posted by ralf_davi
Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?

There are so many ways to alert you through ASP page. You can call your vb dll, send an email directly to you, etc. I would not suggest you to use trigger in this case.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32

i m getting this error when i m executing a stored procedure

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

can anyone help me out what exactly the problemIt looks like you call stack exceeds maximum allowable level. It means that you run a stored procedure that executes another stored procedure that executes yet another... and so on.