By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

Call MS SQL stored procedure (with linked connection)

P: 12
I already found two ways to call an MSSQL stored procedure form MS Access:

1) Pass through query
Problem: Every client has to configure the DNS-source, I don't want that, too much administration work...

2) Write VBA code (e.g. using ADO object)
Problem: I don't want another connection.

I already linked the MS Access database with SSMA. Why can't I use this connection to call a stored procedure?

Hopefully there's an easy solution...Thanks in advance

Best regards,
D.R.

Edit: MS Access 2007
Feb 19 '10 #1

✓ answered by NeoPa

@Poweruser
Each linked table will have a connection string (property) associated with it then. This can be seen using the VBA IDE by typing :
Expand|Select|Wrap|Line Numbers
  1. ? CurrentDB.TableDefs("linked table name").Connect
in the Immediate pane (Ctrl-G). Alternatively, the Linked Table Manager will show this for each linked table in your database I believe.
@Poweruser
Another alternative is to avoid the wizard and either type, or paste in, the string directly. I don't use 2007, but 2003 instead, but I doubt there is much difference in this respect.
@Poweruser
This works on using a similar string to that which you use in your linked tables. It doesn't link via your tables. There is no DSN stored within your database. The string, whatever it is, must use some form of DSN, whether it by file held, or in your registry (System Data Source). I suspect, given that you are already using connected tables, that there must be a usable DSN defined somewhere for you to reference.

I hope you find this helpful.

Share this Question
Share on Google+
12 Replies


P: 12
Nobody knows how to call an MSSQL stored procedure from MS access? :(

What's the best approach?

Best regards,
D.R.
Feb 20 '10 #2

ADezii
Expert 5K+
P: 8,638
To the best of my knowledge, there are 5 Methods for Executing Stored Procedures via a Microsoft Access Project:
  1. From the Access Database Container
  2. From another Stored Procedure
  3. From a Pass-Through Query
  4. From ADO using a Command Object
  5. From DAO
Feb 21 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
@Poweruser
What do you mean specifically by 'the DNS-source'?
Connections can be stored as a property of the PassThru query itself, so I'm struggling to see a problem here. Maybe because I don't know what you mean by the relatively loose term 'DNS-source' above.
@Poweruser
What is this? Standard Access or something 3rd-party?

You wouldn't be expected to know as a new member, but we like people to wait at least 24 hours before bumping a thread. All our volunteers have lives and none of us guarantees to be available every day (Weekends are generally particularly quiet - mainly because there are so much fewer questions and responses to deal with).

Last, but not least, Welcome to Bytes!
Feb 21 '10 #4

P: 12
Sorry for early bumping my thread, I was frustrated!

SSMA = Microsoft SQL Server Migration Assistant for Access 4.0

I used the tool to migrate all my access tables to MSSQL server. I want to use that same connection to call MSSQL stored procedures / functions.

Connections can be stored as a property of the PassThru query itself, so I'm struggling to see a problem here.
Really? I'm using Access 2007 when I want to change the "ODBC Connect Str" property of a PassThru query, I get the "Select Data Source" dialog -> I can choose either a file data source or a machine data source. Nothing embedded in my access application :(

@ADezii: Which do you think is the fastest way? Becasue performance is the main reason I want to use stored procedures.

How to call a stored procedure "From the Access Database Container"?

Best regards,
D.R.
Feb 21 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
@Poweruser
Each linked table will have a connection string (property) associated with it then. This can be seen using the VBA IDE by typing :
Expand|Select|Wrap|Line Numbers
  1. ? CurrentDB.TableDefs("linked table name").Connect
in the Immediate pane (Ctrl-G). Alternatively, the Linked Table Manager will show this for each linked table in your database I believe.
@Poweruser
Another alternative is to avoid the wizard and either type, or paste in, the string directly. I don't use 2007, but 2003 instead, but I doubt there is much difference in this respect.
@Poweruser
This works on using a similar string to that which you use in your linked tables. It doesn't link via your tables. There is no DSN stored within your database. The string, whatever it is, must use some form of DSN, whether it by file held, or in your registry (System Data Source). I suspect, given that you are already using connected tables, that there must be a usable DSN defined somewhere for you to reference.

I hope you find this helpful.
Feb 21 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
@NeoPa
It appears that, while the LTM does show some information for each linked table, it doesn't necessarily include all the data. I would certainly recommend using the former method for discovering the correct working .Connect property value.
Feb 21 '10 #7

P: 12
Thank you very much!

I used the VBA immediate pane to get the ODBC string (other people reading this thread: don't forget the '?' in the beginning...)

Then Copy&Paste this string to the Pass through query's ODBC Connect Str property.

Finished!


Somebody knows if this is the fastest way for calling stored procedures? At least it seems to be a very easy way.

Thank you again!

Best regards,
D.R.
Feb 21 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome.
@Poweruser
It's the most direct.

If the SP returns a Cursor (a recordset essentially), you can link to that as a table too, but if you want work done on MS SQL Server that involves executing your SQL code (a simple EXEC of the SP doesn't really benefit that much) then PassThrus are the most efficient ways of doing this I believe.
Feb 21 '10 #9

ADezii
Expert 5K+
P: 8,638
One important item to keep in mind is that Pass-Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.
Feb 22 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
@ADezii
Check out the properties of a PassThru query. You're looking particularly for :
  1. Returns Records
  2. Log Messages
Feb 22 '10 #11

ADezii
Expert 5K+
P: 8,638
Not the same as Output Parameters and Return Values, are they?
Feb 22 '10 #12

NeoPa
Expert Mod 15k+
P: 31,494
No. They're not exactly. You're right.

I inferred from the context you were talking more generally about returning the values produced by a Stored Procedure. It's true that such values cannot be passed back directly to VBA calling code.

The Return Value is accessible, as can any Output Parameters, but only by directing them to a table within the SQL. They are certainly not as easy to process as local procedures.
Feb 22 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.