Hi Everyone,

I am just wanting to ask peoples opinions on using MS Access with a
database server as the BE. I have done my own things with this before
but never actually asked what other people do. My primary issue has
always been keeping forms up-to-date with data changes in the BE. I
have always felt that my approach is a little 'clunky' - doing
periodic refreshes on passthrough queries into a recordset - and was
wondering what other peoples experiences are?

I ask because I am about to design a relatively simple FE for talking
with an Oracle BE. I was thinking of writing the entire FE using ADO,
but it was also suggested to me that perhaps linked tables (ODBC /
DSN) would be better for the forms and passthrough's from code for
data manipulation and returning results for reports.

The application will remain at the departmental level and so by policy
must be built in(to) an MS Office application, so no web interfaces,
no Java, no nothing but MS Office. Good thing I am comfy with
Access :-)

I am curious to hear other ideas and experiences.

Looking forward to hearing from you.

The Frog

Hi

I have just had to synchronise 10 legacy Access databases with 1 new
centralised database without disrupting the various teams working
practices.

So far it has worked quite well. Don' know what will happen when we get
12,000 records in it.

I basically use a single table to collect all client contact details. Each
team has the ability to take ownership of a client and these are then
available in their local team database. It needed an agreement from each
team as to what the common table would hold because everyone would use the
same data. By linking the central database to the teams DB referral section
each team could then record separate info depending on what work they were
involved with.

The teams are scattered across 10 different sites and access the database
through Terminal Services.

Hope this is of use.

Phil

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:d3c7f6d1-c9d6-42d9-9a49-b657a194bcfc (AT) v16g2000vbq (DOT) googlegroups.co
m:

Going unbound means you shouldn't be using Access.

Use ODBC linked tables and build the app the same way you would with
a Jet/ACE back end. Then adjust as necessary for performance and
features (e.g., to use sprocs for inserting new records).

My apps with server back ends are all upsized. I'm not sure I'd make
any different design decisions if I were designing from scratch,
though. Certainly the idea of interacting with the database only in
code is completely repulsive to me. It's so anti-Access that it
makes me want to slap anybody who suggest is.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Thu, 24 Feb 2011 04:27:36 -0800 (PST), The Frog
<mr.frog.to.you (AT) googlemail (DOT) com> wrote:

Don't use DSNs.

I much prefer DSN-Less connections as it is one less thing for someone
to have to configure and one less thing for the users to screw up.
This is also better for Citrix/TS farms where each individual system
would have to have a DSN created and maintained.

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafa...m&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.carlprothman.net/Default.aspx?tabid=81
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

If Microsoft had not scuttled "real" Visual Basic in favor of Visual Fred
(aka VB.NET), I'd say you might as well spend 3 - 5 times as long (if all
goes well, more if it doesn't) and that many times more effort to write it
in VB. The same ratio will apply if you use unbound forms and ADO in
Access.

ODBC (with or without DSN) linked tables in the Oracle DB is your best
bet... it won't be a lot different than doing a well-designed multiuser
application with a split Jet or ACE database. That's what the Access team
now recommends as best practice.

Larry Linson
Microsoft Office Access MVP

Thanks for the feedback guys, I appreciate it. In the past I have done
the ODBC (albeit with DSN's) linking the tables of the BE and pumping
data back through ADO / DAO passthrough queries. It has worked well to
date and I see that it is what more experienced developers than myself
are doing too.

David, I take your point. I was thinking along these lines coming from
a Java point of view (ie/ just build an app to do the job) and
'translating' into what I can actually do inside the department. This
approach ignores the advantages that Access provides and Larry's time
estimate is probably quite accurate.

It is good to know that the linked table approach is considered best
practice. I am probably going to have to stay with DSN's for this one
as setting up a connection to Oracle is not a trivial task for a
novice and a DSN can be administered by our 'IT Group' (those same
really unhelpful people!). Users will just need to know the name of
the DSN, their username and password - the app will remember the DSN,
do the relinking etc... and then they just need their Username and
password.

Out of curiosity, how would you handle a multi BE linked table setup?
I was giving this some thought as I have a few multi mdb BE setups but
have never attempted it with a client / server setup. I dont actually
need it, just curious how you could manage it. Same maybe as Dev
Ashish has on his ODBC relinking page? (keep a table with the details
to act like a map of what goes where)

Similarly, has anyone had to change server BE from one type to
another, eg/ SQL Server to Oracle, MySQL to PostGres. I did this once,
migrating an SQL Server 7 BE to Firebird (not my choice - I dont like
Fried-bird at all), and it was tedious. Not impossible, but tedious.
Probably not a common scenario I would expect though.

Cheers (and thanks)

The Frog

Per Access Developer:
My experience - rewriting an already-developed Access app using
VB6 was 3x.

Would you say that VB.NET would take more than that or less -
assuming an experienced .NET developer.
--
PeteCresswell

On Feb 24, 9:28*pm, "David-W-Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
I still do a lot of unbound with Access. I think a slap is
unwarranted :-). For me, unbound Access forms do a better job when
things must be scaled up to something besides an Access frontend.
Unbound is certainly part of Access. Plus, most of the data I edit
doesn't require conflict resolution at all. Bound forms have some
great advantages, but don't work optimally on an inexpensive LAN when
the number of simultaneous users gets high, even with a SQL Server
backend (definitely safer though). I agree that validation at the
database level is better than with code. With a SQL Server backend,
you definitely want to take advantages of what SQL Server has to offer
at some point, such as data triggers, database level validation rules
and general SQL Server optimizations. Using bound forms in Access
might prepare the developer for data binding in, say, ASP.NET, but
data binding is not the only option for up-sizing. I'm not even sure
that an internet app using data binding to SQL Server in a high
performance situation will work out. Sometimes I wonder what they
were thinking when they thought data binding on an internet app would
be useful when high volume is required. I'll be testing out some .NET
software patterns soon, and many of those patterns use data binding,
so I expect to be able to test the performance myself. Perhaps some
real performance data will change my mind. We'll see. I, for one, am
glad that unbound forms are available in Access.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Per James A. Fortune:
And, if the app is hooked into a DAO back end, unbound forms are
more scalable when it comes to cross-field/cross-record
validation. Users think up new and wonderful things, they can
be implemented in a more straightforward manner.
--
PeteCresswell

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

Umm. Why do you "pump data back through ADO/DAO passthrough queries"? In
general, bound forms work very nicely indeed with linked tables in
ODBC-compliant servers.

The reasoning given for unbound forms really doesn't make sense...
"upscaling from Access to a different front-end"? That's not "upscaling";
that's, at least, a reimplementation... you pay the price _twice_, once when
you implement with unbound forms, and again when you rewrite in whatever
other language/tool to which you "upscale".

I mean, I've only worked with Access since January 1993, so Jim may have a
couple of months of experience on me, but the only thing similar to what he
describes that I've seen is implementing a web-based application that uses
similar data -- user interaction in an Access LAN / WAN enviroment and in a
web-based environment is sufficiently different that it was, indeed, neither
an upscaling nor a reimplmentation, but a new project entirely. And, I'd
surely have paid a high price over those years if I'd done much with unbound
forms on the expectation of future upscaling.

It has been rare indeed that I ever had a need to use an unbound form since
I learned how Access is supposed to, and does, work with bound forms. I've
worked with some that were implemented by others on projects where I was
enhancing existing databases -- most of those were done by "refugees from
VB" who'd been drafted to work on an Access project because, "after all, the
development tools are almost identical" (which anyone who's worked with both
Access and classic VB knows to be only a superficial resemblance).

Oh, I saw one case of some folks trying to "upscale" from Access to
PowerBuilder, but as time dragged on and expenses mounted -- and they
finally took a realistic look at possible benefits, their management
cancelled the project, tucked their tails between their legs, and hoped that
they could keep a sufficienty low profile that upper management didn't fire
them for wasting corporate resources

And, I'm not even sure what Pete is talking about... if a salesman for a
newer, better development environment / language had said that to me, I'd
have disregarded it as "BS and bafflegab".

I'd re-link in code... IIRC, there's a good example (still should work
despite originally being done several Access versions ago) in the Developer
Solutions database (I hope they haven't succumbed to the temptation to
remove it from microsoft.com -- they moved it around so much that I always
started from the Knowledge Base to find its current location). The
opportunity for human error would be high, I'd think, if you tried to handle
it with the Linked Table Manager because of the number of manual operations
required.

Regards,
Larry Linson
Microsoft Office Access MVP