Access front end - stored procedure result

Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil

quote:

---

Originally posted by Neil
Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil

---

I was in your situation a couple years back when I first migrated my systems from Acces
s97 to Access2000 + SQL Server. There's actually a moderate amount of (somewhat obscur
e) reference material at http://support.microsoft.com, but I'll give you a s
hort synopsis.
Using ADO requires a few things:
First, in any database that uses ADO code, you're going to need to set a Ref
erence to a Microsoft ActiveX Data Objects Library.
In the VBA editor window, select Tool ->
References
Select an appropriate version of Microsoft ActiveX Data Objects Library. I
believe Access97 ships with at least version 2.1, but I believe the followin
g code will work just fine with pretty much any version.
Once you've done that, you need to do two things.
- Set up a Connection to your data source (in your case, SQL Server)
- Retrieve the data through the connection
You could also do this via DAO, although I honestly don't recommend doing so
, since DAO is terribly inefficient at handling communications between Acces
s and SQL Server (speaking from personal experience). While researching thi
s, I happened to find some old DAO code that I had commented out of my datab
ase, so I can provide the syntax for the DAO call here.
Hope this helps.
Jim
Edit: Sorry for the poor formatting, this forum software doesn't seem to wan
t to format my posts correctly.

code:

Public Sub VBA_ADO_Example()
' Example code to demonstrate how to make an ADO
' call to retrieve data from SQL Server
' Declare variables
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
' Open a connection to the server
' Depending on what sort of login authentication 
' you are using on your server...
' If you are using SQL Server authentication...
cnn.ConnectionString = "Provider=SQLOLEDB.1;
Data Source=MY_SERVER_NAME;
Initi
al Catalog = MY_DB_NAME;
User ID = MY_LOGIN_NAME;
Password = MY_PASSWORD"
' If you are using Windows authentication...
cnn.ConnectionString = "Provider=SQLOLEDB.1;
Data Source=MY_SERVER_NAME;
Initi
al Catalog = MY_DB_NAME;
"
cnn.ConnectionTimeout = 0
cnn.Open
' Get the data
rst.Open "SELECT * FROM My_Table WHERE MyCondition = 1", cnn, adOpenDynamic,
 adLockPessimistic
' At this point, you should be able to treat the rst object
' pretty much exactly like you would a normal DAO recordset
With rst
 .AddNew
 !MyDataField = MyValue
 .Update
End With
rst.MoveFirst
MsgBox "My First Value Is " & rst!MyDataField
' Clean up 
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = nothing
End Sub

code:

Public Sub VBA_DAO_Example()
' Example code to demonstrate how to make an DAO
' call to retrieve data from SQL Server
' Declare variables
Dim ws as Workspace
Dim cnn as Connection
Dim rst as Recordset
Set ws = CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseODBC)
' Open a connection to the server
' Depending on what sort of login authentication 
' you are using on your server...
' If you are using SQL Server authentication...
Set cnn = ws.OpenConnection("MyConnection", dbDriverNoPrompt, False, "ODBC;
D
 ATABASE=MY_DATABASE;
UID=MY_USERNAME;
PWD=
MY_PASSWORD;
DSN=MY_ODBC_DATA_SOURCE_
NAME")
' If you are using Windows authentication...
Set cnn = ws.OpenConnection("MyConnection", dbDriverNoPrompt, False, "ODBC;
D
 ATABASE=MY_DATABASE;
DSN=MY_ODBC_DATA_SOU
RCE_NAME")
Set rst = cnn.OpenRecordset("SELECT * FROM MyTable WHERE MyCondition = 1", d
bOpenDynamic, 0, dbPessimistic)
' Manipulate or retrieve data
With rst
 .AddNew
 !MyDataField = MyValue
 .Update
End With
rst.MoveFirst
MsgBox "My First Value Is " & rst!MyDataField
' Clean up
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
ws.Close
Set ws = nothing
End Sub

|||Pass-through queries *are* the most efficient way of working with
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:

>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil