Do .execute queries take a lot of resources? 1

[steve728]

I've recently converted old ADO rs.open action queries (INSERT INTO and UPDATE) into ADO.execute ,,129 ones. Now my MS Access 2003 application is frequently closing down. Any ideas how to prevent this? The first time the program runs (including about 10 30 action queries) it works fine. Run it again and it shuts the application down. If I close MS Access and rerun the application all works fine until I run the procedure for the 2nd time in this session.

Steve
O/S Win XP, 768 RAM, Processor: 2.7Mz, No other apps running. Please help!

I open the connection prior to running these queries then close the connection after the last run runs. Code Sample:

CurrentProject.Connection.Execute "INSERT INTO PRRTRANS ([reference], mdate,[name],empcode,[date]) Values ('" & Nextone & "', #" & Now() & "#, '" & Trim(rsPRCheck.Fields("name").VALUE) & "', '" & rsPRCheck.Fields("empcode").VALUE & "', #" & prDate & "#)", , 129

 

[cmmrfrds]

It sounds like you are creating objects in your code and then leaving them without destroying them.

Do you have a
Set yourobject = Nothing
when finished with the object.
Especially check any loops that are creating objects.

 

[steve728]

Good thought but I always close my recordeset objects and set them to nothing at the end when they are not needed. I also close the current ado connection when done but do not set it to nothing because of the frequent times that it is used throughout the application. Any other ideas? I appreciate your quick reply on a Sunday.

Steve

 

[cmmrfrds]

The example uses.
CurrentProject.Connection.Execute

What do you mean by closing the current ADO connection?
Show the code.

 

[steve728]

adoConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = 'T:\WA\WAData.mdb';")

CurrentProject.Connection.Execute "INSERT INTO PRRTRANS ([reference], mdate,[name],empcode,[date]) Values ('" & Nextone & "', #" & Now() & "#, '" & Trim(rsPRCheck.Fields("name").VALUE) & "', '" & rsPRCheck.Fields("empcode").VALUE & "', #" & prDate & "#)", , 129

CurrentProject.Connection.Close

Hope this helps!

 

[cmmrfrds]

This is closing the projects connection not the one you created, that would be a problem.
CurrentProject.Connection.Close

To use your connection.
adoConnection.Execute


and then
adoConnection.close

 

[steve728]

I'll try it out immediately and let you know.

Steve

 

[steve728]

The changes you suggested helped lessen the use of memory resources but the program still shut down on the second run!&^%$#

Steve

 

[cmmrfrds]

What happens if you also destroy the connection when closing so that it creates a new connection the second time through.

adoConnection.close
Set adoConnection = Nothing

 

[steve728]

I'll try it.

 

[steve728]

The application is still shutting down.

 

[cmmrfrds]

Is this the application you are working in.
'T:\WA\WAData.mdb'

Or are you in another mdb?

 

[steve728]

I have the application split from the data.
Application: WA.mdb
Data: WAData.mdb
Both containers compacted and repaired recently.

Any clues left? I'm willing to pay for help.

Steve

 

[cmmrfrds]

Can you try using the currentproject.connection instead of creating a new connection.
Try creating a new connection this way from the CurrentProject.Connection.

Dim adoConnection as ADODB.Connection
Set adoConnection = New ADODB.Connection
Set adoConnection = CurrentProject.Connection

and then use adoConnection in your code.

If this doesn't work then just use CurrentProject.Connection but don't close it.

 

[steve728]

I'll try it out after dinner. Thanks so much for your replies. I really appreciate it. I'll let you know what happens.

Steve

 

[lespaul]

.....so what happened???????? Don't just leave me hanging!

Leslie

 

[steve728]

I'm sorry I kept you waiting. The application still shuts down. I used a freeware program to display the available RAM throughout the running of the code. It's called FreeRAM XP Pro. After I run the procedure I explained up above there is very little RAM left. I then run the procedure again and bang, the application shuts down. If I close the application after running the code and restart, I have no problem running it once again. What the heck is going on. These .executes seem to hog up a lot of memory! I made sure to open a connection both ways as you suggested, at the beginning of the process and close it at the end of the process.

Please think of any other remedies! I'm taking off on an appointment today but will anxiously return to read my e-mail.

Steve

 

[steve728]

cmmrfrds,

I got the following error message

"Operation is not allowed when the object is open."

when trying the above lines of code including the "Set adoConnection = CurrentProject.Connection"

Thanks again for your replies. Man! I hope I get through this problem. BTW I ran this program on another computer that has a Athlon 64 and 1.5GB of RAM. I had no problems what's so ever. On my own computer, that I up above, I increased the virtual RAM. It helped a little but still no go. HOpe this gives you more clues...

Steve

 

[steve728]

Hi friends! I found out more info regarding this subject!!! I ran the same processes on a PC that has Office 2002 installed. I had no problem what so ever. I ran the same processes about 12 times and never experienced the soaking up of RAM and shutting down. This problem is obviously a bug in version 2003. My son and I will check around the Internet for help. Please do the same. Whoever finds a solution/fix please post it here.

Thank you for all your inputs!

Steve G.

 

[PHV]

Why not simply use the DoCmd.RunSQL method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886