Converting Application from ADP to ACCDB

[PGallez]

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 van Stiphout]

On Tue, 9 Jun 2009 09:49:01 -0700, PGallez

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

 

[Sylvain Lafontaine]

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]

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.

 

[PGallez]

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]

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)

 

[Sylvain Lafontaine]

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)

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

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?

 

[Sylvain Lafontaine]

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)

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!

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?

 

[SimeonD]

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/Mi...uide-to-SQL-Server/Andy-Baron/e/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?

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!

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?

 

[Sylvain Lafontaine]

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)

 

[Sylvain Lafontaine]

Hum, this doesn't work with an ADO recordset? Then try to set the recordset
of the control to a DAO recordset instead or build a string to be used as
the RowSource of the control; see:

http://www.tek-tips.com/viewthread.cfm?qid=1280174&page=8

Also, I don't see why you want to use a recordset instead of a querydef with
a passthrough query for your controls. Passthrough queries are read-only
but listboxes and comboxes are themselves not updatable, so this changes
nothing.

--
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)

It works for attaching an ADODB recordset to a form, but can you do the
same
thing to a normal listbox or combobox? (The listview control works for a
listbox, but is not easy to use). What I want to do in code is:

set rst=ADODB.recordset
..
..
..
rst.Open()
lstListbox.rowsource = rst
cboCombobox.rowsource = rst

Can this be done? Have I missed something?

It would be nice if someone had answers to some basic questions about
using

[quoted text clipped - 4 lines]

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]

True. I guess I'm looking for the best of all worlds here. Since I
specialize in the design and fabrication of data analysis and reporting
applications in Access for executives, it is very important to me to meet
the
following criteria as best I can.

- Use server-side processing when pulling data from SQL Server as a
back-end.

This can be done with ODBC Linked Tables if your QueryDef's queries are not
too complexe or with linked Views or by directly creating a DAO recordset.

- Have a multi-user front-end file on the server that several executives
can
use simultaneously, which is what they want (maintaining a copy on their
hard
drive is "too hard and too confusing"...).

Bad idea with either ACCDB, MDB or ADP file. Use an Autoupdater; the one
from Tony Toews is free: http://www.granite.ab.ca/access/autofe.htm . You
could also set up a batch file to check for the latest version.

- Handle and process user-created queries on the fly (different SELECT
fields,
tables, joins, etc.).

For security reasons and simplicity, it's better to put the logic directly
into a SP wherever possible.

- Have local tables available for special, personal, uses.

Nothing forbid you to have the temporary tables directly on the server;
however, if you really want to, you can also have an independant local
ACCDB/MDB file or an installation of SQL-Express or even SQL-Compact.

In order to do this, I have to use ADO as much as possible to get the
server-
side processing and avoid using querydefs since you can't change the SQL
string in them without every user that is using the same querydef being
affected by the change. (If one user modifies the SQL string in a
querydef,
then EVERY USER gets the modification, and their recordset changes in
front
of their eyes - like magic!)

If you want to change the sql string of a querydef, it's a much better idea
to use anonymous querydefs instead. Using them won't affect the other users
and will also prevent the bloating of the ACCDB/MDB database file.

However, I've "discovered" that you can set .recordsource and .rowsource
properties to SQL strings without affecting other users of the file, so a
user can load forms and controls with SQL strings on the fly WITHOUT
affecting any other user in the application - and without having to use
querydefs.

Discovering that you could load a form's .recordset property with an open
ADO
recordset and get an updatable form was a tremendous help! Now I want to
be
able to do this for subforms and controls with server-side ADO processing
too!

Subreports are much more problematic than subforms. Don't you have any
reports in your application?

I've tried setting the .rowsource property of listboxes and comboboxes to
ADO
recordsets, but it doesn't work... (do these controls have .recordset
properties too?)

You can't. However, if I remember correctly, you can set their recordset
property to a DAO recordset. (A DAO recordset, not an ADO recordset.).

Any ideas? (I anyone out there knows of a set of tools that someone has
developed to do this, you have a customer!)

It's already done and available right now: it's ADP. A second possibility
would be to switch to .NET; either with WinForms or with WebForms (ASP.NET).

--
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)