Is Access Good Enough for Retails?

[xolo]

I am building my own program for my 2 retail shops using Access 2007 and it's about 70% finished. But lately a person told me that Access is very slow compared to Microsoft SQL or MySQL when dealing with a lot of data. One shop of mine has hundreds of transaction a day, so I am wondering if this will become a problem. I want to make sure about this first before continuing my program.

So, is Access good enough for my project?

 

[namliam]

1000 transactions per day, 7000 per week 350.000 per year

Depending on how much "live" history you want to keep up to 10 mio records isnt really a problem for access it self.

Problem is dedication, keep in mind that access is reliant on your Network and/or desktop/laptop computer (1 processor@2ghz + 4gig ram + 2 harddrives?). While SQL Server/MySQL is working on a "lone" server with 4 or more cpu, 16 gig ram? + multiple harddrives.

Offcourse responses will go faster on the server, question is by how much and when does the 1 second (up to what 10?) pay off vs the cost of the license, maintenance and buy cost of the damn thing?

 

[Mike375]

I have quite a few applications that are used for telemarketing.

The largest use is 7 computer and across wirelesss (I know that is not supposed to be done) and between the 7 computers there would be about 250 actions per hour. Each one involves opening a couple forms, tansferring data and moving to next record. It is also printing and emailing during the hour depending on result of call.

That is with Access 2003

Edit: Just read the Mailman's post and fogot to mention the records in the Many table are moved out after about a month. Although that is done for other reasons, not speed.

 

[DCrake]

Mike I would be overly concerned about wireless connections. This is the easiest way to corrupt your database. 1 nanosecond of lost connection during a write can easily corrupt your data.

 

[Mike375]

David,

We have never had it happen but even if I knew it would happen would still use wireless and so would the other places that use what I make. At worse we would lose 60 minutes of data and that includes if the DB became corrupt.

Have had the wireless drop out a fair bit, probably once a day but I guess the chances of it happening as write is being done is small.

If it was a normal office environment things would be different. But the freedom the telemarketer has to move outside and call etc. is very important and they have wireless head phones.

 

[dportas]

I suggest you give some thought to the availability and integrity needs of the database. In a transaction processing system avoiding data loss is usually critical to the business, even for low volume data like yours. SQL DBMSs like SQL Server and MySQL have transaction logging so that your data is always protected against data loss, even between database backups. This and other integrity and availability features have no equivalent in the Jet/ACE file-sharing model, which was never designed for high availability or high integrity systems.

 

[HiTechCoach]

I am building my own program for my 2 retail shops using Access 2007 and it's about 70% finished. But lately a person told me that Access is very slow compared to Microsoft SQL or MySQL when dealing with a lot of data. One shop of mine has hundreds of transaction a day, so I am wondering if this will become a problem. I want to make sure about this first before continuing my program.

So, is Access good enough for my project?

If you properly split your database into a front end and back end.

If the need arises in the future, you can always switch the back end from Access (JET/ACE) to mySQL or Ms SQL Server. Stilling using the Access as a front end for the Forms, reports, etc.

 

[Zigzag]

dportas

I'm sure that with over 1900 posts Mike is well aware of the consequences of using wireless.

I recently found out that one of my customers have 9 pcs using wireless for the past 9 months. When I pointed out the errors of their ways the customer replied that it has performed faultless up to date and would accept responsibility for any consequences.
Maybe they have just been lucky?
But I am still waiting for the call.

 

[vbaInet]

But I am still waiting for the call.

You're asking for trouble there. You didn't touch wood when you said that! lol

 

[gemma-the-husky]

i suspect we may worry unduly about wireless

At home, Our PS3 and laptops tend to be attached to the internet by wireless connection (and a not-too quick one at that) - the ps3 seems to be able to download hundreds of megs of stuff without failing.

While it may not be ideal, and may not be quite as quick as a wired connection, i suspect it remains adequate.

-------------
with regard to the OP

Accessd should be perfectly acceptable for your environment. The system design has a lot to do with it, as you can limit the amount of data to be transferred, by judicious design.

Note that "just" mgrating to SQL isnt necessarily a panacea anyway - you need to re-engineer the application to take advantage of the server side processing.

 

[Rich]

dportas

I'm sure that with over 1900 posts Mike is well aware of the consequences of using wireless.

I wouldn't bet on it

 

[dportas]

dportas

I'm sure that with over 1900 posts Mike is well aware of the consequences of using wireless.

My reply was directed at the OP's question. Nothing to do with wireless or Mike's reply.

 

[Zigzag]

dportas

Apologies…