Production

[Neak1]

Hello Everyone,

Has anyone ever worked with an Access Database which had more that 12,000 records, if so how did it perform. I am trying to decide what type of Database to use to have a more efficient database as far as production is concerned. There are many users that will be accessing the database on a daily basis. I would like some recommendations or advice as to what to use to get this database to work effectively. Should I use Access, Visual Basic, or some other form of database application and what type of server capacity should be used????

What do you think?????

Neaka

 

[svanels]

Yes unfortunately we have worked 2 years with an Access Database in a Network environment. Now it crashed with 28 MB of data.
My advise stay away from access, invest in MS SQL server, Interbase, Sybase or Oracle, especially if you will have multiple inputs.

Nothing is more annoying then Access telling you that you can not update because another user caused some jet engine error, 3 o'clock in the morning, and you are the only one on the plant.

Regards Steven van Els
SAvanEls@cq-link.sr

 

[ecims]

I have developed Access databases for years with 300,000+ records that grow to around 50 MB. They are used for activities such as payroll, production planning, environmental reporting and inventory.

Multi-user access over 10 people is not recommended as record locking can be an issue and Access is not well suited for large numbers of users. One to 5 users is ideal for simultaneous use.

For an enterprise app you should preferably use Visual Basic and SQL Server or Oracle. But, as you might imagine the more robust the architecture the more effort required to get results. VB, SQL Server and Oracle are no exception to the rule.

If you do decide to develop in Access then go with Access 2000 or XP as these provide the most robust platform because SQL Server technology was added in these versions.

The majority of databases I have seen that suffer from poor performance are related to poor relational database design or not enough memory on the client or server computer. Access runs best on Windows pcs with at least 64MB memory and up.

If you set your app up on a network then create one mdb file with all the forms, queries, modules, macros and reports and store that locally on the client pc. Next, create a separate mdb file with the data tables and put that file on the server. Then you just link to the tables on the server from the client. You will get significant performance and stability improvements if configured this way. Additionally, Access needs to be regularly repaired and compacted to clean out its index tables and keep the mdb file size small.

If you grow out of Access you can connect Access mdb files to SQL Server tables and use the existing Access forms and reports with some minor modification. Access will also connect to Oracle, Paradox, dbase, FoxPro, and Excel if you need to gather data from several sources.

Visual Basic is also very good if you want to create an executable, but VB typically requires a more significant effort and the learning curve is a little steeper. The benefit is a richer development environment and an executable that is smaller than an mdb. But, if all users in your office already have Access then sometimes Access is prefered because a Visual Basic app will copy drivers which may conflict with other programs on the client pc whereas if Access is already there then you don't have to worry about driver conflicts.

For networks; Many times the best design is to use Access on the client as a local data collector with temporary data tables then have it post data to SQL server or Access tables on a server for recordkeeping/reporting at the end of a shift or other timeframe. If the network goes down you still can use the app and the next time it's up you post to the SQL Server tables.

Hope this brief overview helps.