Please convert access query into equivalent stored procedure

[shovan mohanty]

Hi ,

Can anybody convert below access query into equivalent stored procedure.
Also please advice in the below query,user defined function of VBA i.e anneeSelection4 1510() has been used in the .MDB file(access project).Now how this can be implemented in the equivalent .ADP(client/server architechture)p roject because obviously anneeSelection4 1510() function will not be recognised by the sql server." (t_activity_mon th.fy)=anneeSel ection41510()) ".
Hope,you will help me out in this.

SELECT t_customer.code _customer, t_customer.name _customer, t_activity_bran d1.no_activity AS Expr1, t_product.code_ product, t_product.produ ct_name, [price_off_amoun t1]*[nb_std_bt_plann ed1] AS mnt1, [price_off_amoun t2]*[nb_std_bt_plann ed2] AS mnt2, [price_off_amoun t3]*[nb_std_bt_plann ed3] AS mnt3, nz([mnt1],0)+nz([mnt2],0)+nz([mnt3],0) AS tot_mnt_mo, t_activity_bran d1.price_off_cu stomer_invoice AS Expr2, t_activity_bran d1.price_off_ac tive_invoice AS Expr3, t_activity.no_a ctivity_month, t_activity_mont h.fy, t_activity_bran d1.activity_bra nd_cancelled AS Expr4, t_activity.acti vity_cancelled
FROM t_product, t_activity_bran d1, t_activity_mont h INNER JOIN (t_customer INNER JOIN t_activity ON t_customer.code _customer = t_activity.code _customer) ON t_activity_mont h.no_activity_m onth = t_activity.no_a ctivity_month
WHERE (((t_activity_m onth.fy)=anneeS election41510() ) AND ((t_activity.ac tivity_cancelle d)=False Or (t_activity.act ivity_cancelled ) Is Null) AND (([t_activity_bran d1].[price_off_diage o_invoice])=True) AND (([t_activity_bran d1].[activity_brand_ cancelled])=False Or ([t_activity_bran d1].[activity_brand_ cancelled]) Is Null));

Many Thanks!

shovan

[Jim Doherty]

Hi ,

Can anybody convert below access query into equivalent stored procedure.
Also please advice in the below query,user defined function of VBA i.e anneeSelection4 1510() has been used in the .MDB file(access project).Now how this can be implemented in the equivalent .ADP(client/server architechture)p roject because obviously anneeSelection4 1510() function will not be recognised by the sql server." (t_activity_mon th.fy)=anneeSel ection41510()) ".
Hope,you will help me out in this.

SELECT t_customer.code _customer, t_customer.name _customer, t_activity_bran d1.no_activity AS Expr1, t_product.code_ product, t_product.produ ct_name, [price_off_amoun t1]*[nb_std_bt_plann ed1] AS mnt1, [price_off_amoun t2]*[nb_std_bt_plann ed2] AS mnt2, [price_off_amoun t3]*[nb_std_bt_plann ed3] AS mnt3, nz([mnt1],0)+nz([mnt2],0)+nz([mnt3],0) AS tot_mnt_mo, t_activity_bran d1.price_off_cu stomer_invoice AS Expr2, t_activity_bran d1.price_off_ac tive_invoice AS Expr3, t_activity.no_a ctivity_month, t_activity_mont h.fy, t_activity_bran d1.activity_bra nd_cancelled AS Expr4, t_activity.acti vity_cancelled
FROM t_product, t_activity_bran d1, t_activity_mont h INNER JOIN (t_customer INNER JOIN t_activity ON t_customer.code _customer = t_activity.code _customer) ON t_activity_mont h.no_activity_m onth = t_activity.no_a ctivity_month
WHERE (((t_activity_m onth.fy)=anneeS election41510() ) AND ((t_activity.ac tivity_cancelle d)=False Or (t_activity.act ivity_cancelled ) Is Null) AND (([t_activity_bran d1].[price_off_diage o_invoice])=True) AND (([t_activity_bran d1].[activity_brand_ cancelled])=False Or ([t_activity_bran d1].[activity_brand_ cancelled]) Is Null));

Many Thanks!

shovan

Hi Shovan,

I see your previous thread post was about SQL server too. It seems to me you have an ongoing project to convert an MDB fileserver application to SQL server backend and convert the frontend to an Access ADP Project file not just this query am I correct?

There are several elements of that SQL statement that will not work in SQL server the VBA NZ wrapper function is one of them where you could convert using either coalesce or case expression.

The useage of Access's default EXPR in aliasing column data in my view is pretty useless, it doesn't on the face of it visually 'mean' anything if presented as a header for a column of data in a matrixed display, you really need meaningful names there.

The VBA function whatever it does will have logic in it that needs to be replicated on the server. (whether that maps to a user defined function UDF in SQL Server or worked out within the stored procedure or otherwise via amended SQL remains to be seen. From what I see there it does no more than provide some sort of value for the WHERE clause argument of the SQL thus far)

To convert this properly would require prefixing the relevant tables with the OWNER (dbo) predicate to eradicate conflicts. (Its not always necessary but it is good practice in a multi user environment together with a host of other things not least of which is passing parameters to the 'stored procedure' you require evidently currently contained in the where clause of your posted SQL)

When I convert or create applications in SQL Server I ensure the dog (sql server woof) wags the tail and not the other way round. All of any SQL should sit on top of properly revised table structures having a standard naming convention and with datatypes to suit. Some datatypes and resultant values in Access are different in SQL Server. Inevitably I end up re-writing whole blocks of stuff that hitherto worked perfectly well in Access but which now needs wholesale amendment to suit the new more powerful backend environment properly!

To do it 'properly' rather than peacemeal requires an understanding of what you have there derived from a workable knowledge of your table structure keys, constraints, indexes relationships in other words a full scripted out text file of this, so that it could be replicated and tested. I would not be comfortable with saying "yeaaaah simply paste the SQL into a stored procedure and hope for the best" not good advice for futureproofing in my view.

On the face of it, the posted SQL is simply a single table joined to two others having restrictive crtieria centering on the VBA function return value together with a few boolean true or false values nothing too elaborate.

I am mindful of moving this to the SQL server forum, but will leave it here for the time being because of the Access ADP Project aspect to this (unless admins insist it be moved in which case I will).

Are you able to post your SQL Server table structure as a scripted out text file and also the contents of the VBA function so that we can see what it is doing, in pretty much the same way functionally, as you see? I think that needs dealing with first before any ADP frontend

Regards

Jim :)