A Need for Speed (1 Viewer)

Rich1968 · Sep 29, 2012

We are currently running a split access 2003 database. 4 users and it's slow. I am trying to come up with a solution. A friend suggested SQL.

My question is there any easy way to this conversion. Will sql increase my speed I know nothing about sql, is it easy to learn?

Thanks

Rich1968

bob fitz · Sep 29, 2012

Can you tell us what, exactly, is slow.
I can't tell you anything about increasing the the speed of your db by using SQL but I was amazed how much faster I was able to open, use and close forms and reports by just making the following changes:

1) Load the form, subform, combobox and listbox record sources at run-time. That's it. You will achieve dramatic performance improvement and reduced database size. See: http://www.granite.ab.ca/access/performanceforms.htm

2) Create a persistent connection. See:http://www.granite.ab.ca/access/performanceldblocking.htm

Take a look at some other suggestions at the site these came from: http://www.granite.ab.ca/access/performancefaq.htm

Rich1968 · Sep 29, 2012

Thanks Bob,

When we have 3 people on the system it slow down alot. I keep a form connected to the BE at all times and I have gone thru the forms to make them perform better.

I still have a need for speed!

Thanks
Rich1968

bob fitz · Sep 29, 2012

Rich1968 said:
I have gone thru the forms to make them perform better

What, exactly, have you done?

boblarson · Sep 29, 2012

You say it's split but does each user have their own copy of the front end on their machine? You aren't sharing a single front end from a network location are you?

Rich1968 · Sep 30, 2012

No Bob,

The BE is on the server and each of us has a FE, linked over the network.

I wish there was a program that could look at it and tell me what to tinker with. I have read all of the posts, and tried all the suggestions. When the third person logs on wam, it slows down. Every FE is a copy of the master FE I work in.

Ok, so is it difficult to set up a sql BE?

Thanks
Rich1968

Galaxiom · Oct 1, 2012

Appropriate indexes on the BE tables are absolutely essential for performance.

SQL Server will not automatically be faster. In fact it can even be slower in some arrangements. The design has to be appropriate for the backend.

Galaxiom · Oct 1, 2012

Queries on joins between back end and front end tables are always slow no matter how they are configured.

mahenkj2 · Oct 1, 2012

I wish there was a program that could look at it and tell me what to tinker with. I have read all of the posts, and tried all the suggestions. When the third person logs on wam, it slows down. Every FE is a copy of the master FE I work in.

There is. At least Access 2007 has inbuilt feature to check database performance and suggest the optimization. I never used Access 2003, please check if it is.

In access 2007, in Database tools, there is a button Analyze Performance, which if you click, shall analyze the objects selected by you and suggest the optimization in setting indexes, table relationship etc. I think queries are very much affected by setting proper indexes, so if your dB has lot of data then it might take you long to retrieve the results. Please search same feature for 2003.

As Galaxiom pointed out

Appropriate indexes on the BE tables are absolutely essential for performance.

Based on my negligible experience, I do not think four users applications has this much slow if everything is in place and you should not need upsizing.

mahenkj2 · Oct 1, 2012

I suggest first make a back up before changing anything.

Rich1968 · Oct 1, 2012

Thanks Mahenkj2, found it, Gonna go thru it and hopefully I will satisfy my need for speed.

Rich1968

Rich1968 · Oct 1, 2012

OK sometimes its something as simple as an index......Yep an INDEX!!!! Thanks guys fixed the indexes and wam speed. Still going to run thru all my code.

Rich1968

A Need for Speed (1 Viewer)

Rich1968

Registered User.

bob fitz

AWF VIP

Rich1968

Registered User.

bob fitz

AWF VIP

boblarson

Smeghead

Rich1968

Registered User.

Galaxiom

Super Moderator

Galaxiom

Super Moderator

mahenkj2

Registered User.

mahenkj2

Registered User.

Rich1968

Registered User.

Rich1968

Registered User.

Similar threads

Users who are viewing this thread