473,372 Members | 1,040 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Access Front End Stored Procedure Advice

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
Nov 12 '05 #1
1 2033
I have the same setup you have at my workplace. ADO is the way to use
stored procedures from Access (the only way that I know anyways). Com
ADO is what you use for Access97. To use Com ADO (as opposed to
ADO.Net) make sure you have mdac2.5 and mdac2.6 installed on the
workstations that would use your application. You can get mdac2.5 and
mdac2.6 at msdn. Actually, I just checked and can't find it (them). I
believe mdac 2.5 was the last version that contained the jet engine.
Someone please correct me if I'm wrong. But I had problems if I did not
also load mdac 2.6. The thing is that this is all legacy stuff,
Access97 is sort of legacy (although robust as heck and still kicking
like no tommorrow) so support will probably be limited and hard to find
(try a google search). Anyway, assuming you have all this loaded, here
is the code for using com ADO. First, make sure you have a reference to
Microsoft ActiveX data Objects 2.6 (or higher) in Tools/References (from
any code module). Here's the code:

Dim cmd As New ADODB.Command
Dim RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset
...
'here we return records

cmd.ActiveConnection = "Provider=SQLOLEDB;Data
Source=yourSqlServerName;" _
& "Initial Catalog=yourSqlDB;UID=SA;PWD=tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_yourStoredProc"
sDate = #1/1/04# 'date parameters - assume you have params
eDate = #1/31/04# 'called @bDate and @eDate defined in Sqls
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDB.OpenRecordset("tbl1")
Do While Not RSado.EOF
'RSdao and RSado have same fields
RSdao.AddNew
For i=0 to RSado.Fields.Count-1: RSdao(i)=RSado(i):Next
RSdao.Update
RSado.MoveNext
Loop

To execute an Update or Insert Query (stored Procedure) just do all of
the above except instead of
Set RSado = cmd.Execute
it is just
cmd.Execute
and you are done.

AS for returning records, I have experimented with a sql Server method
called OpenRowset to return bulk data directly into Access97 instead of
looping, but that only works at the server, couldn't get it to work on a
workstation (remotely). You need ADO.Net for non-looping stuff (very
sweet and way more efficient than com ADO except that you have to write
your own .Net dlls to work it with Acc97 - been there - not too bad but
not as easy as com ado).
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: 8leggeddj | last post by:
Hello, I am having a problem when using access xp as a frontend for sql server 2000. I have been trying to update a number of stored procedures (Just simple adding fields etc) which results in...
38
by: Remco Groot Beumer | last post by:
Hello, I'm trying to decide if the following situation would be workable: Generate an MS Access Front End (which will run localy on client computers), which will link to a DBMS (SQL server or...
7
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
1
by: Neil | last post by:
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...
1
by: Terry | last post by:
Hello, Has anyone experienced the following problem following an Upsize from Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see what the problem might be. Before Upsize...
4
by: BerkshireGuy | last post by:
Our IT department wants to place our Access 2000 tables on an SQL server due to the fact the tables are quite large. With that said, can we still use the Access queries or do we have to do...
4
by: Supa Hoopsa | last post by:
I am in the process of rewriting an MS Access application (access front end with SQL 2k database) in VB.NET and one of the things I would like to do is link in to the compiled Access ADE so that I...
10
by: Hank | last post by:
We have just recently migrated the data from our Access 2000 backend to Postgres. All forms and reports seem to run correctly but, in many cases, very slowly. We do not want to switch over until...
10
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Thank you for that. It was very...
23
by: Gloops | last post by:
Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.