Connection lost

I'm running an access application on my local server (SQL Server 2000)
On my win 2000 it worked perfectly.
On my win XP, I always get this error message :
3151: ODBC--Connection to 'MyODBC' Failed.
This happens when I want to run a procedure that fills a table with records
from other tables. There's a lot of Select in this code but always on the
same recordset. Many loops. I see my record number going fast in the bottom
of the screen but it always stops at 207. When I do it in debug, no
problem... can't find where's the problem.
I allowed the maximum quantity of memory for SQL Server (512) and I have 2
Go of memory on my computer.
Help please!
StephSteph,
First, the memory issue. If your host computer has 2 GB of memory, and there
are no other significant applications using the computer, SQL Server should
NOT be limited to 512 MB. With Enterprise Manager, check the max memory
setting for the server and check the box to allow SQL to Dynamically manage
the memory. -AND, it doesn't sound as though your issue is related to
memory. (But if you are running MSDE or SQL Express on your desktop
computer, that it a different issue.)
Second, your Access application probably 'should' be using an OLEDB (or ADO)
connection instead of ODBC.
Third, it is extremely poor use of SQL Server to be using 'Many loops' in a
procedure. SQL Server handles data in sets -and very fast and efficiently.
We 'should' not step through data one row at a time.
If you were to post your code to one of the Access newgroups, someone may be
able to help you in making it fast and efficient.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stephen" <smarcoux@.cbgi.qc.ca> wrote in message
news:Oapt9$7uGHA.1296@.TK2MSFTNGP02.phx.gbl...
> I'm running an access application on my local server (SQL Server 2000)
> On my win 2000 it worked perfectly.
> On my win XP, I always get this error message :
> 3151: ODBC--Connection to 'MyODBC' Failed.
> This happens when I want to run a procedure that fills a table with
> records from other tables. There's a lot of Select in this code but always
> on the same recordset. Many loops. I see my record number going fast in
> the bottom of the screen but it always stops at 207. When I do it in
> debug, no problem... can't find where's the problem.
> I allowed the maximum quantity of memory for SQL Server (512) and I have 2
> Go of memory on my computer.
> Help please!
> Steph
>