Access web application is slow, should I upgrade to SQL server?

Hi,
first time poster/newbie here.
I've got a football (soccer for the yanks!) predictions league website that is driven by and Access database. It basically calculates points scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There are two sections of the site however that have almost ground to halt now that more users have registered throught the season. The players section and league table section have gone progressively slower to load throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All the calculations are performed in the Access database Ive written and there are Access SQL queries to get the data out.
My question is, is how can I speed the bloody thing up! ! Somone has alos suggested to me that I use stored procedures and SQL Server to speed things up? Ive never used SQL Server before so I am bit scared about using it (Im only a hobbyist), and I dont even know what a SP is or does. How easy will it be upgrading the whole thing to SQL Server and will it be worth the hassle, bearing in mind I expect my userbase to keep growing? Do SP help speed things up significantly? Would appreciate some advice!
Thanks in advance,
John.

This is one of those how long is a piece of string questions....

In general I woudl recomend Access Db apps with more than 5/20 users upgrade to a version of SQL Server.

An SP is a way of capturing logic and running it inside the server, if your app brings back lots of data and then processes in the web page then its likely that an SP will be faster, but it depends on the logic. Its faster because you can be more selective with the data, you are not pullng a lot of unused data over the wire etc.

Do you know which parts of the app are slow? specific queries? you could try running it ona local machine using both access and sqlexpress and compare the perf to see.

access to sql upgrade

Can someone tell me what advantages I would be looking at for using MS SQL as my backend for database. We're seeing higher and higher performance losses on slow queries etc. I don't know much about SQL so any help would be appreciated.I'm not an expert on Access, so here are some points for SQL Server, not all but a start:[list=1]
Can handle more concurrent users
Better handling of locks and lock types (nolock)
Stored procedures
Cached queries
Cached data
Much more flexible with backup and recovery, up to the minute
Triggers to implement business rules
[/list=1]|||Take a look at the following link:

click here (http://http://www.swynk.com/friends/Vartanyan/Access_vs_SQLServer.asp)

Access to SQL Server 2k database is slow

My SQL server was just upgraded from 7.0 to 2000. It was having memory leak
problems before, so it was upgrade to 2000 by the agency that over sees our
databases. Now the access time to the database is pretty slow when trying to
pull up information. Our custom client software loads fine and connects to
the database, but we when we do a search or try to open a particular case
(we are a court) from the database, it takes almost 30 seconds before any
information comes up. No error messages are returned. It is just a slow
response time. Sql server 2k is installed on a w2k server and the clients
are running XP pro, with 100mb/full duplex on a switch. I am not familiar
enough with sql server so I wouldn't exactly what to look for or what kind
of commands to run for running the Profiler or Query Analzyer.
I would appreciate any help anybody could provide. In case your wondering,
the guy who did the upgrade decided to take 3 days off after doing this on
the weekend, don't ask me why? I am the tech person for the agency, just no
familiar with sql server. Again, thanks for any help or advice.Make sure the autoclose database option is not set to on. You can check this
out in enterprise manager, right click on your database and then go to
options. If this is not your problem, I think you're going to have to run
your query manuaaly from query analyzer to see if the problem is within sql
server or outside.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Vegeta" <vegeta@.whocares.com> wrote in message
news:%23BhI6Y2CEHA.3408@.tk2msftngp13.phx.gbl...
> My SQL server was just upgraded from 7.0 to 2000. It was having memory
leak
> problems before, so it was upgrade to 2000 by the agency that over sees
our
> databases. Now the access time to the database is pretty slow when trying
to
> pull up information. Our custom client software loads fine and connects to
> the database, but we when we do a search or try to open a particular case
> (we are a court) from the database, it takes almost 30 seconds before any
> information comes up. No error messages are returned. It is just a slow
> response time. Sql server 2k is installed on a w2k server and the clients
> are running XP pro, with 100mb/full duplex on a switch. I am not familiar
> enough with sql server so I wouldn't exactly what to look for or what kind
> of commands to run for running the Profiler or Query Analzyer.
> I would appreciate any help anybody could provide. In case your wondering,
> the guy who did the upgrade decided to take 3 days off after doing this on
> the weekend, don't ask me why? I am the tech person for the agency, just
no
> familiar with sql server. Again, thanks for any help or advice.
>|||The autoclose database option was not set to on. I also just saw that sql
server
2k had no service packs installed. I will be backing up my database and
installing
sql 2k SP3a. Maybe this could help it some.
Thanks for you response.
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:%23tdVxG4CEHA.3804@.TK2MSFTNGP09.phx.gbl...
> Make sure the autoclose database option is not set to on. You can check
this
> out in enterprise manager, right click on your database and then go to
> options. If this is not your problem, I think you're going to have to run
> your query manuaaly from query analyzer to see if the problem is within
sql
> server or outside.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Vegeta" <vegeta@.whocares.com> wrote in message
> news:%23BhI6Y2CEHA.3408@.tk2msftngp13.phx.gbl...
> leak
> our
trying
> to
to
case
any
clients
familiar
kind
wondering,
on
> no
>|||On 2004-03-16, Vegeta <vegeta@.whocares.com> wrote:
> The autoclose database option was not set to on. I also just saw that sql
> server
> 2k had no service packs installed. I will be backing up my database and
> installing
> sql 2k SP3a. Maybe this could help it some.
>
Try updating MDAC on the client machines. I had the same problems on some
machines at my company, though we're running Win2k on most of clients.
Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr

Access is Denied in Upgrade Advisor

Hi all,

I have run the SQL Server 2005 Upgrade Advisor against a default instance of SQL Server 2000 server running on Windows Server 2003.

One thing I need to fix before I upgrade is:

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)

SVCACCTREQ

I'm not sure what this means or what I need to do before I can upgrade.

Does anyone have a clue on this one.

Thanks,

David

Hi David,

The issue you posted is actually an error encountered while processing the rule (SVCACCTREQ). I'm wondering if the account you used to run Upgrade Advisor was a member of the local administrators group on the machine in question?

jeff.

|||

Thanks Jeff,

I am running the Upgrade Advisor remotely as the server does not have .NET 2.0 Framework installed.

The first time I got the error, I connected to SQL Server using TCP/IP logging on as sa.

I got the same result where I had a mapped drive to the remote server using a Domain Administrator logon and use Named Pipes to connect to SQL Server.

We now have access to a test server where I can install the .NET 2.0 Framework and run the Upgrade Advisor locally.

Hopefully this will resolve the problem.

David.

|||

Jeff, same here using SA (remote).

I also get "Attempted to perform an unauthorized operation" on NETPROTOCOL, MDACVERSION and few more.

\Ruud

|||

Is there anyway to use the Upgrade Advisor tool remotely avoiding the .net framework installation on sql server 200?

i got the following errors:

Attempted to perform an unauthorized operation.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Thanks

Gianfranco

|||Some of the rules executed by Upgrade Advisor require admin priviliages at the Operating System layer that won't be available when using SQL Authentication. I suspect the rules you are encountering these errors are those rules which require admin priviliages at the Operating System layer. The SA, or equivilant SQL account, does not have permission to perform the actions necessary for these rules to execute, thus the error you are seeing.|||

You should not need to install the .NET Framework on a remote install. All rules will use either a SQL Connection or WMI to perform the action requested by the rule. The .NET Framework is a requirement of the Upgrade Advisor tool itself, so only where you are running Upgrade Advisor do you need the .NET Framework.

There are additional requirements for the Upgrade Advisor client machine, such as OLAP Tools and the DTS client, so make sure you have those installed on the machine where you're Upgrade Advisor.

|||

I was trying to do the remote installation too and I got the same error too.

I was able to fix this by running Sql Server 2005 Upgrade Advisor on a machine that was on the same domain as the database server.

I was logged in as a user who had administrator rights on both machines. So when I ran Sql Server 2005 Upgrade Advisor I made it connect to the database server and to login using Windows Authentication.

Access is Denied in Upgrade Advisor

Hi all,

I have run the SQL Server 2005 Upgrade Advisor against a default instance of SQL Server 2000 server running on Windows Server 2003.

One thing I need to fix before I upgrade is:

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)

SVCACCTREQ

I'm not sure what this means or what I need to do before I can upgrade.

Does anyone have a clue on this one.

Thanks,

David

Hi David,

The issue you posted is actually an error encountered while processing the rule (SVCACCTREQ). I'm wondering if the account you used to run Upgrade Advisor was a member of the local administrators group on the machine in question?

jeff.

|||

Thanks Jeff,

I am running the Upgrade Advisor remotely as the server does not have .NET 2.0 Framework installed.

The first time I got the error, I connected to SQL Server using TCP/IP logging on as sa.

I got the same result where I had a mapped drive to the remote server using a Domain Administrator logon and use Named Pipes to connect to SQL Server.

We now have access to a test server where I can install the .NET 2.0 Framework and run the Upgrade Advisor locally.

Hopefully this will resolve the problem.

David.

|||

Jeff, same here using SA (remote).

I also get "Attempted to perform an unauthorized operation" on NETPROTOCOL, MDACVERSION and few more.

\Ruud

|||

Is there anyway to use the Upgrade Advisor tool remotely avoiding the .net framework installation on sql server 200?

i got the following errors:

Attempted to perform an unauthorized operation.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Thanks

Gianfranco

|||Some of the rules executed by Upgrade Advisor require admin priviliages at the Operating System layer that won't be available when using SQL Authentication. I suspect the rules you are encountering these errors are those rules which require admin priviliages at the Operating System layer. The SA, or equivilant SQL account, does not have permission to perform the actions necessary for these rules to execute, thus the error you are seeing.|||

You should not need to install the .NET Framework on a remote install. All rules will use either a SQL Connection or WMI to perform the action requested by the rule. The .NET Framework is a requirement of the Upgrade Advisor tool itself, so only where you are running Upgrade Advisor do you need the .NET Framework.

There are additional requirements for the Upgrade Advisor client machine, such as OLAP Tools and the DTS client, so make sure you have those installed on the machine where you're Upgrade Advisor.

|||

I was trying to do the remote installation too and I got the same error too.

I was able to fix this by running Sql Server 2005 Upgrade Advisor on a machine that was on the same domain as the database server.

I was logged in as a user who had administrator rights on both machines. So when I ran Sql Server 2005 Upgrade Advisor I made it connect to the database server and to login using Windows Authentication.

Access Denied to SQL Server 2000 after the OS upgrade to W2003

I cannot connect to the remote SQL Server 2000 running on this machine that has just been upgraded to the W2003 server. Everything worked perfectly before the upgrade on W2000. All the logins, firewall setting stayed the same. Can ping the machine.
But when trying to register/link, or connect thru ODBC, I got the error
SQL server does not exist or access denied.
We don't have SQL server SP3 installed.

olaCan you register it by IP? Win2003 comes with everything closed. I didn't have much chance to play with it, but see if TCP/IP is selected in Server Network Configuration, and if the port (1433) is still the default port.|||Yes, on both machines the TCP/IP is selected, 1433 a default port, SQL authentication.
Remote machine: W2003 + SQL server 2000
My machine: w2000 + SQL server 2000
Everything worked fine for the past year until the upgrade of a remote machine we did this past tuesday