Have a client having problems with a fairly complex Access system
which is split into front end and back end. Using access 2003/mdb
format with Access 2007 software. We are surmising that recent server
problems or connectivity problems may be to blame.

If we converted to MySQL or SQL server as the back end, would that
eliminate the potential for Database corruption that can occur with
Access Jet databases when you lose connectivity, when someone shuts
down their PC with the database active, etc? If so am I correct that
you connect via ODBC rather than using native drivers?

I also read that Jet will process query criteria that it can on the
SQL server before the results are sent back. correct? does this also
happen when you link to MySQL?

A while back I got the book "Microsoft Access Developer's Guide to SQL
Server" - SAMs - Chipman and Baron. As I recall, switching to SQL
server is not for the faint of heart.

Is converting to SQL server or MySQL server the same complexity? Is
one better to use with Access than the other?

Any suggestions for better books or specific threads to read?

Thanks

bob

Bob Alston wrote:
As long as the backend is not affected by the event, then nothing will
happen to the backend beyond incomplete transactions being rolled back.

Correct
It depends. If it is possible to pass the query through to the backend,
it will. Factors that will prevent that include (this list is
incomplete):
- joins or references to local tables
- the use of VBA methods in your queries (Date(), Now(), Iif(), Nz(),
etc.)

Yes
If you have that book, your questions should already be answered ...

I would imagine the same issues that complicate the use of SQL
Server(different SQL dialects, different datatypes, etc.) will also
complicate the use of mysql.
--
HTH,
Bob Barrows

Bob Alston <bobalston (AT) gmail (DOT) com> wrote in
news:bf7641d1-170a-4768-8da5-0e69097ef1b5 (AT) c21g2000vba (DOT) googlegroups.co
m:

[]

What's a "native driver"?

Yes, you use ODBC, but with SQL Server, for instance, the SQL Server
Native ODBC Client is really fast and efficient and reliable. I
don't know about MySQL -- I use an older version of MySQL and its
MyODBC client, and it's perfectely adequate, but my guess is that
all of that has been improved in the last few years.

I think that overstates the level of difficulty. If you understand
how server databases work, there's really only a few things you need
to do when you upsize:

1. clean up your data and schema to work well with your server
database before you upsize. That means things like

a. checking dates for values that are out of range for the target
database

b. evaluating your referential integrity and making it as tight as
possible (e.g., required FK fields are a real performance gain,
since they speed up any LEFT JOINs).

c. revising validation rules to use syntax that can be properly
interpreted by the target database (some valid Access validation
rules can't be upsized to SQL Server, for example).

d. getting rid of all obsolete/redundant/un-necessary tables and
fields.

2. in all your tables, make sure you have a primary key and a
timestamp field.

3. in your code in your Access front end, any time you open a
recordset or .Execute a DML statement, add the dbSeeChanges option.

That's about it!

Every application is different, and some things will run faster and
some things will run slower with a server back end, but in my
experience, it's completely impossible to forecast which accurately.
I almost always assume certain things will be slow and they won't,
and overlook operations that turn out being significant bottlenecks.

But I just don't think it's that hard. But I would allocate a
minimum of 10 hours for planning, testing, front-end revision,
production implementation and followup testing.

Access is designed with operation in a SQL Server environment in
mind, and the SQL Server Migration Assistant for Access is
tailor-made for upsizing Jet/ACE data to SQL Server. I suspect MySQL
offers a tool, but I wouldn't expect it to be as elaborate or robust
as the corresponding SQL Server tools.

MySQL is a good database, but my guess is there's a lot more online
resources for Access developers that are SQL Server-oriented.

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

Davis is very much on the money here. I am using MySQL as a backend a
lot of the time for my development, and there are not so many tool (at
least not that I have found) to assist with the upscaling. On the
other hand, depending on the volume of data and the number of
connections etc... MySQL is an extremely effective option.

When designing an app (or upscaling) I use a combination of ODBC and
'Native' drivers - but the 'Native' drivers can only be used within
code such as ADO / DAO (I use ADO mostly here). What I also do to
speed things up is take all the queries I have in Access (or most of
them anyway) and try to turn them into views on the server. I may not
be as fast as David at migrating an app, so I usually leave a work
week for anything thats a little complex.

I must say that I do enjoy working with MySQL as a backend, and it has
served me extremely well. The only gripe I have with it is the lack of
support for between database replication like SQL Server does. For
mobile / field workers the SQL replication options are IMO unbeatable
- especially Merge Replication, that just rocks. That being said, for
internal based applications or those that can be web interfaced for
external users I use MySQL, and also Ubuntu Server to host it (you
could use the Ubuntu Desktop too, its basically the same product but
includes a graphical interface), and then remote manage the server and
its backups (its a nice touch for the client when you can VPN in and
remote manage the server and its issues (or at least perceived issues)
all while people are still working away). Setting up MySQL on either
Windows or Ubuntu is extremely easy (and Ubuntu is free with the
option of paid support if you need it).

A last note on my 'upscaling' of queries to server views - some people
argue that this is the 'right way' and others the 'wrong way' to deal
with a DB server for an application. This is based on a couple of
premises, all of which I believe are false. Firstly the school of
thought that all interaction should be through views and stored
procedures is IMO garbage as the current editions of most DB server
products have such adequate security control that users cant do what
you dont want them to anyway. Secondly the codebase you maintain can
be thought of as having to be maintained in two places at the same
time, once in the app and the other in the server. I dont see this as
an issue as you have to write the SQL anyway, and on the server it can
be performance tuned very well. Also the server can keep a cache of
whats being queried, or in extreme performance cases you can even load
the entire set of 'reference' data into an in memory database
(catalog / schema) on the same server - it doesnt get quicker than
that. These advantages IMO outweigh the other concerns and IMO are a
much better way to decide on how to implement your chosen path.

Good luck

The Frog

Per Bob Alston:
I've done a few of those conversions and results have been mixed
performance-wise.

The poorest were where the mandate was to just migrate the
tables, change the ODBC links in Access and leave it at that.

The only application I wrote from scratch for SQL server runs
appreciably faster than what I would expect from a .MDB back end.

One thing nobody else seems to mention is the diff between one's
own SQL Server box and having the server run by corporate IT.

In the first case, you have control: need better performance?
Just throw some money at it - in the form of hardware and/or SQL
Server expertise.

In the second case, you can lose control. The app might run
like a champ in your test environment - with SQL Server Express
on the PC two desks over and then go down the toilet in
production as corporate ID puts the DB on a virtual PC in some
box that's getting pounded on by hundreds of other apps.

I've had that problem a couple of times both with SQL Server and
Citrix.
--
PeteCresswell

On Thu, 23 Sep 2010 10:22:09 -0700 (PDT), Bob Alston
<bobalston (AT) gmail (DOT) com> wrote:

You've got some excellent replies. Personally I'd stay with SQL
Server as you are going to get a lot more support. Also the free
version has a maximum database size of 10 Gb, or maybe it's 4 Gb.
Anyhow that should be lots for an upsized Access database. At least
for now. And if the data gets that big well, it's time the org spent
some money.

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/

Tony Toews <ttoews (AT) telusplanet (DOT) net> wrote in
news:ddrs9617v8qotoa9qj8s73nvlcmkddpbo8 (AT) 4ax (DOT) com:

Some things I discovered about SQL Server Express in my first
deployment of it last month:

1. it doesn't include the backup agent. So you have to set up your
own backup. I found a web page that has a whole suite of
SQLCmd-based scripts for this purpose, but can't seem to find it
right now. It's a helluva lot harder than with full SQL Server.

2. there's no SQL Profiler. I downloaded one from here:

http://sites.google.com/site/sqlprofiler/

....it seems to work fine, though it seems more confusing to use than
the SQL Server one (as I have used it in the past). But it does the
job.

These are two things that are really required for working with SQL
Server. In my opinion, that they are not included with SQL Server
Express shows that it's not really intended as a serious production
tool -- it's nothing more than an enticement to force you to buy
full SQL Server and all the licenses.

I was very disappointed in MS when I discovered this, particularly
given how everyone and his dog recommends SQLExpress as such a
perfect replacement for Jet/ACE. It seems to me that the people
making those recommendations have never actually tried it themselves
or they'd be less sanguine about how "simple" it is to swap one for
the other.

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

Davids point here about the usability of SQL Server Express Edition is
quite important. Please note that my comparisons above were not
regarding the Express Edition, but the full server product. This is
one of the reasons I use MySQL quite extensively. You do have most
every full blown server option available to you. Backups are in-built
so to speak, and scheduleable. The whole thing is performance tunable
to the nth degree, and I have found it to be rock stable. There is
also a cluster edition if you need failover (hot or warm as I
understand it) or load balancing. I have never needed to go that far,
even with apps that can server up to three hundred different users at
a time (OK they are not the most complex apps in the world but still,
thats a lot of work for a server made out of 'recycled' hardware, and
it runs quite smoothly with approximately 12Gigs of data across 28
tables and some estimated 22million rows - thats my biggest one so far
(deals with factory shipping data if anyones interested).).

For the sake of losing a few hours I would recommend you give it a
try. Its free, supportable if you want to pay for it, community
supported if you dont want to pay (I've never needed to and I doubt
many people would), and you can even take a course and qualification
in it from Oracle if you feel the need (thinking about that myself). I
use the Windows version on my corporate laptop, and Ubuntu Server
setups for installs to 'clients'. I have seen windows based MySQL
server installs running quite nicely on Win XP / Vista / 7 desktop
OS's as well as the server editions of WIndows.

Just my 2 cents

The Frog

Per The Frog:
Can one write a stored procedure in SQL Anywhere that stacks
several result sets into a single stream?

On my one-and-only SQL-Server-from-scratch application I had a
number of screens that had 5+ subforms on them. Instead of
making 5+ trips to the server, I would run a stored procedure
that sent all five datasets in a single stream and then I would
slice and dice them in the app. Seemed to me that it speeded up
the screen load times significantly.
--
PeteCresswell

Hi Pete,

Sorry to say I am not familiar with Sybase products. Is there perhaps
a forum for these products?

The Frog