SQL Time Out on ADP

[kcmccc]

I have an ADP/ADE file linked to SQL 2000 with about 10 users. As users log in and logout SQL does not release the users memory, and so the memory usage continues to grow until eventually SQL begins to give Time Out errors to the database users. Any idea why this might be happening or what I can do? I have be dilegent about closing recordsets, optimizing queries, etc....

 

[cmmrfrds]

Are you using a lot of recordsets in vba code. How do you clean up after using a recordset? What other types of things are you doing with sql server. Stored Procedures and do you use temp tables? What are the error messages? Do they happen after a user goes through a certain sequence of events? Need more to work with.

 

[kcmccc]

I do use alot of ADO recordsets, but I close/set them all to nothing when done with them. Since this is an ADP, all of the stored procedures and views are in SQL. There are alot of them as the database is quite large. There is no one sequence that triggers the error, a simple "Time Out Expired" message, but it happens when them memory usage on the server gets to a certain point, and seems to happen when the user does something that calls ADO code that either adds to or deletes from a table. I do not use SQL temp tables but rather use the old fashioned Access way of deleting and adding records to a table on the server. This is not done frequently, only in a handful of cases.
Thanks for your help!

 

[cmmrfrds]

The default timeout on a connection object or command object is 15 seconds and 30 seconds or vise/versa - look up.

Dim cn as new adodb.connection
Dim cmd as new adodb.command

cn.CommandTimeOut = 0 'no timeout
cn.CommandTimeOut = 300 '5 minutes
cmd.CommandTimeOut = 300 '5 minutes

Are you running large update batches? Are you locking pessimistic or optimistic?

 

[kcmccc]

I do have some rather large updates. I am using optimistic locking. I have not tried changing the time out option through Access, but I had done so on the server with no results.

 

[bcorrigan78]

Not being an access guy, let me say that I am having the same problem and although I understand what the code referenced above does, i dont understand how to intgrate it into my access adp project. Cany anyone epxlain this further? Do I crate module and paste the code into it?

In access.. Im clueless.

Thanks!