Need Your Opinion

• Feb 25, 2008

• #2

SQLSister

Programmer

Jun 18, 2002

7,292

US

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

 

Upvote 0 Downvote

• Mar 3, 2008

• #3

HarleyQuinn

Programmer

Jan 15, 2003

4,769

GB

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.

 

Upvote 0 Downvote

• Mar 4, 2008

• #4

juliane26

Technical User

Mar 10, 2004

207

GB

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

Juliane

 

Upvote 0 Downvote

• Mar 4, 2008

• #5

PRPhx

MIS

Jul 4, 2005

747

US

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.

 

Upvote 0 Downvote

• Mar 4, 2008

• #6

MasterRacker

Active member

Oct 13, 1999

3,343

US

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]

 

Upvote 0 Downvote

• Mar 13, 2008

• #7

juliane26

Technical User

Mar 10, 2004

207

GB

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

 

Upvote 0 Downvote

• Mar 13, 2008

• #8

MasterRacker

Active member

Oct 13, 1999

3,343

US

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]

 

Upvote 0 Downvote

• Mar 14, 2008

• #9

Thargy

Technical User

Nov 15, 2005

1,348

GB

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

 

Upvote 0 Downvote