Call a Function from a View or Stored Procedure

[PaulBricker]

Is there some way to call a Function, RMax(ParamArray FieldValues()) As Variant, from a Stored Procedure or View. I'm just starting to try and work with .adp files.
Thanks

Paul

 

[cmmrfrds]

If this is an Access Function then no, since sql server will not be aware of the Access Function. You can use an Access Function to return values as part of building dynamic SQL that will be send to the server. You could recode the function as an sql server function, if that has any value.

There are ways of using Access tables in SQL Server joins, but these are usually for special circumstances. The 3 most common ways are (1) an Access Table as a linked server in sql server. (2) OPENROWSET and (3) OPENQUERY which are both sql server functions that can be used to dynamincally join tables from other databases.

 

[PaulBricker]

Sorry I wasn't more clear but let me try again. First, this is practice for me. I've worked in Access a long time but am just pushing the envelope a little. In Access, you can put a function in a general Module and call it from the query using something like this
MyVal:RMax(Field1, Field2, Field3)

and it will return the max value of those three fields. Now if I wanted to do this all within the framework of a .adp project, how would I set up the Function. I've been reading some MS articles here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

on creating functions but I haven't actually successfully written a function and have it do anything (can't even save it yet). So I'm looking for the BASICS on where to start. I'm not even sure if the Create Function goes in a Stored Procedure or View (I've tried both equally unsuccessfully).
Also, any links to tutorials or suggested reading would be good also.
Thanks

Paul

 

[cmmrfrds]

The functions described in the URL are sql server functions. I use Access 2000, so I am not aware if newer versions of Access supply more interface capability to create objects on sql server. An Access 2000 project allows creating Views (somewhat limited) and Store Procedures. The stored procedure interface is qood and useful. To create views and other objects, such as, sql server functions it is best (necessary) to have the sql server front end tools. Enterprise Manager is the most robust, but Query Analyzer will also work fine. Personally, I would not undertake an sql server project without at least Query Analyzer, unless newer versions of Access Project are more robust in the sql server interface.

 

[PaulBricker]

I'm using A2000 as well. Sounds like there is a good reason why I can't do this. I wonder is anyone knows a good book on working with .adp projects. There must be some way inside the .adp framework to run a function from a View or Stored Procedure. It doesn't make sense that it can be so easy in Access.mdb's and so hard in .adp apps.
Call me 'confused'.

Thanks

Paul

 

[cmmrfrds]

You can always send SQL DDL statements such as a Create statements though an ADO connection where it is built through code. A rough example.

Dim mysql as string

mysql = "USE YourDataBaseName
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'categories')
DROP VIEW categories
GO
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO"

The above is a batch of sql statements that could be sent through an ADO connection.

Dim cn as New ADODB.Connection
Set cn = currentproject.connection

cn.Execute mysql

cn.close
Set cn = Nothing

This is crude but can be done if no other tool is available.

 

[PaulBricker]

OK, I'll start here and post back with any problems. I know I've got a long way to go with this so I don't expect to put it all together in a few days.

Thanks

Paul

 

[Nowell]

Hi Paul,

if you are going to use an .adp and can afford to upgrade to Access XP then i would recomend it. There is a few problems with Access 2000 project, especially when dealing with bit operators.

In answer to your question, you could always use your functions in VBA to get values then parse those values to a stored procedure as parameters.