Advice about how to do an Access project

[Gwen61]

I've spent the last couple of weeks, off and on, teaching myself how to design a database, but now I'm thinking it should be handled another way.

This is the scenario of the project:

There are 5 people in different parts of one state, who will be accumulating the same information.

Originally, I designed the db to be used by each of the 5 people, even though I have no idea, yet, how to pull it all together.

Now, I'm wondering if each of those 5 people should input the info into an Excel spreadsheet, then send it to 1 person who will be in charge of the db, who would then import the Excel spreadsheets into Access, using one table for each person.

Or, build 5 databases, one for each person, all containing the same info.

Ultimately, a simple report needs to be prepared for the governor of the state, and based upon what I've learned so far, I think it will be easier to prepare the report from 5 tables rather than 1.

I'd appreciate anyone's opinions.

Thanks.

 

[Banana]

There's two questions that will have to be answered.

1) How much do you really need the Access's functionality over than Excel? Excel as a data storage format is quite serviceable for that purpose and as long dataset are small and relatively simple, there's nothing wrong with using a flat file format. But if you have complex business rules and want to validate all your data before they're added, then you will definitely benefit having Access forms. (To be fair, Excel does have UserForms, which I would surmise is adequate for simple validation; I'm just not sure how up to par it is to Access forms, though)

If you've decided that your data entry requirement is sufficiently complex and stringent to require Access to enforce the business rules then we can move onto next question:

2) What is your concurrency needs? If all you want to do is to have one central repository of all data and the users over the world doesn't have to look at other data, then it will be just adequate to have them do a regular mailing with their copy attached and you can automate the appending process and either do everything with one click of a button or use task scheduler to run the process without your intervention.

If your users require concurrency and ability to look at others' data, then you will have to consider two (actually three) possible options:

a) Terminal Server where your users can log in and work with their data on the server itself rather than their own PC, so there's just one database (with copy of front-end for each user's session, of course). This is simple to set up and provide the users with live data without too much administration.

b) Replication. While .ACCDB files no longer support replication, if you're still using .MDB files, you can pursue this option. You would probably want to look at David Fenton's wiki on replication for more information. This require quite more administration compared to terminal server but if you do not have a terminal server or bandwidth available, this is a viable option, especially for keeping a 'pure' Access solution.

c) If you are really willing to go out on the limb, another option is to use a backend that is more network tolerant; MS-SQL, DB/2, MySQL, PostegreSQL or whatever you like. You can link Access front end to those servers via WAN and still get great performance but at expense of increased learning curve and development cycle. Security will also be a concern as well.

So you may want to think about how you will gather the data and whether you need to share the data or not.

Regardless of what you choose, the end result should be that you only have one table, though in option A, they may have their own copy of table, but it's just an append query away from being updated and for option B, a synchronization away.

I hope this helps some.

 

[pbaldy]

Another option that might work if the circumstances fit is creating a web page (asp, php, etc) for the remote users to enter data, so the central database contains everything. Like the yellow dude, I think one table is the way to go, with a field to designate the source if appropriate.

 

[dfenton]

To me, the clear winner is obviously Windows Terminal Server. This allows you to deploy a pre-existing Access application to remote users without needing any alterations to the existing app. And it avoids all the muss and fuss of things like Jet replication (which gets complicated when your users aren't able to connect by wired LAN to the central office).

 

[Gwen61]

Thanks very much for all your responses. I've been focused on something else today, but I'll be back at the db tomorrow, and when I read the responses in detail, I'm sure I'll have questions.

Thanks, particularly, about the terminal server info. According to what I read in the Access 2007 help, the only way to publish a db to the web was to use a Sharepoint website. Microsoft Sharepoint costs $4,000 + just for the program, so I didn't think that would be cost effective.

Since I'm doing this for a state dept., I guess I would need to find out if it has, or can get, a terminal server, right? I don't know much about them.

 

[dfenton]

I thought that most versions of Windows Server from 2003 on included Sharepoint services out of the box?

But I don't see why you mention Sharepoint. Nobody mentioned it before, and it's completely distinct from Windows Terminal Server (which really only requires the Client Access Licenses, which, last time I checked were about $35/40 per seat).

 

[Gwen61]

I mentioned SharePoint because that's where I was directed on Access Help when I searched for publish to web.

In any event, I'm in a holding pattern until 1/5, when the person in charge of the project returns to work and answers some questions I have.

I'll be back then.

Thanks.

 

[dfenton]

I mentioned SharePoint because that's where I was directed on Access Help when I searched for publish to web.

But publishing to the Web is not really what you want to do, is it?