Using SQL User-Defined Function from Access 2003

J

jhmosow

I have an Access 2003 database with the backend being a SQL 2000
database. I have written a User-Defined function but can not figure
out how to call this function from VBA code. Could someone provide an
example of how to do this? The user-defined function returns a table
and works fine in SQL Query Analyzer.

TIA
 
R

Ron Hinds

This is all using DAO, which may not have a reference set in A2003:

Dim qdf As QueryDef
Dim rs As Recordset

Set qdf = CurrentDb.CreateQueryDef("myQuery")

qdf.Connect = your_connect_string (in DAO syntax, *not* ADO!!)
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647 '2^31 - 1
qdf.SQL = whatever you put in Query Analyzer to execute your UDF

Set rs = qdf.OpenRecordset()
 
G

Guest

Note, this sample code creates and saves a named query
in your Access database. You can use the same query
again just by:

set qdf = CurrentDB.QueryDefs("myQuery")
Set rs = qdf.OpenRecordset()

(or from qdf.Execute if the stored procedure does not
return records).

To use a temporary query, not stored in your Access
database, use an unamed querydef:

Set qdf = CurrentDb.CreateQueryDef("")

(david)
 
R

Ron Hinds

Yes, in my own code I always delete the named queries I create on the fly
like that just before exiting the Sub/Function. The reason you might want to
use named queries in a situaion like this would be if you are joining
against e.g. a local table.
 
J

jhmosow

First, thanks for the help. I will try it when I am back in the office
in a week.

Second, can you provide a sample of a connection string used to connect
to a Microsoft SQL 2000 server you refer to in qdf.Connect? I had
tried some but they all seem to fail.

I have some queries I already run against the tables in my VBA code
that returns recordsets. When I used the same techique to run a
function, it fails. I am sure this is because Access doesn't know
anything about SQL functions, only tables and views.

Thanks again.
 
R

Ron Hinds

Here is one from one of my databases (the names were changed to protect the
innocent):

ODBC;Description=Backup Database;DRIVER=SQL
Server;SERVER=SQL2000-1;UID=sa;PWD=pass;APP=Microsoft Open Database
Connectivity;WSID=RAMAC-RON;DATABASE=Prototype

The elements are:

Description - anything you want
SERVER - your server name
UID - a valid user id from your server
PWD - UID's password
WSID - Workstation ID (not sure if this is absolutely necessary)
DATABASE - the databse you are linking to
 
D

Douglas J. Steele

Ron's example assumes that you're using SQL Server IDs, not Trusted
Connection. To be honest, though, I thought it was necessary to put braces
around the Driver: DRIVER={SQL Server}, not DRIVER=SQL Server

If you're using Trusted Connection, use

"ODBC;DRIVER={sql
server};DATABASE=DatabaseName;SERVER=ServerName;Trusted_Connection=Yes;"

For other possibilities, check the ODBC section of Carl Prothman's site:
http://www.carlprothman.net/Default.aspx?tabid=90

(and no, the WSID isn't necessary).
 
R

Ron Hinds

Douglas J. Steele said:
Ron's example assumes that you're using SQL Server IDs, not Trusted
Connection. To be honest, though, I thought it was necessary to put braces
around the Driver: DRIVER={SQL Server}, not DRIVER=SQL Server
Click to expand...

That is the way Access built the connection string. I just typed in the
Debug window:

?CurrentDb.TableDefs("Customer").Connect

and that is what is returned. Perhaps the curly braces are optional?
 
J

jhmosow

It looks like this is using ODBC connections. Is it possible to use a
direct SQL connection?
 
G

Guest

It is certainly the case that SQL Server can control the
visibility of some objects. If it doesn't work, you may
want to ask the question again in some other groups.

Or you could create a view that calls the function.

(david)
 
D

Douglas J. Steele

What do you mean by "a direct SQL connection"? All applications need to use
a protocol like ODBC or OleDB in order to communicate with external
databases. Access cannot use OleDB, so you're limited to ODBC.
 
T

TC

Ron said:
Set qdf = CurrentDb.CreateQueryDef("myQuery")
Click to expand...

You shouldn't use currentdb like that. Instead, cache its value in a
variable:

dim db as database
set db = currentdb()
set qdf = db. etc.

For more information, google "currentdb cache kaplan" without the
quotes.

HTH,
TC
 
T

TC

david@epsomdotcomdotau said:
Use of CurrentDB [in that way] is an acceptable coding practice.
Click to expand...

Here are some of the statements I was thinking of:

===

MK: "In some cases, the use of CurrentDb on a single line will cause a
non-persistent - object to be created, so you will not be able to use
[that object] if you do not cache it".

===

Andy Barron: "my rule of thumb is don't use Currentdb for anything that
you can't do [completely] in one line."

MK: "This is not CurrentDb's implementation that is responsible, it is
VBA.... they are the ones who are charged with the "hidden" obj ref in
this case and they are the ones who free it since they think they are
done." [ IOW, there are cases where creating a new object based off
a currentdb call, will fail. ]

===

Andy Barron: "The rule of thumb that I follow is to use currentdb by
itself (rather than setting a variable to currentdb) only if what I'm
doing can be done in one line. For multi-line tasks, I always use a db
variable. There are some multi-line cases, like opening recordsets,
where currentdb by itself will work, but mostly it won't."

MK: "Basically, as soon as that one line is done running, the db
variable itself will be gone. So unless the thing you get from db can
stand on its own and keep an internal ref when needed (such as
recordsets do) then the other variable won't work. The best two
examples are the Recordset, which DOES hold such a ref... and the
TableDef, which does not."

MK: "A Querydef ref actually loads the query and does not require the
db ref to be there still (provided the db is open)..... while the
TableDef ref does not "open the table" and it needs to call back to the
db ref to get info."

===

And I guess the definitive post:

MK:

<quote>

Well, you took it a step further than I intended. CurrentDB is a
special case because each call to it creates a new object, so that the
TableDef can try to go back to its parent and fail as it is looking at
a pointer that is basically not valid. So it is the tdf that needs the
db and can't get it anymore. So the following two operations are
equivalent:

Set tdf = CurrentDb.TableDefs(0)
? tdf.Name

Set db = CurrentDb
Set tdf = db.TableDefs(0)
set db = Nothing
? tdf.Name

In both cases the attemot to use tdf will fail. Basically because DAO
does not understand enough to know where to go back to. AFAIK, any time
the latter case works, the former case will work, however. That is the
basic reason for a difference between CurrentDb and DBEngine(0)(0) in
this "one line" scenario.... an implementation detail in Access.

Note that

set db = DBEngine(0)(0)
set tdf = db.TableDefs(0)
set db = Nothing
? tdf.Name

will succeed, as will

Set tdf = DBEngine(0)(0).TableDefs(0)
? tdf.Name

because it is guaranteed that the TableDef has a "place" to go back to
in the case where you use DBEngine (at least in Access -- in VB you can
see the same kind of issue in cases where you can invalidate the (0)(0)
database.

</quote>

===

I can't say I 've re-tested all this in Access 2000 & later. I just
remember MK's previous warnings, & comply with those :)

Cheers,
TC
 
T

TC

Further to this, I note your statement: "you need to use a persistent
reference when referring to tabledefs". That's the kind of issue I was
getting at. IMO it's not realistic to expect most developers to think:
"Is this, or is it not, one of the objects that I can create directly
off a currentdb call?" Better IMO to avoid the one-line call entirely.

My comment was not in regard to the performance aspects. I agree that
cached versus non-cached makes no difference if there is only one call.

Cheers,
TC
 
G

Guest

I'm not sure that I found anything of relevance at:
For more information, google "currentdb cache kaplan"
Click to expand...

You should 'cache' references when using loops,
and some people use a global reference instead of CurrentDB,
and you need to use a persistent reference when referring to
tabledefs,

But for a simple function/subroutine with a single reference
to CurrentDB, where the time to refresh the database
collection is negligible,

Use of CurrentDB is an acceptable coding practice.

(And use of module level database variables is almost always
a bad idea).

And in general, although it is an acceptable optimisation, there
is very little benefit to using a global database variable.

(david)
 
D

dbahooker

Douglas

and doesn't Access use OLEDB whenever it connects to an MDB linked
table?

that's OLEDB not ODBC right???

but it's hidden? so access TOTALLY supports OLE DB; it's just
happening behind the scenes.

I would ALMOST begin to forgive ms for being a bunch of drunk assholes
if they just added true OLE DB support to MDB.

SO I CAN FUCKING LINK TO OLAP QUERIES I MEAN **** YOU MICROSOFT EAT
SHIT AND DIE REDMOND
 
G

Guest

Yes, I can see Andy Barons point, and I can see your point
made in your other message.

No, I don't think MK's posts make the point for you. :~)

(david)
 
R

Ron Hinds

Actually, from a performance standpoint, using CurrentDb at all (even to set
a reference variable) is not good. In my own code, I always set a db
variable at the beginning of a routine like so:

Dim db As Database

Set db = DBEngine(0)(0)

This *does* make a difference in performance - try it, you'll see (I didn't
believe it until I did some benchmarks). But in my "down-and-dirty" code to
answer a question, it's easier just to use something people are familiar
with.
 
Top