large SQL table linked to ACCESS

[MikeDutcher]

I am an actuary. My computer skills are concentrated in application development, bare bones in setting up systems.

I have a million record ACCESS 2000 table. (I have many multi-million record tables coming down the road.)

Others have said ACCESS unstable for large tables, get SQL SERVER.

Now I have ACCESS 2000 & SQL SERVER 2000.

I used upsizing wizard to upsize the large ACCESS table to SQL. After applying MS patches, setting up mixed-mode authorization & changing a registry setting, all went well.

ACCESS 2000 now links to this SQL SERVER 2000 table.

I ask ACCESS 2000 to go to the last record. (seems simple)

16 minutes pass, I get a "system resource exceeded" error & the row # is about 700,000.

I work for a NYS agency. The server is cutting edge, my client pc likewise. I do not lack for system resources.

I suspect that some setting is stifling me.

ANY SUGGESTIONS?

 

[bev]

I'm probably not the best person to reply, but I have both a question and a suggestion.
Q: When you say you asked ACCESS 2000 to go to the last record, *how* did you ask it to do that?
Suggestion: Build indexes in SQL for table.
Bev

 

[MichaelRed]

I second Bev's suggestion, and raise her one. Get a SQL Server Programmer on the staff. If you are goning to have Million record tables in ANY db, you really will need a pro programmer.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Stickarm]

Try Creating a passthrough query and executing this code. select count(*) from tblname