Need Your Opinion

[huskerphil]

Hello everybody! I had a quick couple of questions regarding a new database I'm currently looking into designing. I'm pretty new to database design, I've currently only been involved in Access 2007 database design and nothing else. The problem I'm having is if with this new database I'm not sure if I should be designing it as one database in Access or split the database between GUI and data and use MYSQL to make the initial tables, rows, columns, etc and then link the table back to Access. One of my co-workers thinks spitting the two and using MYSQL is the best bet. This database needs to be able to be quick with the GUI parts of it. I'm just wondering if using MYSQL will be the best or if doing it all in Access would suffice. I know there is size issues with Access and my database will not come close to that. Will the speed be quicker if it was all in Access? Please give me some insight on the pros and cons to using Access vs MySQL. Thanks!

 

[SQLSister]

I've never used MySQL so I can't answer for sure, but when we used a SQL Server back end we only gained performance increases when we used stored procs instead of Access queries, If we used Access queries, the process was often slower than when the database had been in Access because it had to be processed by the Access jet engine and then translated for SQl Server database engine.

You will need to relearn how to access and process data though if you truly want speed. No more using recordsets. No more processing data one record at a time and no more creating the SQL at runtime. You need to work with stored procs. You will also need to learn about performance tuning in MYSQL including, at a minumum, indexing.

You also should make sure you know how to properly design relational tables. I menion this becasue many people who started in Access are not formally trainined in database deisgn and thus make design mistakes that affect the performance and maintainability of the database. Here is a anarticle to get you started if you aren;t familiar with relational database design.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

"NOTHING is more important in a database than integrity." ESquared

 

[HarleyQuinn]

Not massively insightful but if you're moving away from Access as a back end it might also be useful to move away from Access as a front end as well.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[juliane26]

Its not so much performance: applications had to move away from Access backend (to SQLserver in our case) to enable multi-user.

Juliane

 

[PRPhx]

huskerphil,
A couple of questions you will need to answer.
1) how many users will be accessing your database.
2) How big do you expect your tables to get?
3) Security might be an issue. I would have to say that SQL Server security is much better than Access. SQL Server can allow you to give or deny access to most everything in SQL Server.

As stated, you really need to start thinking in terms of stored procs and tuning your database. If you need, you can download and use SQL Server 2005 Express, which is free. There is also a GUI download that is helpful. You might want tio think of using something other than Access as the frontend. VB.Net or C# allow you to do so much with the front end. Yes you can do most eveything in Access and link to your database. But after working with VB.net, I HATE going back to an access dev environment.

The one advantage Access has of vb/SQL Server is building reports. AFAIK, report generation is not included in SQL Server Express. You can use the included Crystial Reports in VB.Net, but this can be a real pain when you want to distribute your app to multiple users.

 

[MasterRacker]

juliane26 - what kinds of problems were you seeing? Access handles multiple users quite well in most circumstances - at least at the workgroup level.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[juliane26]

MasterRacker,
Depends on the size of your workgroup - 5 or 6 ok, but when all of a sudden a complete department is interested, then it becomes an issue and a problem.
I am busy with SQL server only short now(2 month), but we took over already 3 Access applications for that reason, probably all other migrated earlier for similar reasons: over the net Access does not scale (performance) and file sharing issues.

Juliane

 

[MasterRacker]

I was curious because I've seen an Access system for phone base trouble ticketing handle 15-20 simultaneous users quite well.

That being said, I agree that SQL Server is a much better platform and if you're able to port you will be much better off down the road. Good luck.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Thargy]

I think you were lucky racker. I found that with access around the 24/25 mark it just went legs in the air.

To be fair though, it was always designed and intended as a single user system, with some room for a few more. I can't imagine it ever scaling to anything useful for enterprise scale work, short of a complete redesign.

Regards

T

Grinding away at things Oracular