How can I launch an Access project on an AS400

[Guest]

I developed an Access XP database to track various accounting info for my
employer. I would like to convert it into an Access project but was told
that I can't do this on an AS400. Our server has multiple drives for sharing
files but I would like multiple users to be able to access the database at
the same time. I am not very familiar with networks and servers so I need to
know if there is a way to create a virtual SQL server within one of our
networks drives? Any advice would be greatly appreciated.

 

[Sylvain Lafontaine]

SQL-Server is a service, not a file; so trying to create something like a
« virtual SQL server on a network drive » doesn't make any sense.

In you case, a better solution would be to split the database into a
Frontend (FE) and a Backend (BE) - using the Database Splitting Wizard - and
give a copy of the FE to each of your user while having a single BE shared
by everyone. This will give you an easy way into having multiple users
accessing the database at the same time.

 

[Guest]

I don't know much about Access Project types, but since you've already got an
Access database built, it'd be a PITA to try to convert IMO. You might want
to consider splitting your database.

When you split a database, you divide it into two parts: a front-end and a
back-end. The back-end contains all of the data and tables, and resides on a
network drive or other accessable location. The front-end contains queries,
forms, reports, etc., and is used to interact and manipulate the data stored
in the back-end. There are clear advantages as to why you would want to do
this, and you can probably write a book from the posts you could find in the
Access Database Design section.

Basically though, Access databases tend to become corrupt if you have too
many people interacting with the same file. So, if you put your database on
the network and have everyone create shortcuts directly to it, chances are
it'll "eat shit" sooner than later. And it will definitely end up corrupting
if you're trying to edit it at the same time. By using a separate front-end
on all of the computers to access the data in the back-end, the only
interaction with the back-end is done by the Jet engine, which deals mainly
in SQL commands.

..... well, I'm not going to re-type every pro and con to splitting (and
there aren't very many cons at all), so you probably should read up on
splitting in the Access Database Design forum. All the whys and why nots and
how-tos are there.

hth

Nick

 

[Larry Linson]

I developed an Access XP database to track various accounting info for my
employer. I would like to convert it into an Access project but was told
that I can't do this on an AS400. Our server has multiple drives for
sharing
files but I would like multiple users to be able to access the database at
the same time. I am not very familiar with networks and servers so I need
to
know if there is a way to create a virtual SQL server within one of our
networks drives? Any advice would be greatly appreciated.

Why would you want to convert your database to an Access project (ADP)? ADPs
only work against a Microsoft SQL Server data store, and you cannot run
Microsoft SQL Server on an AS400... it only runs in a Windows environment.

But, with a little research on the subject, you can find ODBC drivers for
the native database supported by the AS400, create a database with identical
tables on the AS400, remove the tables from the Access database and use it
as a client. You can link the Access tables directly, on your PC, and link
the AS400 tables via ODBC to fill the AS400 tables.

Then, give each user a copy of the Access client on their own PC, link the
tables on the AS400, and that will be a lot less strain and pain than it
would have been to convert to ADP. (BTW, you can use an MDB and ODBC drivers
to connect an Access client to a MS SQL Server database, too -- it'll be a
lot less trouble, and knowledgeable Microsoft insiders now consider it
preferrable to the ADP approach.)

You will very likely have to polish and tweak your Access client for
performance. "A well-implemented single user application, just split and
linked, does not necessarily make a good multiuser application; a good
multiuser application just linked to a server does not necessarily make a
good Access client." You will need to approach your problem in a manner so
as to minimize traffic on the LAN... use Queries or SQL to limit the number
of records retrieved and to limit the number of fields retrieved (both to
just the ones you need), as a start. Keep a connection open to the server by
having a hidden form linked to a dummy table and leave it open.

When you run into specific problems, post again.

Larry Linson
Microsoft Access MVP