473,320 Members | 2,088 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,320 software developers and data experts.

Access and SQL data

EV
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee
Nov 13 '05 #1
4 1232
You need to create a pass-through query and put the call to the Stored
Procedure there.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"EV" <er***@village.gurnee.il.us> wrote in message
news:4c**************************@posting.google.c om...
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee

Nov 13 '05 #2
Create a pass-through query which executes the sp, as in:

Exec sp_myspname @param1='blah blah'

Simply create a query; don't add a table; select the Query menu, SQL
Specific, Pass Through; enter the above statement (adjusted for your values)
in the query, and save.

You can then execute the pass-through query just as you would another query,
using Currentdb.execute, or Docmd.RunQuery.

If you store the password in the ODBC connect string (in Query Properties),
then the user won't get prompted for it each time.

Neil
"EV" <er***@village.gurnee.il.us> wrote in message
news:4c**************************@posting.google.c om...
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee

Nov 13 '05 #3
Well, I just said he could, not that he should.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:ot********************************@4ax.com...
On Wed, 01 Sep 2004 22:26:26 GMT, "Neil Ginsberg" <nr*@nrgconsult.com>
wrote:
If you store the password in the ODBC connect string (in Query
Properties),
then the user won't get prompted for it each time.


Gack! Use a trusted connection instead. (Storing passwords is always
a *bad* idea!)
--
A Prudent Man Does Not Make A Goat His Gardener.

Nov 13 '05 #4
EV wrote:
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee


As well as the queries pointed out by Doug & Neil you can do it directly
by either DAO or ADO methods:

DAO (Assumes a Database type variable pointing at the server initialised
with OpenDatabase with a connection string instead of a database name)
db.Execute "exec MySP", dbSQLPassThrough

With ADO you do the same sort of thing using a connection object or for
more control use a Command object.

Note, if using a pass through query you MUST manually set the
ReturnsRecords property to No if the stored procedure doesn't return
records otherwise an error will occur.
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #5

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

Similar topics

0
by: sedefo | last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i was researching how i can write a database independent data access layer. In my company we already use Data Access...
63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
9
by: WalterR | last post by:
This is my first time here, so there may be earlier relevant threads of which I am unaware. Though my experience with DB2 is not extensive, such as it is was under OS/390 or equ. My main...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
20
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to...
9
by: Tony Lee | last post by:
Some time a ago, on this newsgroup the following comments were made in recommending good references for Access (2003) >I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for...
17
by: DaveG | last post by:
Hi all I am planning on writing a stock and accounts program for the family business, I understand this is likely to take close to 2 years to accomplish. The stock is likely to run into over a...
5
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a...
17
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
21
by: nihad.nasim | last post by:
Hi there, I have a database in Access that I need on the web. The web page should connect to the database and write records for certain tables and view records for others. I want to know a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.