Migrating a single user access database app to multi-user multi-site.

[canigou9 (remove your socks to reply)]

(cross posted - comp.databases.ms-access, microsoft.public.access)

Hello folks - this is my first post here, after a short lurk.

I have written an application in Access2002 for a friend's business
which allows him to take orders, arrange deliveries and trace
deliveries back in the event of subsequent complaints.

It uses a handful of tables in a relational schema, with a few
ancillary lookup tables, some forms, reports and VBA modules.
Currently the whole thing runs on a standalone PC.

Now he wants to run a copy in the warehouse which packs and ships the
orders, in a location miles from anywhere and across the country from
the order-taking and customer service office. OK, fine...

I'm thinking that there are two options here:

1) is run a dedicated server in one location or the other, with the
mdb on it, and make the remote warehouse access it remotely... er...
somehow.

Or 2) (more likely), migrate the main tables to an SQL or MySQL server
on the net somewhere, and edit the application such that it accesses
the server in a client/server kinda way. This makes more sense to me
in that he's talking about creating a website further down the line,
allowing customers to order online.

My question is, how easy is it to migrate a relational database app
like this to client/server whilst retaining reasonable performance and
integrity?

Both sites have windowsXP or 2000, Access 2002 and I'm guessing he can
afford to run ADSL at both sites, but any kind of serious expenditure
is out of the question - especially as I'm hoping he might pay me a
little!

Thanks in advance for any advice/pointers.

Charlie

 

[Larry Linson]

If yours is a typical Access-Jet application, there will be some changes for
performance. The good thing is that, if you only have a small user audience,
you can probably use the stripped-down version of SQL Server that comes
_free_ with Access. It is called SQL Server Desktop Edition or Microsoft
Data Engine (MSDE). Don't be misled by "optimized for 5 concurrent users"...
after 5 concurrent internal "batch operations" there are some deliberate
slowdowns, but many have reported satisfactory operation with 25 or more
active users.

As a start, you might take a look at the presentation I did for my user
group about Access as an ODBC client. That will be an easier conversion than
rewriting to Access Data Project (ADP) form, and would, as well, preserve
your option of more easily switching to a different server database should
that become desirable.

You might find it worthwhile to visit MVP Tony Toews' site,
http://www.granite.ab.ca/access/sqlserverupsizing.htm
for his "random thoughts" on this matter. And, just as an aside, take a
look at all the other links and information he has made available on
Access-related subjects starting at http://www.granite.ab.ca/accsmstr.htm.

One primary consideration is that you are now going to try to _minimize_
network traffic by having as much database work as possible done at the
server, so you'll want to avoid opening forms on a table or entire query. It
is amazing, absolutely amazing, just how often you need only one record (if
it exists) or none (if it doesn't) in normal business operations (and
equally amazing how many straight single-user Access programs will open that
same form on the whole table, then locate to the currently-needed record).

Larry Linson
Microsoft Access MVP

 

[canigou9 (remove your socks to reply)]

As a start, you might take a look at the presentation I did for my user
group about Access as an ODBC client. [snip}

Thanks for the pointers, Larry... I'll do some reading tonight!

 

[Albert D. Kallal]

You can read the following notes of mine on using ms-access from a remote
location.

http://www.attcanada.net/~kallal.msn/Wan/Wans.html