Access 2003 and SQL 2005

before anything, i've been trying to fix this for a month, went all over the net and even signed up for experts exchange
and noone can help


i converted to SQL as a backend and Access 2003 as a front end
we have 10 computers
5 or 6 of them are fine (though it's only fine when it's not too many records to pull, otherwise it takes a little longer than Access Back End used to)

but the rest - the database is unusable, opening the database and clicking any search buttons takes 30-40 seconds

some of the working and not working computers are exactly the same configuration
i'm puzzled, i'd understand all of them not working, but some??

it's ODBC connection, tried different options like using SQL Native Clients instead
my thoughts are that it's something with the odbc connection, but there aren't too many options to play with and i think i've tried them all


want to add another thing, i thought SQL would make things much faster, but even working computers, as i said, when it comes to a lot of records (20,000, not that much as a matter of fact), take a while


one last thing, i researched this problem and found that quite a few people have this problem with sql 05 and noone has a solution

any bright minds here can help?

you guys have solved all my problems before

I'll only be making this single post in this thread and it is a pointer only to what you could look into.

1. SQL Server is not Access. You need to look into how to improve SQL Server performance (try "SQL performance tuning" on google or buy a book on SQL Server performance tuning).

2. With #1 said, make sure you have indexes on the correct fields, especially those frequently used in where clauses and those used in keys.

3. Bob Larson has stated many times on these forums that SQL 2005 has it's own special driver (sql native client driver) that you should be using. Do an advanced search on these forums for Boblarson as the user and the words 2005 driver.

That's about all I have to say on the subject. Check my siggy for other cool facts that might help if you continue to have problems.

I know that sql is not access but a lot of people use access sql combo

I tried native client

As for everything else, it goes to fixing the database
But my problem is that they're all using the same database with different results

And thank you, even though I did massive research on this, will look into performance tuning also

Hi,

Few questions for you:

1. Where does the access front end exist (on a network or local drive)
2. How many rows in the problem tables? how many columns? do they have primary keys?
3. How do the tables perform in sql server management studio?
does select * from table take a long time in management studio?
4. How did you upsize these tables?
5. What spec is the sql server? (memory processor ect)

1. local for each user
2. 20,000 - not much at all, right? the table opens fast, it's the form based on the table and any searches in that form that take 30-40 seconds
3. i didn't try, only because as i said before, it's only a few computers with this problem so i assumed the database wasn't the problem, because other computers are fine
the table itself opens pretty fast in SQL, but the record counter on the bottom doesn't show all 20000 records right away, ig goes up in increments of 4000, pretty fast and gets to 22000 in a few seconds
4. by hand, exported them one by one, redid all the relationships and indexex
for some reason when i try using IMPORT EXPORT, in sql, i get an error about some class not being registered
5. my sql server is the nt server as well, memory 4gb, processor don't remember, but it's a brand new commercial server, pretty fast

i can look it up next time i'm there