473,573 Members | 2,810 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2059
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.ActiveConne ction = "Provider=SQLOL EDB;Data
Source=yourSqlS erverName;" _
& "Initial Catalog=yourSql DB;UID=SA;PWD=t iger;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_yourStored Proc"
sDate = #1/1/04# 'date parameters - assume you have params
eDate = #1/31/04# 'called @bDate and @eDate defined in Sqls
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDB.OpenR ecordset("tbl1" )
Do While Not RSado.EOF
'RSdao and RSado have same fields
RSdao.AddNew
For i=0 to RSado.Fields.Co unt-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
1651
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 access crashing with event ID 1000 and 1001. Does anyone have any ideas as to what could be the problem? Thanks in advance..
38
2287
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 Oracle). As far as I know there will be approx. 30 till 40 users max working on the database. I think approx. 25 users will actually enter data ...
7
6401
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 opened. The reports record source is a query. The query uses the value from the form text box to restrict the query. Table name = EggsTable one of the...
1
361
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 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...
1
2059
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 (Access 97 front end and Access 97 tables): A MainForm loads up correctly and then I enter a ReferenceNumber value in a MainForm field control. I then...
4
1682
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 everything via SQL views? I would think we could just create an ODBC connection to the tables on the SQL server and that is the only thing we would need...
4
3219
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 can use the reports that have already been created. I have somewhere in the region of 200 reports already created in MS Access and I really don't want...
10
2353
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 we can speed things up. We would like to start implementing Stored Procedures so we can do Server-Side processing. Can anyone recommend a book...
10
409
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 clear and I actually understand it!
23
3378
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 and ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600. I failed executing an Oracle stored procedure from Access, and a trigger to store...
0
7788
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8037
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7799
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6430
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3737
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2226
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.