Starting an Access database for a group...

[kkrishna]

Hi All,

I am a new entrant to this group.

In my workplace, I am required to start a new Access database with network access to a limited number of users (about 25). We have an IT department who will help us in the installation.

I have few questions in this regard:



1. What is a better option - to start a database on the personal computer, populate the files, generate the most immediate queries and then copy the database file to the network folder;
- or right away start with a new database on the network with access permission only for me until the database is loaded with information before opening for the users?

2. We have a larger SQL database which holds corporate data. Eventually we would like some of the data (a few tables) from the Access database to be exported to SQL and similarly we would like to import some tables from the SQL database to Access. We envisage that this will be a periodical process (once in a month) to update the data in both the databases.

How best can we address this issue at the design stage of the Access tables? I mean in deciding about the data types of the fields etc.
Presently we have the main data in Excel tables.

3. I appreciate if the experienced specialists can offer advice and suggest some reference material for an exercise of this nature. We would like to avoid the common pitfalls associated with this task. The size of the database is obviously limited by the choice of Access.
Thanks in advance.

[Access_Blaster]

We would like to avoid the common pitfalls associated with this task. Thanks in advance.

kkrishna,

You’re going to have a steep learning curve with this project. It doesn't start with forms/queries/reports or any other MS objects the first thing I would do is get a piece of paper and start interviewing people on the needs. Second piece of advice learn all you can about the concept of normalization of tables in a relational database.

End of speech

Richard

[TheShabz]

Richard couldn't speak truer words. One thing I would like to add on is that when you do interview people, keep in mind that what they tell you they want, may not (probably wont) be what they really want. I cant begin to narrate the number of times I have been given requests ranging from simple queries to bigger multi-user database designs where the users dont know what they really want.

From my experience, dates will be your biggest nemesis. "I want to know when a customer received XYZ service." It seems simple. But most likely there is a process and each step along the way with its own date. What does the user mean by "received"? The date the customer inqured about a service? The date the purchase was made? The date the installation happened? The date it was billed? The date it was paid? The date it was sent to accounting? The date it went into the books? It's these finer things that as the db designer/architect/implementer/dataminer you have to always keep in mind.
The actual db creation is the easy part.

Take care and good luck.

[Access_Blaster]

Thank you sir

[kkrishna]

Thank you, Richard and The Shabz, for the response.
Fortunately, we will not be dealing with Purchase Orders and other entries concerned with dates. This database will be used mainly for monthly metrics with the associated data for the plants.

[kkrishna]

We are looking at one option of splitting the Access 2007 database and distribute the front end to users and storing the backend (with tables) in the backend on a network location.
1. Can this be done by persons who have access to network drives - I mean the same way we post a folder on the network drive?
2. With NTFS in place, can we protect the backend data with password protection?
Will anyone who is working with this arrangement share his experiences with me?
Thanks again

[Access_Blaster]

We are looking at one option of splitting the Access 2007 database and distribute the front end to users and storing the backend (with tables) in the backend on a network location.
1. Can this be done by persons who have access to network drives - I mean the same way we post a folder on the network drive?

Yes you can upsize your database to SQL Server or export the tables to a SQL server and use ODBC for your connection.

2. With NTFS in place, can we protect the backend data with password protection?

I know that ODBC has a built in password. But you can build your own logon routine.

Richard

[Rawb]

We are looking at one option of splitting the Access 2007 database and distribute the front end to users and storing the backend (with tables) in the backend on a network location.
1. Can this be done by persons who have access to network drives - I mean the same way we post a folder on the network drive?
2. With NTFS in place, can we protect the backend data with password protection?
Will anyone who is working with this arrangement share his experiences with me?
Thanks again

kkrishna,

What you're doing sounds exactly like how we have things set up where I work.

1. Yes. In fact, that's really the whole point of using a Split Database. What you'd do is install the Access Runtime on everyone's computer (since it's free, it'll be a lot cheaper to do this than to install the full version of Access) and give them a copy of the Frontend database. Then you just stick the Backend somewhere on a shared Folder or a Mapped Drive and *BAM* you're set up.

2. If all you're concerned about is "should someone have full access to the database or should they not be able to use it at all" then yes. For that, all you'd need to do is set up a Group (called "Database Users" or something similar) and add everyone to it that needs to use the database. Then you only give that group access to the network location where the Backend is stored.

If you need to track who does what inside the database though (for example, if you want to record who exactly entered/changed data on a certain plant), then you'll need to set up an Access Workgroup. You'd also need to use a Workgroup if you want to say, let User A do anything they want but only let User B do certain things.

[kkrishna]

Thank you, Rawb, for your response.
This gives us the confidence to go with our task as planned.