Need Your Opinion
Need Your Opinion
(OP)
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!
RE: Need Your Opinion
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://
"NOTHING is more important in a database than integrity." ESquared
RE: Need Your Opinion
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: How to get the best answers before posting.
RE: Need Your Opinion
Juliane
RE: Need Your Opinion
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.
RE: Need Your Opinion
_____
Jeff
It's never too early to begin preparing for 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
RE: Need Your Opinion
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
RE: Need Your Opinion
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
It's never too early to begin preparing for 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
RE: Need Your Opinion
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