How should I set up a multiuser database?

[Guest]

I searched under 'multiuser', but I couldn't locate an answer that addressed
my situation (or at least one I understood). I'm new to Access design; my
little experience involves making simple changes to reports and export
queries, and creating a very small 2-table mail logging database. I've
inherited an existing tracking system from a former employee, and I'm the
only one who knows anything about Access, so I've been reading up on database
theory (I understand the basic concepts of normalization).

Somehow (I swear it wasn't me), the design master got corrupted and the
compact and repair option doesn't seem to work; a lot of the tables are all
garbage now. I kinda sorta got it working by brute force - deleting anything
that disagreed with me, but now a lot of our info is inaccessible. I've
learned quite a bit trying to fix it, but Boss Man is impatient and wants me
to scrap it and rebuild from scratch. I also want to have the satisfaction
of learning and creating it myself. Here's the run-down:

Subject: Lots of loans and accompanying info - the old db had about 6-8
different tables organizing this data. At least two tables were imported
from external (non-Access) programs.

Use: Currently 4 simultaneous users, with a need to be able to expand this
up to 12 users. They need to be able to enter data, run (not modify)
reports, and refresh at will (import the external data). I need to be able
to create/modify reports, queries, and macros on demand daily.

Status: I managed to piece together a working version that seemed to have
everything I needed, except I forgot about the multiuser part until the end,
and i don't know how to make it simultaneously available to everyone. Here's
what I have:

switchboard items
tblBorrowerInfoImported - one of the imported tables
report1 - the other imported table
tblChecks
tblChecksSub
tblClosed
tblComments
tblLoanOfficers
tblMainTracking
tblYearlyClosed

queries: about 40 of these

reports: see above

macros: I figgered how to refresh/import/update via macro-linked form buttons.

I've been reading into replication, and i think that's what the old system
used. Will that work for me? Thanks for reading this far,

 

[Guest]

update: I just tried the database splitter wizard, and got two errors:

Subscript out of range
Invalid procedure call or argument

I'm running Access 2000 (9.0.3821 SR-1) on Win2k Pro.

 

[Vincent Johns]

I searched under 'multiuser', but I couldn't locate an answer that addressed
my situation (or at least one I understood). I'm new to Access design; my
little experience involves making simple changes to reports and export
queries, and creating a very small 2-table mail logging database. I've
inherited an existing tracking system from a former employee, and I'm the
only one who knows anything about Access, so I've been reading up on database
theory (I understand the basic concepts of normalization).

Somehow (I swear it wasn't me), the design master got corrupted and the
compact and repair option doesn't seem to work; a lot of the tables are all
garbage now. I kinda sorta got it working by brute force - deleting anything
that disagreed with me, but now a lot of our info is inaccessible. I've
learned quite a bit trying to fix it, but Boss Man is impatient and wants me
to scrap it and rebuild from scratch. I also want to have the satisfaction
of learning and creating it myself. Here's the run-down:

Subject: Lots of loans and accompanying info - the old db had about 6-8
different tables organizing this data. At least two tables were imported
from external (non-Access) programs.

Use: Currently 4 simultaneous users, with a need to be able to expand this
up to 12 users. They need to be able to enter data, run (not modify)
reports, and refresh at will (import the external data). I need to be able
to create/modify reports, queries, and macros on demand daily.

Status: I managed to piece together a working version that seemed to have
everything I needed, except I forgot about the multiuser part until the end,
and i don't know how to make it simultaneously available to everyone. Here's
what I have:

switchboard items
tblBorrowerInfoImported - one of the imported tables
report1 - the other imported table
tblChecks
tblChecksSub
tblClosed
tblComments
tblLoanOfficers
tblMainTracking
tblYearlyClosed

queries: about 40 of these

reports: see above

macros: I figgered how to refresh/import/update via macro-linked form buttons.

I've been reading into replication, and i think that's what the old system
used. Will that work for me? Thanks for reading this far,

 

[Vincent Johns]

I can't address your shared-database questions right now (I hope someone
else will), but I have a couple of suggestions about recovering your data.

I searched under 'multiuser', but I couldn't locate an answer that addressed
my situation (or at least one I understood). I'm new to Access design; my
little experience involves making simple changes to reports and export
queries, and creating a very small 2-table mail logging database. I've
inherited an existing tracking system from a former employee, and I'm the
only one who knows anything about Access, so I've been reading up on database
theory (I understand the basic concepts of normalization).

Somehow (I swear it wasn't me), the design master got corrupted and the
compact and repair option doesn't seem to work; a lot of the tables are all
garbage now. I kinda sorta got it working by brute force - deleting anything
that disagreed with me, but now a lot of our info is inaccessible. I've
learned quite a bit trying to fix it, but Boss Man is impatient and wants me
to scrap it and rebuild from scratch. I also want to have the satisfaction
of learning and creating it myself. Here's the run-down:

Subject: Lots of loans and accompanying info - the old db had about 6-8
different tables organizing this data. At least two tables were imported
from external (non-Access) programs.

Use: Currently 4 simultaneous users, with a need to be able to expand this
up to 12 users. They need to be able to enter data, run (not modify)
reports, and refresh at will (import the external data). I need to be able
to create/modify reports, queries, and macros on demand daily.

Status: I managed to piece together a working version that seemed to have
everything I needed, except I forgot about the multiuser part until the end,
and i don't know how to make it simultaneously available to everyone. Here's
what I have:

[...]

It appears that you lost some of your existing data, but not all.

If you have reconstructed all of the Tables, etc., that the previous
database contained, then please ignore the rest of my reply.

Since your old database was corrupted to some extent, I suggest that you
can rescue much of the content by finding the last version of the old
database that you can still read, even if you can't compact or repair it
(don't even try), and either import from that database into your new
version (that you know is in good shape) the contents of the old Tables,
or in the old database, export those Tables to text files and then
import the text files into new Tables in the new database. You may have
to do some manual fiddling to be sure that the design of the new Tables
is what you want it to be.

For Queries, copy the SQL of the old versions into new Queries in the
new database, if you can't import them directly from the old database.

For Forms and Reports, if you can't import them directly, you can open
them in Design View and duplicate the contents manually.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.