Moving an Access database online

[Daven Thrice]

If I have a fairly big Access MDB, that is relational, and has, say, 100
objects (forms, reports, modules, etc.), what is the path to get this
database "online".

Is there a way to put the tables online and then distribute the front end to
each user?

What if I want to have all the forms and stuff online? How do you approach
something like that? Is it a total rewrite with another tool?

Thanks!

Daven

[Trevor Best]

Daven Thrice wrote:

If I have a fairly big Access MDB, that is relational, and has, say, 100
objects (forms, reports, modules, etc.), what is the path to get this
database "online".

Is there a way to put the tables online and then distribute the front end to
each user?

What if I want to have all the forms and stuff online? How do you approach
something like that? Is it a total rewrite with another tool?

Define online, if just a LAN then that's straightforward, split mdb and
run like normal. If you mean remote access from other sites (via
internet) then your choices are limited:

1. Terminal Services or Citrix, remote users run as dumb terminals, all
processing is done locally to the LAN where db resides.

2. Re-write front end in something else, e.g., ASP 3.0, ASP.NET, etc.

3. Replication

The first two give real-time updates and visibility, the third will give
you an ulcer and possibly a full time occupation of resolving conflicts
and no real-time visibility.

--
This sig left intentionally blank

[Peter Russell]

Daven Thrice previously wrote:

*From:* "Daven Thrice" <da*********@NOyahooSPAM.com>
*Date:* Thu, 18 Nov 2004 10:36:28 -0800

If I have a fairly big Access MDB, that is relational, and has, say,
100 objects (forms, reports, modules, etc.), what is the path to get
this database "online".

Is there a way to put the tables online and then distribute the front
end to each user?

What if I want to have all the forms and stuff online? How do you
approach something like that? Is it a total rewrite with another tool?

Thanks!

Daven

What do YOU mean by 'online'?
If you mean 'for people to access via the internet' then yes, you will
have rewrite your front-end completely using web tools such as ASP or
Coldfusion.
If you just want to share the database on a company internal network then
you split the database into front-end and back-end using the built in-
database splitter tool. Put your back-end tables into a shared area and
provide your users with a copy of the front-end which you first need to
have re-linked to the backend using the linked tables manager. There's
more in Help on this option.

Peter Russell

[Daven Thrice]

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me***********************@russellscott.btinte rnet.com...

Daven Thrice previously wrote:

*From:* "Daven Thrice" <da*********@NOyahooSPAM.com>
*Date:* Thu, 18 Nov 2004 10:36:28 -0800

If I have a fairly big Access MDB, that is relational, and has, say,
100 objects (forms, reports, modules, etc.), what is the path to get
this database "online".

Is there a way to put the tables online and then distribute the front
end to each user?

What if I want to have all the forms and stuff online? How do you
approach something like that? Is it a total rewrite with another tool?

Thanks!

Daven
What do YOU mean by 'online'?
If you mean 'for people to access via the internet' then yes, you will
have rewrite your front-end completely using web tools such as ASP or
Coldfusion.

That's what I meant. I'm taking a look at (is it?) mysql and some php tools.

Wow, I've really fallen behind the times!

If you just want to share the database on a company internal network

I know how to do this quite well. Its the Internet stuff I need to know.

Thanks!

Daven

[Daven Thrice]

"Daven Thrice" <da*********@NOyahooSPAM.com> wrote in message
news:_W5nd.123625$cJ3.42402@fed1read06...

If I have a fairly big Access MDB, that is relational, and has, say, 100
objects (forms, reports, modules, etc.), what is the path to get this
database "online".

Is there a way to put the tables online and then distribute the front end
to each user?

What if I want to have all the forms and stuff online? How do you approach
something like that? Is it a total rewrite with another tool?

I've done some looking around. You can connect to a database over the
Internet using ODBC. If you're determined to use microsoft products, you can
use SQL Server. Otherwise, MySQL is a pretty good option.

I found an article on migrating the back end to MySQL and continuing to use
your existing front end. It looks reasonably straight forward. I'll probably
take a shot at something like this over the holiday.

Daven

[Daven Thrice]

Oops, I meant to include the link:

http://www.kitebird.com/articles/access-migrate.html

(I have no affiliation w/ these people, and they ain't sellin' nothin'
anyway.)

[Daven Thrice]

"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote in message
news:vg********************************@4ax.com...

On Sat, 20 Nov 2004 01:02:55 -0800, "Daven Thrice"
<da*********@NOyahooSPAM.com> declaimed the following in
comp.databases.ms-access:

I've done some looking around. You can connect to a database over the
Internet using ODBC. If you're determined to use microsoft products, you
can

But do you really want to?

It means either managing individual user IDs/passwords OR
embedding a common user ID/password into whatever application is being
used for accessing the DBMS.

Compare that to using HTML for the presentation logic only, with
the web-SERVER accessing the DBMS (PHP, PERL, Python, almost anything
that can be run by the server -- even if old, slow, CGI interface is
used). Only one password/User ID, which YOU control at your end.

Possibly faster processing, as only the formatted results are
sent over the link, not raw retrievals.

use SQL Server. Otherwise, MySQL is a pretty good option.

Lots of options: "MaxDB by MySQL" (the former SAP-DB), Firebird
(open source based on Interbase), Ingres is supposed to have gone open.
MySQL's strength is that they don't require paid licenses if an ISP
provides MySQL access on the ISP's servers (that isn't considered
distributing it).

Hi Dennis,

I'm overwhelmed at what's out there. I got away from database for the past
few years because I've been working on embedded stuff. I guess things have
changed a bit! Now that I'm doing databases again, it seems that Access is
not nearly as popular for new development as it was a few years ago.

I have a few old databases that I'd like to migrate to online systems. Some
of them have a lot of custom code in them as well as custom controls I did
in VB, etc. There's also a lot of functionality between these old mdb's and,
for example, MS Outlook.

Other than starting all these old apps anew using some tool/language other
than Access, the only option is to somehow move the backend online. You
can't just move the mdb to your web server (I've tried). You have to go with
a client/server setup, and to me that means either SQL Server or MySql.

I *think* that with MaxDB and Firebird, you are suggesting re-development of
the existing apps, no?

There is a bit of a kicker here because if I move the data to MySql, then I
tie myself into a MySql-type solution. That's not necessarily a bad thing.
If I were to go with SQL Server, I'd be tying myself into (pretty much) a
asp/.net type of solution anyway.

FWIW, I think I'm going to have to pay that $500 MySql licensing fee anyway,
if I move in that direction, because I'll have to implement the MyODBC
driver. All told, its not a bad direction.

Thanks for your input,

Daven

[Daven Thrice]

"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote

On Sat, 20 Nov 2004 09:43:29 -0800, "Daven Thrice"
<da*********@NOyahooSPAM.com> declaimed the following in
comp.databases.ms-access:

I'm overwhelmed at what's out there. I got away from database for the
past
few years because I've been working on embedded stuff. I guess things
have
changed a bit! Now that I'm doing databases again, it seems that Access
is
not nearly as popular for new development as it was a few years ago.
If one want to get really technical, Access is NOT a DBMS --
it's a GUI Forms, Report-Writer, Application Generator using JET as the
DBMS. In the newer versions, using "Access Data Projects" you even dump
JET to use MSDE/SQL-Server.

Access is a great tool for generating for generating a front end. You could
always connect to SQL.

What made it popular was that it was relatively cheap for use as
a single-user DBMS...
I've deployed numerous systems with five or ten users and never had a
problem. Its the right tool for a lot of jobs.

I have a few old databases that I'd like to migrate to online systems.
Some
of them have a lot of custom code in them as well as custom controls I
did
in VB, etc. There's also a lot of functionality between these old mdb's
and,
for example, MS Outlook.

Other than starting all these old apps anew using some tool/language
other
than Access, the only option is to somehow move the backend online. You
can't just move the mdb to your web server (I've tried). You have to go
with
a client/server setup, and to me that means either SQL Server or MySql.

The problem I see (and I might be misunderstanding your
statement) is that you are still tying the clients to being Access
compatible, since all the processing of the forms/reports is on the part
of the application you have to distribute to their machines --

There's nothing wrong with using Access as the front end, so long as there
is no requirement for being able to work with a browser. Also, some of these
apps are pretty complex, and it makes sense to pull the data from a server
and do the processing on the user's PC.

I think this is a reasonable way to upgrade old databases. Should a
requirement be made that the product become browser oriented, you have the
data already out there in a sql environment and you can use php, asp,
whatever, to create a new app. In the meantime, your users are still
plugging away with access, and are no doubt pleased with the speed.
especially if queries do not operate as "pass-thru" (sent "as is" to the
DBMS itself; non-pass-thru could, theoretically, read the entire
database over the network just to find two records matching some
selection criteria).
Pass-through queries just return a snapshot, no? I suppose that is suitable
for reports, but for forms, a dynaset is preferable because it's r/w.

At that point, the DBMS is practically irrelevant
-- anything that the Access front-end can link to (via ODBC) is viable.
{It's masochistic, but one can even read the VFP tables used by "The
Master Genealogist" using ODBC into Access}

I *think* that with MaxDB and Firebird, you are suggesting re-development
of
the existing apps, no?

There is a bit of a kicker here because if I move the data to MySql, then
I
tie myself into a MySql-type solution. That's not necessarily a bad
thing.
If I were to go with SQL Server, I'd be tying myself into (pretty much) a
asp/.net type of solution anyway.

I'd suggest a redevelopment regardless of the final DBMS,
putting the business logic portion onto the server side (stored
procedures in all but MySQL [so far], Application server scripts [CGI,
Zope/Plone, PHP]) and leaving the display rendering to a web-browser

Nice, but that's a lot of work if browser functionality is not required.
Access Basic/VBA is a really powerful language, IMHO. I don't know that all
of the calendar activex's, or my activex controls, will be compatible w/ IE,
much less anything else. Why go to all that trouble if you don't have to?

Thanks again,

Daven

[Daven Thrice]

"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote in message
news:e7********************************@4ax.com...

On Sat, 20 Nov 2004 20:10:52 -0800, "Daven Thrice"
<da*********@NOyahooSPAM.com> declaimed the following in
comp.databases.ms-access:

There's nothing wrong with using Access as the front end, so long as
there
is no requirement for being able to work with a browser. Also, some of
these
apps are pretty complex, and it makes sense to pull the data from a
server
and do the processing on the user's PC.
How wide spread of a network are you looking at here? For a
small LAN, where you have direct access to all the client machines (so
you can ensure the software is installed properly), you may be correct.

I'm not sure if you misunderstood me or if we just disagree. Access is
pretty reliable. On the other hand, I've seen some really crapped-up
installations of Internet Explorer. With all the spyware and stuff out
there, its a lot harder to control than Access.

For something running over Internet distances (even through
VPNs), that makes for a lot of net traffic (which is likely the slowest
part of the equation), along with making it more difficult to control
the proper functioning of the front-end processing (who knows what other
stuff a remote client machine has installed)...

That depends on how the data is handled. The topic of optimizing Access and
her queries for use with MySql is here:

http://dev.mysql.com/doc/mysql/en/Optimize_Access.html

I'm still trying to find my way through all of this, though. My hosting
company has yet to enable MySql & PHP on their server so that I can start
playing around online.

Daven

[Daven Thrice]

"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote in message
news:6m********************************@4ax.com...

On Sun, 21 Nov 2004 14:06:59 -0800, "Daven Thrice"
<da*********@NOyahooSPAM.com> declaimed the following in
comp.databases.ms-access:

I'm not sure if you misunderstood me or if we just disagree. Access is

Different backgrounds -- I've worked in stuff where minimizing
network traffic was crucial (and with an application where it /was/ less
expensive to move a few megabytes of raw data back and forth and doing
local processing over sending X-window display command over an encrypted
network...). Heck, even today, the desktop machines at work are running
on 10base-T... to keep anyone from bogging down the main links (10
desktops at 10Mbps using heavy network traffic add up to a 100Mbps link
from the switch to the main server).

But, but... but I'm talking about having the data only on the server.
Through proper design of queries and so forth, I assssuuume that the only
network traffic will be the data itself.