Database design - Step 1 help required

I'm writing a database application for a construction company to replace a standalone Access database I wrote a couple of years ago. Basically, it takes care of subcontract information, including contract changes, estimates, transmittals, etc.

For the old database, a "blank" version had to be copied for each new construction project

The company has maybe six to eight users who will be using the new database, 100BT w/switch connections to a NT4 server.

What I'm thinking of is a single database residing on the server which is accessed by the clients through a front end of my creation.

Alternatively, I was originally thinking of was a standalone application which created a new database for each new project (much like creating a new document in Word). The advantage of the this approach would be portability - a manager could copy the database to a Zip disk and take it with him.

Anyway, I've gotten myself confused as to how I should set this up. I have some Access experience, and some VB. Just not enough to design a client/server database:)

Any tips or pointers to solutions or sources of information would be greatly appreciated.

Regards,
Craig Hansen

You should use a single database on your server.
This will cut out a lot of duplication and be loads more efficent.

With only 8 users Access should do fine.
Should this change upwards dramatically an upgrade to SQL Server will not be that difficult (this will mostly depend on a good GUI design).

Check out "replication" in Access Help for details on how you could make the database portable.

With such a small user base and small Db(I'm assuming here that your Db would not have more than around 20 tables), Access replication "should" work quite well.

Yes, the DB would have fewer than 20 tables. I also don't see how the number of users could do anything more than double in the next five years.

So I could just use Access 2000 on the server to maintain the DB and create a VB front end, eh? Or should the DB be created in VB as well?

Oh well, I'm reading up on that right now. I suppose I'll find ways in my reading to "retire" projects from the DB and archive them. Hmm...

Regards,
Craig

Yes create the Db on the server and have the users connect to it via a VB client.

Personaly I would use Access 97 as the backend database.
All its faults are known and its much more stable than and its a better product (at the moment) than Access 2k.

There is no such thing as creating a Db in Visual Basic.
(not in this sense anyway).

I suspect we'll be getting a few more posts from you in the near future, if they are specific - ask away....

I realize my questions are pretty broad right now, but I really just needed a pointer in the right direction - thank you for that.

I also know what it is like on forums such as these. I'll try not to be a leech by only asking questions without making any further contributions.

Regards,
Craig

'--- Lotsa code AND UGLY WITHOUT INDENTION.

I'm going to provide a solution addressing the creation of databases on the fly through application code. Moving in this direction should alieviate the requirement to copy a 'blank' database to a new name. Keep your head clear, this will use some reusable code we call our Toolbox (hence the T_* naming convensions). This should provide some ideas on how to create a table structure. Basically, we create an empty husk of a database, then fill up arrays with table/field/index info, then create the table.

'--- This is an example of the application side...
Sub FileNewStructure(dbNew As Database)
<blockquote> '--- Archive Table
Call T_CreateField("SSN", DB_TEXT, 9, DB_UPDATABLEFIELD)
Call T_CreateField("DettatchDate", DB_DATE, 8, DB_UPDATABLEFIELD)
Call T_CreateField("Reason", DB_TEXT, 30, DB_UPDATABLEFIELD)
Call T_CreateField("LastUnit", DB_TEXT, 50, DB_UPDATABLEFIELD)
Call T_CreateField("ArchivedBy", DB_TEXT, 64, DB_UPDATABLEFIELD)
Call T_CreateIndex("ArchiveSSN", "+SSN", True, False)
Call T_CreateTable(dbNew, "Archive")

'--- User Management Table
Call T_CreateField("LoginId", DB_TEXT, 64, DB_UPDATABLEFIELD)
Call T_CreateField("SSN", DB_TEXT, 9, DB_UPDATABLEFIELD)
Call T_CreateField("Password", DB_TEXT, 16, DB_UPDATABLEFIELD)
Call T_CreateField("SecurityMask", DB_TEXT, 8, DB_UPDATABLEFIELD)
Call T_CreateField("MajorCommand", DB_TEXT, 50, DB_UPDATABLEFIELD)
Call T_CreateField("TrainingUnit", DB_TEXT, 50, DB_UPDATABLEFIELD)
Call T_CreateField("RecruitingUnit", DB_TEXT, 50, DB_UPDATABLEFIELD)
Call T_CreateField("Supervisory", DB_BOOLEAN, 1, DB_UPDATABLEFIELD)
Call T_CreateField("AdminAccess", DB_BOOLEAN, 1, DB_UPDATABLEFIELD)
Call T_CreateField("TimesAccessed", DB_LONG, 4, DB_UPDATABLEFIELD)
Call T_CreateField("LastUsedDate", DB_DATE, 8, DB_UPDATABLEFIELD)
Call T_CreateField("WhoModified", DB_TEXT, 64, DB_UPDATABLEFIELD)
Call T_CreateField("DateModified", DB_DATE, 8, DB_UPDATABLEFIELD)
Call T_CreateIndex("UsersPrimaryKey", "+LoginId", True, True)
Call T_CreateTable(dbNew, "Users")

'--- System Module Management Table
Call T_CreateField("LoginID", DB_TEXT, 64, DB_UPDATABLEFIELD)
Call T_CreateField("MDBName", DB_TEXT, 128, DB_UPDATABLEFIELD)
Call T_CreateField("CommandName", DB_TEXT, 50, DB_UPDATABLEFIELD)
Call T_CreateField("ModuleCode", DB_TEXT, 25, DB_UPDATABLEFIELD)
Call T_CreateField("ModuleAccess", DB_TEXT, 20, DB_UPDATABLEFIELD)
Call T_CreateField("TimesAccessed", DB_LONG, 4, DB_UPDATABLEFIELD)
Call T_CreateField("TotalTimeInMod", DB_LONG, 4, DB_UPDATABLEFIELD)
Call T_CreateField("LastUsedDate", DB_DATE, 8, DB_UPDATABLEFIELD)
Call T_CreateIndex("LoginID", "+LoginID", False, False)
Call T_CreateTable(dbNew, "UsersModAccess")</blockquote>

End Sub

'--- The enlosed file is 'Toolbox' stuff, otherwise known as reusable code... This is DAO. We will have an ADO class version soon. We use many reusable code modules so there may be issues in attempting to run this code on its own - but you can get the idea. ADODB uses ADOX and catalogs to do the same function.



Roger Bogh