Call MS SQL stored procedure (with linked connection)

[Poweruser]

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

[Poweruser]

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

What's the best approach?

Best regards,
D.R.

[ADezii]

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

[NeoPa]

@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!

[Poweruser]

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.

[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 :

? 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.

[NeoPa]

@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.

[Poweruser]

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.

[NeoPa]

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.

[ADezii]

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.

[NeoPa]

@ADezii
Check out the properties of a PassThru query. You're looking particularly for :

1. Returns Records
2. Log Messages

[ADezii]

Not the same as Output Parameters and Return Values, are they?

[NeoPa]

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.