Can I use MS-SQL Stored Procedures through an ODBC connection?

[Lyle Fairfield]

I created a new MS-SQL Database, "TestODBC".

I made Table1 and StoredProcedure1.

I made an ODBC DSN for that MS-SQL Database.

I created a new AccessXP mdb, "TestODBC".

I linked to the table of the MS-SQL Database, "TestODBC". I wanted to use
StoredProcedure1 as the bound recordsource for a new form Form1. But I
could not find nor link to StoredProcedure1 from the MDB file.

Is there some way that I can bind an Access form in an MDB to a stored
procedure in an MS-SQL database using a ODBC "connection"?

*********

So I went back to the MS-SQL database and made View1 which corresponded to
StoredProcedure1. In the mdb file I linked to View1 as a Table and I was
able to create a form bound to View1. As of yet I have not been able to
have the form display any data, nor is it editable.

Is there some way that I can bind an Access form in an MDB to a view in an
MS-SQL database using a ODBC "connection" and view and edit the data?

*********

So I went to the mdb and created Query1 corresponding to StoredProcedure1
and View1. A form bound to the Query1 worked swimmingly. Is this the
standard way of creating bound forms with an ODBC connection?

What engine is actually doing the work for this query: SQL-Server, ADO,
DAO, something else? Where is it doing the work and how? Is this any
different from where and how a Stored Procedure would do the work?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Terry Kreft]

1)

Is there some way that I can bind an Access form in an MDB to a stored
procedure in an MS-SQL database using a ODBC "connection"?
Use a pass-through query, this will not be updatable though.

2) Is there some way that I can bind an Access form in an MDB to a view in an
MS-SQL database using a ODBC "connection" and view and edit the data?
Make sure your table has an Identity column and that this is included in
your view. When you link Access will ask you to identify the key column,
select your Identity column.

3) What engine is actually doing the work for this query: SQL-Server, ADO,
DAO, something else? SQL Server, DAO and Access
Where is it doing the work and how? Depends on how your table is set up, best case scenario is it transfers the
index across the network and then uses that to decide which records to call.
Worst case scenario it transfers the whole table and then sorts/filters
locally.
Is this any different from where and how a Stored Procedure would do the work?
Yes, a SP will carry out the work on the server and you then get the
resultset back again.

Using methods 1 and 2 more work is carried out on the server and therefore
less data is transfered across the network.
Terry
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... I created a new MS-SQL Database, "TestODBC".

I made Table1 and StoredProcedure1.

I made an ODBC DSN for that MS-SQL Database.

I created a new AccessXP mdb, "TestODBC".

I linked to the table of the MS-SQL Database, "TestODBC". I wanted to use
StoredProcedure1 as the bound recordsource for a new form Form1. But I
could not find nor link to StoredProcedure1 from the MDB file.

Is there some way that I can bind an Access form in an MDB to a stored
procedure in an MS-SQL database using a ODBC "connection"?

*********

So I went back to the MS-SQL database and made View1 which corresponded to
StoredProcedure1. In the mdb file I linked to View1 as a Table and I was
able to create a form bound to View1. As of yet I have not been able to
have the form display any data, nor is it editable.

Is there some way that I can bind an Access form in an MDB to a view in an
MS-SQL database using a ODBC "connection" and view and edit the data?

*********

So I went to the mdb and created Query1 corresponding to StoredProcedure1
and View1. A form bound to the Query1 worked swimmingly. Is this the
standard way of creating bound forms with an ODBC connection?

What engine is actually doing the work for this query: SQL-Server, ADO,
DAO, something else? Where is it doing the work and how? Is this any
different from where and how a Stored Procedure would do the work?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)