DAO Transaction Crash

[TC]

5500 records is nothing.

I would definitely add the DAO. prefix to all applicable objects in the code
in question, & see if that fixes it. Or maybe, if you are not using ADO at
all, just delete the ADO reference.

HTH,
TC

 

[Paul Sweeney]

Hi!
I have an Access XP database, originally created in
Access 97. One of the forms contains a procedure that
takes values from one set of records then either modifies
or adds records to a second recordset, based on the
values in the first recordset. The code has been running
for several years. Very recently, the procedure has been
consistently crashing Access XP. The crash occurs just
after the "Commit Transaction" function is performed. It
turns out that the transaction appears to be properly
completed, but at the risk of Access crashing. (Note:
The code is using the DAO transaction method, not ADO.
Further, the variables for the recordsets and the
database are declared like this:

Dim db as Database
Dim rst1 as Recordset
Dim rst2 as Recordset

They are not declared more explicitly like:

Dim rst1 as New DAO.Recordset

I'm not sure if any of that matters.)

Final note: The "crash" is a caught by the error
monitoring system in XP, the database is restarted,
backed up, and compacted.

I don't know the cause. Could it be related to the
amount of records involved, possibly overloading memory?
The destination recordset has about 5500 records. The
source could have anything from a couple records to
several hundred. Lastly, the problem is happening on
more than one machine. The machines are all fairly new,
running Windows XP Pro, and have at least 256MB of RAM.

I'm considering redoing the code using ADO to see if it
helps.

Any ideas???

Thanks!
Paul Sweeney

 

[Wayne Morgan]

You may want to try downloading Jet4SP8 and see if that helps. It should be available on
www.windowsupdate.com. Also, check your references (open a code module and go to
Tools|References) and see which version of DAO you are using. If it is 3.5, uncheck it and
scroll down the list and choose version 3.6.

If that doesn't work and your file is in Access 2000 format, you may want to try a
decompile. If it is in Access 2002 format, try creating a new database and importing all
of the objects into the new file. Make a copy of the database before you do this in case
it trashes the database.

To decompile, open the database with the following command line:
"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /excl /decompile

Include switches for workgroup, user, and password if you normally need them. Once the
database is open, immediately compact it (Tools|Database Utilities|Compact and Repair).
When it reopens from that, open a code window and go to Debug|Compile.... to compile the
database.

Definitely change your Dim statements. You should be able to use search/replace (Ctrl+H)
to do this.

Find What: As Database
Replace With: As DAO.Database
Search: Current Project

Do this for recordsets, querydefs, tabledefs, and any other DAO objects you may have.

 

[Paul Sweeney]

TC:

Sorry I didn't reply sooner. I was pulled away on other
issues and forgot to check. I'm glad you mentioned that
5500 records "is nothing". I didn't think it should
cause the program to crap out. I'm going to try
explicitly naming the DAO recordset. We'll be converting
the system over to SQL Server in the next couple months,
so I'll be putting more and more ADO code into the system.

Thanks for your input!
Paul

 

[Paul Sweeney]

Wayne:

Thanks very much for your thorough reply! I've been
sidetracked on other issues, including a possible merger
and subsequent huge expansion of the database, including
migrating to SQL Server.

I will try your suggestions and see what happens. In
reference to Jet4SP8, should this be installed on only
the front end (local user) machines and on the server or
just the locals? (The reason I ask is that I'm having a
problem with very slow performance when opening queries,
etc., in design view. A KB article mentioned this
problem when using Access 2000 with Windows NT server,
and different Jet versions on the local machines and NT
server. Although we are using Access XP on Windows XP
Pro (locals), and Win 2000 Server, this is the exact
problem I'm having.)

Thanks very much for your help!

Regards,
Paul Sweeney

-----Original Message-----
You may want to try downloading Jet4SP8 and see if that

helps. It should be available on

www.windowsupdate.com. Also, check your references (open a code module and go to
Tools|References) and see which version of DAO you are

using. If it is 3.5, uncheck it and

scroll down the list and choose version 3.6.

If that doesn't work and your file is in Access 2000 format, you may want to try a
decompile. If it is in Access 2002 format, try creating

a new database and importing all

of the objects into the new file. Make a copy of the

database before you do this in case

it trashes the database.

To decompile, open the database with the following command line:
"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /excl /deco mpile

Include switches for workgroup, user, and password if

you normally need them. Once the

database is open, immediately compact it (Tools|Database Utilities|Compact and Repair).
When it reopens from that, open a code window and go to

Debug|Compile.... to compile the

database.

Definitely change your Dim statements. You should be

able to use search/replace (Ctrl+H)

to do this.

Find What: As Database
Replace With: As DAO.Database
Search: Current Project

Do this for recordsets, querydefs, tabledefs, and any

other DAO objects you may have.

 

[Wayne Morgan]

I would upgrade the server to Jet4 SP8 also. It will be used by IIS, Exchange, and
possibly some 3rd party applications that you may have. Again, it should be available by
going to Windows Update.

For speeding up the database, try the suggestions here:
http://www.granite.ab.ca/access/performancefaq.htm