How do I use stored procedures in an accdb?

[Guest]

With the possible loss of the adp, what is the best way to access all the
stored procedures that I have created in SQL Server from an access accdb

 

[AnandaSim]

With the possible loss of the adp, what is the best way to access all the
stored procedures that I have created in SQL Server from an access accdb

I would expect that MSDN and Technet will have articles to help you re-
skill from ADP. One is here:

http://support.microsoft.com/kb/225918

Remember, in .MDB or .ACCDB, you can still reference ADO and you can
create pass through queries.

 

[Guest]

Thanks for the information, I guess I had too much of a good thing... ADP was
VERY GOOD.

AM I right to say pass through queries are read only?

Thanks again

John

 

[AnandaSim]

Thanks for the information, I guess I had too much of a good thing... ADP was
VERY GOOD.

I had one (for me) major project with ADP and I use ADP regularly as a
poor man's easy to use front end to SQL Server 2000 for a few
databases.

For development, I found the ADP not rich enough in functionality
compared to MDB (started with a thin wrapper over SQLDMO and became
thicker in Access 2002, 2003). Because Jet isn't there, the ease of
use in creating bound forms with filters etc... required a change in
the way that I usually do it in an MDB solution. And I remember facing
the problem that the Form Serverfilter would persist and stay on after
the end user closed and re-opened the Form. Something like that.

I think this must have been the collective experience that MS got
feedback on.

And just using ADP as a front end, could also give problems because
Access would instruct something and SQL Server would not comply.

Maybe in long term they could just make the ADP a free accessory tool
for SQL Server or something like that.

In the MDB, when you link tables, you will see both Tables and Views
of SQL Server. I can't remember whether you see SQL Server Stored
Procedures in the Link Table Manager.

AM I right to say pass through queries are read only?

I have used Pass Through Queries to connect to Oracle ODBC. I don't
remember whether they can/are ReadWrite - I can check when I am next
at that workplace or am fiddling.

Regards

Ananda

 

[Pieter Wijnen]

Pass through queries are read-only in the sence that you can't alter the
returned recordset.
You can however run stored procedures (or "ad-hoc" ones, DDL etc through
them)
I don't remember the regkey exactly (somewhere under Access/Jet) but as far
as I remember the default filter (what's shown in the Linked Table Manager)
Is 'TABLES, VIEWS & SYNONYMS
I Usually Do my own "Table managing" based on the INFORMATION_SCHEMA meta
tables

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

SELECT * FROM INFORMATION_SCHEMA.VIEWS

HTH

Pieter

 

[AnandaSim]

Thanks for the tip Pieter.

Pass through queries are read-only in the sence that you can't alter the
returned recordset.
You can however run stored procedures (or "ad-hoc" ones, DDL etc through
them)

I don't remember the regkey exactly (somewhere under Access/Jet) but as far
as I remember the default filter (what's shown in the Linked Table Manager)
Is 'TABLES, VIEWS & SYNONYMS

http://support.microsoft.com/kb/323658

Pass through querys

http://support.microsoft.com/kb/303968
http://support.microsoft.com/kb/304323

Read Only
http://support.microsoft.com/kb/288633