Access Corruption / MySQL

[Mike]

I recently had a Microsoft Access 97 database corrupt on me. The
setup is a split database (front end/back end) setup with about 10-15
users. This is the first corruption in something like a year. The
database has about 25 tables, and the total size is about 15MB, after
a compact. The front end sits on users Windows 2000 Professional
desktops, and the file server is a Windows 2000 Server.

I've been thinking of migrating the back end, but because of cost,
I've been thinking about using MySQL (www.mysql.com) and using linked
ODBC tables from within the front end.

I have a few questions..

It is my understanding that when an Access97 front end queries an
Access backend, it really just pulls all of the needed data accross
the network, runs the query, and displays what is needed. For
example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:

SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"

The front end would get ALL of the rows from EMPLOYEE via the network,
and then perform the SELECT. Is my understanding correct?

Secondly, if I am correct, will using MySQL stop this from happening
if I use linked ODBC tables? I'd rather not rewrite the entire front
end to use ADO.

Thoughts?

--
Mike Loll

[Peter Miller]

Mike,

On 20 Nov 2003 07:06:28 -0800, mi*********@hotmail.com (Mike) wrote in
comp.databases.ms-access:

I've been thinking of migrating the back end, but because of cost,
I've been thinking about using MySQL (www.mysql.com) and using linked
ODBC tables from within the front end.

I have a few questions..

It is my understanding that when an Access97 front end queries an
Access backend, it really just pulls all of the needed data accross
the network, runs the query, and displays what is needed. For
example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:

SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"

The front end would get ALL of the rows from EMPLOYEE via the network,
and then perform the SELECT. Is my understanding correct?
Yes, kind of.

Although JEt does need to pull 100% of what it needs over the wire,
and absolutely no processing occurs on the file server, what jet needs
is not at all the complete set of rows from your table. Your table is
presumably indexed. Jet will check the table definition, find the
applicable index definitions, bring over the necessary index pages
(which does not at all mean all index pages), find the location of the
appropriate data page(s) with the target data, then bring back those
small data pages that contain your desired records. The result is a
lot less i/o than you might think, but still, 100% of the data
processing occurs on each and every client pc that uses the db - not
on the file server.
Secondly, if I am correct, will using MySQL stop this from happening
if I use linked ODBC tables? I'd rather not rewrite the entire front
end to use ADO.

In your example case, yes, all the work is done by mysql on the
server, and only the correct rows returned over the wire.

But that said, there are cases where jet will pull down much more
data, even from a server rdbms. For instance, if you attempted to
draw data from a query that referenced two tables, one on the mysql
server and one local, or on from a different rdbms, then Jet has to
resolve the query, and mysql can't do all the work for you. In a case
like that, it's back to a similar scenario as a jet-only
implementation.

But in general, a server rdbms will serve you well, and mysql is fine.
A lot of people prefer postgresql (I'm one of them) because the
feature set is more comprehensive, but it all depends what your needs
are. For large dataset olap type projects, mysql has an edge over
postgresql. For full service needs, and as a general replacement for
jet across the board, postgresql is better.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Mike]

> But in general, a server rdbms will serve you well, and mysql is fine.

A lot of people prefer postgresql (I'm one of them) because the
feature set is more comprehensive, but it all depends what your needs
are. For large dataset olap type projects, mysql has an edge over
postgresql. For full service needs, and as a general replacement for
jet across the board, postgresql is better.

What's the status of postgresql on win32? Is it stable? The reason I
ask is because I read the win32 faq on the web site and they listed
some things with running it under cygwin about pipes being insecure or
something. Do you use in for production work on win32?

--
Mike
mi*********@hotmail.com

[Peter Miller]

On 20 Nov 2003 19:09:27 -0800, mi*********@hotmail.com (Mike) wrote in
comp.databases.ms-access:

What's the status of postgresql on win32? Is it stable? The reason I
ask is because I read the win32 faq on the web site and they listed
some things with running it under cygwin about pipes being insecure or
something. Do you use in for production work on win32?

No. I run postgresql from a linux box. Cygwin's fine for a
development machine, but I don't like suggesting it as a requirement
for a production environment.
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Steve Jorgensen]

On 20 Nov 2003 07:06:28 -0800, mi*********@hotmail.com (Mike) wrote:

I recently had a Microsoft Access 97 database corrupt on me. The
setup is a split database (front end/back end) setup with about 10-15
users. This is the first corruption in something like a year. The
database has about 25 tables, and the total size is about 15MB, after
a compact. The front end sits on users Windows 2000 Professional
desktops, and the file server is a Windows 2000 Server.

I've been thinking of migrating the back end, but because of cost,
I've been thinking about using MySQL (www.mysql.com) and using linked
ODBC tables from within the front end.

I have a few questions..

It is my understanding that when an Access97 front end queries an
Access backend, it really just pulls all of the needed data accross
the network, runs the query, and displays what is needed. For
example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:

SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"

The front end would get ALL of the rows from EMPLOYEE via the network,
and then perform the SELECT. Is my understanding correct?

Secondly, if I am correct, will using MySQL stop this from happening
if I use linked ODBC tables? I'd rather not rewrite the entire front
end to use ADO.

Thoughts?

To add to what Peter said, I've been told that Interbase makes a wonderful
server back-end for Access, and is much easier to administer than PostgreSQL,
though I have yet to try it personally. Unlike PostgreSQL, it can run in
native Windows (PostgreSQL will do that soon I'm told, but not yet), and it
has a more complete ANSI SQL implementation than MySQL. There is another fork
of Interbase called Firebird you might also want to look into. Interbase and
Firebird are both free.

Having set up PostgreSQL on Windows before, I'll say that it is not for the
faint of heart, and although it can be made secure on Windows, that takes much
more work.

[R Baumann]

Interbase/Firebird is an excellent server. Interbase is Borland's
commercial version, and Firebird is the Open Source free version. A lot of
the problems with the original Interbase were fixed with Firebird, and it is
under active development. It provides much the same functionallity as
MSDE/SQL Server such as views, referential integrity, etc.. Check it out.

Ruben Baumann

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kd********************************@4ax.com...

On 20 Nov 2003 07:06:28 -0800, mi*********@hotmail.com (Mike) wrote:

I recently had a Microsoft Access 97 database corrupt on me. The
setup is a split database (front end/back end) setup with about 10-15
users. This is the first corruption in something like a year. The
database has about 25 tables, and the total size is about 15MB, after
a compact. The front end sits on users Windows 2000 Professional
desktops, and the file server is a Windows 2000 Server.

I've been thinking of migrating the back end, but because of cost,
I've been thinking about using MySQL (www.mysql.com) and using linked
ODBC tables from within the front end.

I have a few questions..

It is my understanding that when an Access97 front end queries an
Access backend, it really just pulls all of the needed data accross
the network, runs the query, and displays what is needed. For
example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:

SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"

The front end would get ALL of the rows from EMPLOYEE via the network,
and then perform the SELECT. Is my understanding correct?

Secondly, if I am correct, will using MySQL stop this from happening
if I use linked ODBC tables? I'd rather not rewrite the entire front
end to use ADO.

Thoughts?
To add to what Peter said, I've been told that Interbase makes a wonderful
server back-end for Access, and is much easier to administer than

PostgreSQL, though I have yet to try it personally. Unlike PostgreSQL, it can run in
native Windows (PostgreSQL will do that soon I'm told, but not yet), and it has a more complete ANSI SQL implementation than MySQL. There is another fork of Interbase called Firebird you might also want to look into. Interbase and Firebird are both free.

Having set up PostgreSQL on Windows before, I'll say that it is not for the faint of heart, and although it can be made secure on Windows, that takes much more work.

[Steve Jorgensen]

I was under the impression that Interbase had been open sourced first, and
that's how Firebird was possible as an open source derivative.

On Fri, 21 Nov 2003 08:26:10 -0800, "R Baumann" <ry**@9yahoo.com> wrote:

Interbase/Firebird is an excellent server. Interbase is Borland's
commercial version, and Firebird is the Open Source free version. A lot of
the problems with the original Interbase were fixed with Firebird, and it is
under active development. It provides much the same functionallity as
MSDE/SQL Server such as views, referential integrity, etc.. Check it out.

Ruben Baumann

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kd********************************@4ax.com.. .

On 20 Nov 2003 07:06:28 -0800, mi*********@hotmail.com (Mike) wrote:

>I recently had a Microsoft Access 97 database corrupt on me. The
>setup is a split database (front end/back end) setup with about 10-15
>users. This is the first corruption in something like a year. The
>database has about 25 tables, and the total size is about 15MB, after
>a compact. The front end sits on users Windows 2000 Professional
>desktops, and the file server is a Windows 2000 Server.
>
>I've been thinking of migrating the back end, but because of cost,
>I've been thinking about using MySQL (www.mysql.com) and using linked
>ODBC tables from within the front end.
>
>I have a few questions..
>
>It is my understanding that when an Access97 front end queries an
>Access backend, it really just pulls all of the needed data accross
>the network, runs the query, and displays what is needed. For
>example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:
>
>SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"
>
>The front end would get ALL of the rows from EMPLOYEE via the network,
>and then perform the SELECT. Is my understanding correct?
>
>Secondly, if I am correct, will using MySQL stop this from happening
>if I use linked ODBC tables? I'd rather not rewrite the entire front
>end to use ADO.
>
>Thoughts?

To add to what Peter said, I've been told that Interbase makes a wonderful
server back-end for Access, and is much easier to administer than

PostgreSQL,

though I have yet to try it personally. Unlike PostgreSQL, it can run in
native Windows (PostgreSQL will do that soon I'm told, but not yet), and

it

has a more complete ANSI SQL implementation than MySQL. There is another

fork

of Interbase called Firebird you might also want to look into. Interbase

and

Firebird are both free.

Having set up PostgreSQL on Windows before, I'll say that it is not for

the

faint of heart, and although it can be made secure on Windows, that takes

much

more work.

[R Baumann]

Your absolutely right Steve. Sorry if my post gave a wrong impression.
Following is a couple of paragraphs from the Firebird website:
http://www.ibphoenix.com

Introduction to Firebird
In August 2000, Borland Software Corp. (formerly known as Inprise)
released the beta version of InterBase 6.0 as open source. The community of
waiting developers and users preferred to establish itself as an
independent, self-regulating team rather than submit to the risks,
conditions and restrictions that the company proposed for community
participation in open source development. A core of developers quickly
formed a project and installed its own source tree on SourceForge. They
liked the Phoenix logo which was to have been ISC's brandmark and adopted
the name "Firebird" for the project.

Because Borland's open source efforts regarding InterBase never really
took off beyond prime release of the source code and the company returned
its focus to closed commercial development, Firebird became THE Open Source
version of InterBase.

For more information, please see the Firebird(IBPhoenix) site.
Firebird doesn't get a lot of press as opposed to Oracle, SQL Server, My
SQL, or PostgreSQL, but it's just as good, and in the case of MySQL, it
already has what MySQL is just now implementing; referential integrity,
triggers, sub-selects, etc.

HTH

Ruben


"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:0s********************************@4ax.com...

I was under the impression that Interbase had been open sourced first, and
that's how Firebird was possible as an open source derivative.

On Fri, 21 Nov 2003 08:26:10 -0800, "R Baumann" <ry**@9yahoo.com> wrote:

Interbase/Firebird is an excellent server. Interbase is Borland's
commercial version, and Firebird is the Open Source free version. A lot ofthe problems with the original Interbase were fixed with Firebird, and it isunder active development. It provides much the same functionallity as
MSDE/SQL Server such as views, referential integrity, etc.. Check it out.
Ruben Baumann

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kd********************************@4ax.com.. .

On 20 Nov 2003 07:06:28 -0800, mi*********@hotmail.com (Mike) wrote:

>I recently had a Microsoft Access 97 database corrupt on me. The
>setup is a split database (front end/back end) setup with about 10-15
>users. This is the first corruption in something like a year. The
>database has about 25 tables, and the total size is about 15MB, after
>a compact. The front end sits on users Windows 2000 Professional
>desktops, and the file server is a Windows 2000 Server.
>
>I've been thinking of migrating the back end, but because of cost,
>I've been thinking about using MySQL (www.mysql.com) and using linked
>ODBC tables from within the front end.
>
>I have a few questions..
>
>It is my understanding that when an Access97 front end queries an
>Access backend, it really just pulls all of the needed data accross
>the network, runs the query, and displays what is needed. For
>example, if I had a table EMPLOYEE(ID, NAME) and I ran the query:
>
>SELECT ID FROM EMPLOYEE WHERE NAME = "John Doe"
>
>The front end would get ALL of the rows from EMPLOYEE via the network,
>and then perform the SELECT. Is my understanding correct?
>
>Secondly, if I am correct, will using MySQL stop this from happening
>if I use linked ODBC tables? I'd rather not rewrite the entire front
>end to use ADO.
>
>Thoughts?

To add to what Peter said, I've been told that Interbase makes a wonderful server back-end for Access, and is much easier to administer than

PostgreSQL,

though I have yet to try it personally. Unlike PostgreSQL, it can run in native Windows (PostgreSQL will do that soon I'm told, but not yet),

andit

has a more complete ANSI SQL implementation than MySQL. There is

anotherfork

of Interbase called Firebird you might also want to look into.

Interbaseand

Firebird are both free.

Having set up PostgreSQL on Windows before, I'll say that it is not for

the

faint of heart, and although it can be made secure on Windows, that

takesmuch

more work.

[Larry Linson]

"Peter Miller" wrote

No. I run postgresql from a linux box.
Cygwin's fine for a development machine,
but I don't like suggesting it as a requirement
for a production environment.

Does anyone keep up with PostgreSQL enough to know the status of the native
Windows version that was under development? I thought it would be available
by now.

Larry Linson
Microsoft Access MVP

[Peter Miller]

On Sun, 23 Nov 2003 00:10:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote in comp.databases.ms-access:

"Peter Miller" wrote

No. I run postgresql from a linux box.
Cygwin's fine for a development machine,
but I don't like suggesting it as a requirement
for a production environment.

Does anyone keep up with PostgreSQL enough to know the status of the native
Windows version that was under development? I thought it would be available
by now.

As I mentioned, I run it from a linux box. I don't know about
progress on a native windows version. I think, as with several other
projects, there's mixed feeling on this by the developers. A native
version would obviously placate those who are unhappy running cygwin
on Windows (as another layer), but it also takes extra resources when
a viable solution for windows already exists. Personally, I don't
know where I fall on this, but I understand projects that make the
'native vs cygwin' decision a second-tier concern.
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[David W. Fenton]

pm*****@pksolutions.com (Peter Miller) wrote in
<ik********************************@4ax.com>:

On Sun, 23 Nov 2003 00:10:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote in comp.databases.ms-access:

"Peter Miller" wrote

> No. I run postgresql from a linux box.
> Cygwin's fine for a development machine,
> but I don't like suggesting it as a requirement
> for a production environment.

Does anyone keep up with PostgreSQL enough to know the status of
the native Windows version that was under development? I thought
it would be available by now.

As I mentioned, I run it from a linux box. I don't know about
progress on a native windows version. I think, as with several
other projects, there's mixed feeling on this by the developers.
A native version would obviously placate those who are unhappy
running cygwin on Windows (as another layer), but it also takes
extra resources when a viable solution for windows already exists.
Personally, I don't know where I fall on this, but I understand
projects that make the 'native vs cygwin' decision a second-tier
concern.

Well, the security problem makes PostgreSQL on Windows unacceptable
for most production apps, I'd think. A native version would
eliminate that shortcoming, presumably.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc