Although I'd rather stick with the ADP format when writing applications that
use Access as a front-end to SQL Server, just to be safe I'm trying to work
out how to talk to SQL Server from an Access 2007 ACCDB file. My question is,
can all of the following requirements be met simultaneously (especially using
bound forms and an updateable recordset)?

1) Access ACCDB talking to SQL Server 2005.
2) Either ADO or DAO, but preferably ADO.
3) Bound forms (but NOT directly to a linked table or view, instead to an
ADO or DAO recordset based on parameterized stored procedures).
4) The recordset is updateable.

I can do all of the above using pass-through queries, except a PTQ isn't
updateable. I'd rather not move to an unbound form model because of the
substantial increase in coding required. Likewise for doing the development
in Visual Studio.

Any suggestions re whether this can be done, and if so, how?

On Tue, 9 Jun 2009 09:49:01 -0700, PGallez
<(E-Mail Removed)> wrote:

SELECT stored procedures are NEVER updatable. If you have middle-ware
smart enough to derive the corresponding UPDATE or INSERT statement,
great, but I don't think Access the way you describe it is one of
them.

What is wrong with linked tables/views?
What is unsafe about ADP?

-Tom.
Microsoft Access MVP


>Although I'd rather stick with the ADP format when writing applications that
>use Access as a front-end to SQL Server, just to be safe I'm trying to work
>out how to talk to SQL Server from an Access 2007 ACCDB file. My question is,
>can all of the following requirements be met simultaneously (especially using
>bound forms and an updateable recordset)?
>
>1) Access ACCDB talking to SQL Server 2005.
>2) Either ADO or DAO, but preferably ADO.
>3) Bound forms (but NOT directly to a linked table or view, instead to an
>ADO or DAO recordset based on parameterized stored procedures).
>4) The recordset is updateable.
>
>I can do all of the above using pass-through queries, except a PTQ isn't
>updateable. I'd rather not move to an unbound form model because of the
>substantial increase in coding required. Likewise for doing the development
>in Visual Studio.
>
>Any suggestions re whether this can be done, and if so, how?

What you are describing is to work the same way as with an ADP project but
with using an ACCDB database file instead of an ADP project. If it was
possible to use an ACCDB database file against an SQL-Server in exactly the
same way as with an ADP project, I don't know what would be the usefulness
of ADP in this (hypothetical) context.

With ADO, you can create an ADO recordset from a SP, disconnect it and then
connect it to a form but I don't remember if this form will be updatable or
if it will be read-only. You can try it, you'll see the answer by yourself.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"PGallez" <(E-Mail Removed)> wrote in message
news:924CB492-9D39-4650-85CB-(E-Mail Removed)...
> Although I'd rather stick with the ADP format when writing applications
> that
> use Access as a front-end to SQL Server, just to be safe I'm trying to
> work
> out how to talk to SQL Server from an Access 2007 ACCDB file. My question
> is,
> can all of the following requirements be met simultaneously (especially
> using
> bound forms and an updateable recordset)?
>
> 1) Access ACCDB talking to SQL Server 2005.
> 2) Either ADO or DAO, but preferably ADO.
> 3) Bound forms (but NOT directly to a linked table or view, instead to an
> ADO or DAO recordset based on parameterized stored procedures).
> 4) The recordset is updateable.
>
> I can do all of the above using pass-through queries, except a PTQ isn't
> updateable. I'd rather not move to an unbound form model because of the
> substantial increase in coding required. Likewise for doing the
> development
> in Visual Studio.
>
> Any suggestions re whether this can be done, and if so, how?

Tom, thanks for responding.

Here are some reasons to not directly specify a linked table or view as the
recordsource for a bound form:

1) The same form is going to be used to present data to different groups of
users with different permissions to the underlying SQL Server data. I usually
do this by testing for SQL Server role membership in the form's Load event
and then specifying the appropriate stored procedure/input parameters.

2) The same form is going to be used to present data that is "filtered" in
different ways. Again, the form's recordsource is based on different
parameterized stored procedures and the procedures/input parameters are
specified in the form's Load event.

One could avoid having to do this by having multiple forms, each with their
own "static" recordsource, but then when the form's design changes, the
change has to be made on ALL of the copies, which is time consuming and
error-prone.

There's nothing "unsafe" about ADP's, they've been a perfect solution for me
for years. But I get the impression that they may be going away in future
version of Access (it looks like they might have been included in 2007 for
backward compatibility) and I'd like to have an alternative that still allows
me to use Access as a front-end for SQL Server.

"Tom van Stiphout" wrote:

> On Tue, 9 Jun 2009 09:49:01 -0700, PGallez
> <(E-Mail Removed)> wrote:
>
> SELECT stored procedures are NEVER updatable. If you have middle-ware
> smart enough to derive the corresponding UPDATE or INSERT statement,
> great, but I don't think Access the way you describe it is one of
> them.
>
> What is wrong with linked tables/views?
> What is unsafe about ADP?
>
> -Tom.
> Microsoft Access MVP
>
>
> >Although I'd rather stick with the ADP format when writing applications that
> >use Access as a front-end to SQL Server, just to be safe I'm trying to work
> >out how to talk to SQL Server from an Access 2007 ACCDB file. My question is,
> >can all of the following requirements be met simultaneously (especially using
> >bound forms and an updateable recordset)?
> >
> >1) Access ACCDB talking to SQL Server 2005.
> >2) Either ADO or DAO, but preferably ADO.
> >3) Bound forms (but NOT directly to a linked table or view, instead to an
> >ADO or DAO recordset based on parameterized stored procedures).
> >4) The recordset is updateable.
> >
> >I can do all of the above using pass-through queries, except a PTQ isn't
> >updateable. I'd rather not move to an unbound form model because of the
> >substantial increase in coding required. Likewise for doing the development
> >in Visual Studio.
> >
> >Any suggestions re whether this can be done, and if so, how?
>

Thanks for your response, Sylvain.

You hit the nail on the head in your first paragraph, that's exactly what I
want to do--work as much as possible in an ACCDB as I currently do in an ADP.
I'm concerned that ADP's may be going away and am looking at my options if
they do. There are some nice design-time features that are available in
ACCDB's as well that it might be nice to use, assuming the underlying data
access works OK.

I hadn't considered using a disconnected ADO recordset and trying to bind
that--I'll take a look and see if it makes sense. But after a couple more
days investigating this it's looking like I'll just have to give up bound
forms outside of ADP file.


"Sylvain Lafontaine" wrote:

> What you are describing is to work the same way as with an ADP project but
> with using an ACCDB database file instead of an ADP project. If it was
> possible to use an ACCDB database file against an SQL-Server in exactly the
> same way as with an ADP project, I don't know what would be the usefulness
> of ADP in this (hypothetical) context.
>
> With ADO, you can create an ADO recordset from a SP, disconnect it and then
> connect it to a form but I don't remember if this form will be updatable or
> if it will be read-only. You can try it, you'll see the answer by yourself.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "PGallez" <(E-Mail Removed)> wrote in message
> news:924CB492-9D39-4650-85CB-(E-Mail Removed)...
> > Although I'd rather stick with the ADP format when writing applications
> > that
> > use Access as a front-end to SQL Server, just to be safe I'm trying to
> > work
> > out how to talk to SQL Server from an Access 2007 ACCDB file. My question
> > is,
> > can all of the following requirements be met simultaneously (especially
> > using
> > bound forms and an updateable recordset)?
> >
> > 1) Access ACCDB talking to SQL Server 2005.
> > 2) Either ADO or DAO, but preferably ADO.
> > 3) Bound forms (but NOT directly to a linked table or view, instead to an
> > ADO or DAO recordset based on parameterized stored procedures).
> > 4) The recordset is updateable.
> >
> > I can do all of the above using pass-through queries, except a PTQ isn't
> > updateable. I'd rather not move to an unbound form model because of the
> > substantial increase in coding required. Likewise for doing the
> > development
> > in Visual Studio.
> >
> > Any suggestions re whether this can be done, and if so, how?
>
>
>

Using unbound forms is not so bad. Your biggest problem here is not with
the forms but with reports: you cannot have subreport linked to a passtrough
query when using an ACCDB database file. The only way to connect a subreport
to a stored procedure that I know of is with ADP. Of course, you can try to
solve this without using ADP by putting all the data you need in the query
source (the stored procedure) of the main report and adapt the design of
your report or to use temporary tables (either locally or on the server) but
at this point, we are far from the "simplicity" of using an ACCDB database
file against SQL-Server.

As for the future of ADP, I don't think that they are going away per see or
in the near future. I would very surprised if they were to drop the
functionality of ADP without integrating it first into the JET engine/ACCDB
database file format.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"PGallez" <(E-Mail Removed)> wrote in message
news:B4FB66D4-D9C0-4CCD-AF69-(E-Mail Removed)...
> Thanks for your response, Sylvain.
>
> You hit the nail on the head in your first paragraph, that's exactly what
> I
> want to do--work as much as possible in an ACCDB as I currently do in an
> ADP.
> I'm concerned that ADP's may be going away and am looking at my options if
> they do. There are some nice design-time features that are available in
> ACCDB's as well that it might be nice to use, assuming the underlying data
> access works OK.
>
> I hadn't considered using a disconnected ADO recordset and trying to bind
> that--I'll take a look and see if it makes sense. But after a couple more
> days investigating this it's looking like I'll just have to give up bound
> forms outside of ADP file.
>
>
> "Sylvain Lafontaine" wrote:
>
>> What you are describing is to work the same way as with an ADP project
>> but
>> with using an ACCDB database file instead of an ADP project. If it was
>> possible to use an ACCDB database file against an SQL-Server in exactly
>> the
>> same way as with an ADP project, I don't know what would be the
>> usefulness
>> of ADP in this (hypothetical) context.
>>
>> With ADO, you can create an ADO recordset from a SP, disconnect it and
>> then
>> connect it to a form but I don't remember if this form will be updatable
>> or
>> if it will be read-only. You can try it, you'll see the answer by
>> yourself.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
>> please)
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "PGallez" <(E-Mail Removed)> wrote in message
>> news:924CB492-9D39-4650-85CB-(E-Mail Removed)...
>> > Although I'd rather stick with the ADP format when writing applications
>> > that
>> > use Access as a front-end to SQL Server, just to be safe I'm trying to
>> > work
>> > out how to talk to SQL Server from an Access 2007 ACCDB file. My
>> > question
>> > is,
>> > can all of the following requirements be met simultaneously (especially
>> > using
>> > bound forms and an updateable recordset)?
>> >
>> > 1) Access ACCDB talking to SQL Server 2005.
>> > 2) Either ADO or DAO, but preferably ADO.
>> > 3) Bound forms (but NOT directly to a linked table or view, instead to
>> > an
>> > ADO or DAO recordset based on parameterized stored procedures).
>> > 4) The recordset is updateable.
>> >
>> > I can do all of the above using pass-through queries, except a PTQ
>> > isn't
>> > updateable. I'd rather not move to an unbound form model because of the
>> > substantial increase in coding required. Likewise for doing the
>> > development
>> > in Visual Studio.
>> >
>> > Any suggestions re whether this can be done, and if so, how?
>>
>>
>>

Thanks for the light up on the fact that you can bind a form to an ADODB
recordset and still have it updatable. I keep forgetting it every few
months. Did you guy had the time to check the same with subforms and
subreports?

As for your use of local tables, it's hard to give you any advice or opinion
on that matter without knowing what you want to do with those local tables.
BTW, you can also have local tables with an ADP project, all you have to do
is to have a local ACCDB database file and access it with ADO.

An even better idea would probably be to have a local installation of
SQL-Server Express and use it not only for the storage of local tables but
also as the main portal to the data by implementing a synchronisation
process with it and the central server (or a cloud storage).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Mark_David_Edwards" <u52630@uwe> wrote in message news:97b6ac4304188@uwe...
>A group of developers I'm working with just had a good, indepth discussion
>on
> wether to use a .adp or a .accdb for SQL Server development. Our
> requirements were:
>
> Server-side processing instead of client side.
> Bound, updatable forms.
> Would like to have local tables, if possible (VERY usefull).
>
> We decided to go with the .accdb after one member showed us how to create
> and
> open an ADODB recordset (dynamic and updatable) in a .accdb file and set
> the
> form's recordset property to the ADODB recordset. IT WORKED! Together
> with
> either native or ActiveX listboxes/listviews/comboboxes, we were able to
> accomplish our requirements. Looks and works like a regular .accdb file
> to
> the user - but we have both server-side processing AND local tables!! Wooo
> Whooo!
>
> This has greatly reduced my need and desire to use .adp files when using
> Access front-end with SQL Server back-end.
>
> I would like to hear other methods of accomplishing the same goals.
>
> p.s. I see a lot of hullabaloo about specific, nitpicky technologies that
> are just 6-of-1 compared to a half-dozen-of-another, without any serious
> differences in core issues. Took me awhile to cut thru all the "stuff".
>
> Sylvain Lafontaine wrote:
>>Using unbound forms is not so bad. Your biggest problem here is not with
>>the forms but with reports: you cannot have subreport linked to a
>>passtrough
>>query when using an ACCDB database file. The only way to connect a
>>subreport
>>to a stored procedure that I know of is with ADP. Of course, you can try
>>to
>>solve this without using ADP by putting all the data you need in the query
>>source (the stored procedure) of the main report and adapt the design of
>>your report or to use temporary tables (either locally or on the server)
>>but
>>at this point, we are far from the "simplicity" of using an ACCDB database
>>file against SQL-Server.
>>
>>As for the future of ADP, I don't think that they are going away per see
>>or
>>in the near future. I would very surprised if they were to drop the
>>functionality of ADP without integrating it first into the JET
>>engine/ACCDB
>>database file format.
>>
>>> Thanks for your response, Sylvain.
>>>
>>[quoted text clipped - 54 lines]
>>>> >
>>>> > Any suggestions re whether this can be done, and if so, how?
>

Good référence material about what? For SQL-Server, look for books about
SQL-Server and not for books about Access with one or two chapters about
SQL-Server.

For bound forms and VBA, it doesn't really matter if you work against a JET
database or with ODBC Linked Tables; there are a few differences but they
are minime.

For ADP, excerpt for the type of ther record source and the way of passing
parameters, the theory for the forms and reports is about the same as for
the forms and reports on an ordinary ACCDB database file with a few
differences here and there.

Finally, for unbound forms, any good book on ADO will cut the deal.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Mark_David_Edwards via AccessMonster.com" <u52630@uwe> wrote in message
news:97c2bcda18b51@uwe...
> Know of any good reference material I can lay my hands on that covers
> these
> little goodies? Every book I look at in the book store starts out:
>
> "This is a table... it holds data... This is a form... you can put
> controls
> on it and bind it to a data source... But don't ask us for much more than
> the
> basics, because we don't cover anything else...."
>
> Magazines are the same way:
>
> "100 Ways to Speed Up Your Computer - (Same 100 ways we told you about
> last
> year about this time)... Next month, the 10 Best Computers We Tested
> (made
> by the companies who paid us the most to mention their junk in this
> magazine).
> ."
>
> You get the picture. Is there an "advanced" book out there somewhere? If
> not, I'm going to write one! So, please, everyone send me your tips and
> tricks and techniques - or point me to a GOOD source of them - and I'll
> get
> it done!
>
> Sylvain Lafontaine wrote:
>>Thanks for the light up on the fact that you can bind a form to an ADODB
>>recordset and still have it updatable. I keep forgetting it every few
>>months. Did you guy had the time to check the same with subforms and
>>subreports?
>>
>>As for your use of local tables, it's hard to give you any advice or
>>opinion
>>on that matter without knowing what you want to do with those local
>>tables.
>>BTW, you can also have local tables with an ADP project, all you have to
>>do
>>is to have a local ACCDB database file and access it with ADO.
>>
>>An even better idea would probably be to have a local installation of
>>SQL-Server Express and use it not only for the storage of local tables but
>>also as the main portal to the data by implementing a synchronisation
>>process with it and the central server (or a cloud storage).
>>
>>>A group of developers I'm working with just had a good, indepth
>>>discussion
>>>on
>>[quoted text clipped - 52 lines]
>>>>>> >
>>>>>> > Any suggestions re whether this can be done, and if so, how?
>
> --
> Message posted via http://www.accessmonster.com
>

I was using MDB for years, and about 3 years ago I got a new job. The new
job involves nearly all ADP work.
I've bought loads of books to find 'best practice' for using ADP and SQL
Server, but not one had everything I wanted.
I think the best was "Microsoft Access Developers Guide to SQL Server"
http://search.barnesandnoble.com/Mic.../9780672319440

But its a bit out of date by now, has no reference to Access 2007. Any
Access 2007 book I've seen so far haven't been great.
I wish someone would write a decent one. Or let me know of a good book!

With regards to local tables, I personally would like them in Access.
Since the future of ADP is a bit uncertain, I've often considered switching
formats.
Planning to upgrade to SQL Server 2008 this year. I won't be able to change
the design of any SQL objects from within ADP, which means that ADP loses
one of its main advantages.
Is there any reason not to use ACCDB?


"Mark_David_Edwards via AccessMonster.com" <u52630@uwe> wrote in message
news:97c2bcda18b51@uwe...
> Know of any good reference material I can lay my hands on that covers
> these
> little goodies? Every book I look at in the book store starts out:
>
> "This is a table... it holds data... This is a form... you can put
> controls
> on it and bind it to a data source... But don't ask us for much more than
> the
> basics, because we don't cover anything else...."
>
> Magazines are the same way:
>
> "100 Ways to Speed Up Your Computer - (Same 100 ways we told you about
> last
> year about this time)... Next month, the 10 Best Computers We Tested
> (made
> by the companies who paid us the most to mention their junk in this
> magazine).
> ."
>
> You get the picture. Is there an "advanced" book out there somewhere? If
> not, I'm going to write one! So, please, everyone send me your tips and
> tricks and techniques - or point me to a GOOD source of them - and I'll
> get
> it done!
>
> Sylvain Lafontaine wrote:
>>Thanks for the light up on the fact that you can bind a form to an ADODB
>>recordset and still have it updatable. I keep forgetting it every few
>>months. Did you guy had the time to check the same with subforms and
>>subreports?
>>
>>As for your use of local tables, it's hard to give you any advice or
>>opinion
>>on that matter without knowing what you want to do with those local
>>tables.
>>BTW, you can also have local tables with an ADP project, all you have to
>>do
>>is to have a local ACCDB database file and access it with ADO.
>>
>>An even better idea would probably be to have a local installation of
>>SQL-Server Express and use it not only for the storage of local tables but
>>also as the main portal to the data by implementing a synchronisation
>>process with it and the central server (or a cloud storage).
>>
>>>A group of developers I'm working with just had a good, indepth
>>>discussion
>>>on
>>[quoted text clipped - 52 lines]
>>>>>> >
>>>>>> > Any suggestions re whether this can be done, and if so, how?
>
> --
> Message posted via http://www.accessmonster.com
>

"Mark_David_Edwards via AccessMonster.com" <u52630@uwe> wrote in message
news:97cf35f3f5fd1@uwe...
> It would be nice if someone had answers to some basic questions about
> using
> SQL Server with .accdb/.mdb like:
> - How do you easily get server-side processed data sets into controls like
> listboxes & comboboxes.
> - How do you easily get updatable, dynamic, scrollable server-side
> processed
> data sets into forms & subforms.

Hum, this doesn't look exactly like what you've said in a previous message:

« A group of developers I'm working with just had a good, in depth
discussion on
wether to use a .adp or a .accdb for SQL Server development. ...

We decided to go with the .accdb after one member showed us how to create
and
open an ADODB recordset (dynamic and updatable) in a .accdb file and set the
form's recordset property to the ADODB recordset. IT WORKED! Together with
either native or ActiveX listboxes/listviews/comboboxes, we were able to
accomplish our requirements. Looks and works like a regular .accdb file to
the user - but we have both server-side processing AND local tables!! Wooo
Whooo! »

What's the problem with this solution? It looks fine to me.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)