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 anneeSelection41510() has been used in the .MDB file(access project).Now how this can be implemented in the equivalent .ADP(client/server architechture)project because obviously anneeSelection41510() function will not be recognised by the sql server." (t_activity_month.fy)=anneeSelection41510()) ".
Hope,you will help me out in this.
SELECT t_customer.code_customer, t_customer.name_customer, t_activity_brand1.no_activity AS Expr1, t_product.code_product, t_product.product_name, [price_off_amount1]*[nb_std_bt_planned1] AS mnt1, [price_off_amount2]*[nb_std_bt_planned2] AS mnt2, [price_off_amount3]*[nb_std_bt_planned3] AS mnt3, nz([mnt1],0)+nz([mnt2],0)+nz([mnt3],0) AS tot_mnt_mo, t_activity_brand1.price_off_customer_invoice AS Expr2, t_activity_brand1.price_off_active_invoice AS Expr3, t_activity.no_activity_month, t_activity_month.fy, t_activity_brand1.activity_brand_cancelled AS Expr4, t_activity.activity_cancelled
FROM t_product, t_activity_brand1, t_activity_month INNER JOIN (t_customer INNER JOIN t_activity ON t_customer.code_customer = t_activity.code_customer) ON t_activity_month.no_activity_month = t_activity.no_activity_month
WHERE (((t_activity_month.fy)=anneeSelection41510()) AND ((t_activity.activity_cancelled)=False Or (t_activity.activity_cancelled) Is Null) AND (([t_activity_brand1].[price_off_diageo_invoice])=True) AND (([t_activity_brand1].[activity_brand_cancelled])=False Or ([t_activity_brand1].[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 :)
2
