By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,570 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,570 IT Pros & Developers. It's quick & easy.

Crash on acOutputTable

P: n/a
XP system. Access2002 (10.6501.6714) SP3. VBA 6.3

Access has been crashing at a certain point in code execution.

Code extract>>
Clear the old records from this table which is a temporary table for
the records which are used for merging in Word.
cnn.Execute "DELETE * FROM tblReadersLicences;"

rst.Open "SELECT * FROM tblReadersLicences", cnn, adOpenKeyset,
adLockPessimistic
rst.AddNew
rst!code = strCode
other fields filled….
rst!SpellSchemeMonth = strSchememonth
rst!SpellSchemeYear = strSchemeyear
rst.Update
rst.Close
The next line causes the system to pause for a given period.
TimerInterval of 4000 works, whereas 3000 does not. This is used to
stop the output file containing the data that was in the table before
it was substituted. Access seems to require a considerable time for
the new records to become assimilated.
DoCmd.OpenForm "frmPrintReport", acNormal, , , , acDialog, "Printing
Licence"

DoCmd.OutputTo acOutputTable, "tblReadersLicences", acFormatRTF,
strRTFFolder & "\Licences.rtf", False
The system crashes on executing the above line, unless the pause time
is extended sufficiently.

Is this a known phenomenon? If so, is there a surer way of avoiding
it?
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
First, verify that rst is Dim'ed as a DAO.Recordset. If not, I would expect
problems prior to where you're having them, but it won't hurt to check.

There is a command you can use to give Access the time to write everything
to disk.

DBEngine.Idle dbRefreshCache

--
Wayne Morgan
Microsoft Access MVP
"tom blower" <to*******@tiscali.co.uk> wrote in message
news:91**************************@posting.google.c om...
XP system. Access2002 (10.6501.6714) SP3. VBA 6.3

Access has been crashing at a certain point in code execution.

Code extract>>
Clear the old records from this table which is a temporary table for
the records which are used for merging in Word.
cnn.Execute "DELETE * FROM tblReadersLicences;"

rst.Open "SELECT * FROM tblReadersLicences", cnn, adOpenKeyset,
adLockPessimistic
rst.AddNew
rst!code = strCode
other fields filled..
rst!SpellSchemeMonth = strSchememonth
rst!SpellSchemeYear = strSchemeyear
rst.Update
rst.Close
The next line causes the system to pause for a given period.
TimerInterval of 4000 works, whereas 3000 does not. This is used to
stop the output file containing the data that was in the table before
it was substituted. Access seems to require a considerable time for
the new records to become assimilated.
DoCmd.OpenForm "frmPrintReport", acNormal, , , , acDialog, "Printing
Licence"

DoCmd.OutputTo acOutputTable, "tblReadersLicences", acFormatRTF,
strRTFFolder & "\Licences.rtf", False
The system crashes on executing the above line, unless the pause time
is extended sufficiently.

Is this a known phenomenon? If so, is there a surer way of avoiding
it?

Nov 13 '05 #2

P: n/a
There is nothing in the *posted* ADO code that should cause a problem or any
sort of delay, unless you are experiencing some unknown conflict with
pessimistic locking (simply switching to optimistic locking will eliminate
that possibiltiy). Unless you are operating over a WAN or a very slow LAN,
there should be no delay whatsoever, that you would need:
This is used to
stop the output file containing the data that was in the table before
it was substituted.
Are you using CurrentProject.Connection? If you are not, the delay could be
caused by unneccesarily creating a new connection. In short, this
statement:
Access seems to require a considerable time for
the new records to become assimilated.
is not *normally* correct.

Could you post the *entire* ADO code for deleting/filling this table?
Darryl Kerkeslager

"tom blower" <to*******@tiscali.co.uk> wrote: Access has been crashing at a certain point in code execution.
rst.Open "SELECT * FROM tblReadersLicences", cnn, adOpenKeyset,
adLockPessimistic
rst.AddNew
rst!code = strCode [snip]
The next line causes the system to pause for a given period.
TimerInterval of 4000 works, whereas 3000 does not. This is used to
stop the output file containing the data that was in the table before
it was substituted. Access seems to require a considerable time for
the new records to become assimilated.

Nov 13 '05 #3

P: n/a
Disregard the previous if you're using ADO. ADO does have a Flush method to
force a write, but the help file says that Flush is done automatically when
you do a Close.

Where is the table located that you are writing to? If it is across a
network, could there be a network problem? What happens if, in addition to
closing the recordset, you close or close and reopen the connection before
you do the acOutputTable?

--
Wayne Morgan
MS Access MVP
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:d_****************@newssvr31.news.prodigy.com ...
First, verify that rst is Dim'ed as a DAO.Recordset. If not, I would
expect problems prior to where you're having them, but it won't hurt to
check.

There is a command you can use to give Access the time to write everything
to disk.

DBEngine.Idle dbRefreshCache

--
Wayne Morgan
Microsoft Access MVP

Nov 13 '05 #4

P: n/a
Thanks for the helpful comments.

I do not think it is a LAN problem as I am coding standalone and the
crashes occur here. I have (as suggested) changed to using optimistic
locking and not only does that seem to prevent the output "ignoring"
the new record(s), but did not cause the crash either. I shall run
with this for a while and report back.

Darryl
This is the full sequence of settings for establishing the recordset.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
------
Set cnn = New ADODB.Connection
cnn.Open CurrentProject.Connection
------
' Delete record(s)
cnn.Execute "DELETE * FROM tblReadersLicences;"

‘ Now prime the pump!
rst.Open "SELECT * FROM tblReadersLicences", cnn, adOpenKeyset,
adLockPessimistic
rst.AddNew
other fields covered
rst.Update
rst.Close

Form pauses system
DoCmd.OpenForm "frmPrintReport", acNormal, , , , acDialog, "Printing
Licence"

DoCmd.OutputTo acOutputTable, "tblReadersLicences", acFormatRTF,
strRTFFolder & "\Licences.rtf", False
Nov 13 '05 #5

P: n/a
Since my last comment, the crashes have re-occurred. The only way I
could stop it was to compact and repair, when it ran OK one time and
then reverted. I then tried to create a new databse and import, but
that failed halfway.
I then decided to uninstall OfficeXP, defragged the disk and
reinstalled.
I have not yet installed any updates, but Access crashed again.

Any ideas? It is getting a bit desperate.
Nov 13 '05 #6

P: n/a
I assume that you are not running the particular code you posted when it
crashes?

Here is the only problem I see in the code you posted. I don't know whether
it would cause an Access crash or not.

Replace:

Set cnn = New ADODB.Connection
cnn.Open CurrentProject.Connection

With:

Set cnn = CurrentProject.Connection
If that does not fix the problem, or if your problems are occurring even
when you don't run the code you posted, then there is something else going
wrong other than the code you posted. Re-post the complete description of
the problem, and test with the most basic mdb that crashes, and describe
exactly what happens ('crashes' is too vague). Someone will know what the
problem is in this newsgroup, but the more specific you can be, including
version number, service pack, and the steps you take, and the more basic the
mdb, the better chance of getting the answer on your first post.
Darryl Kerkeslager
"tom blower" <to*******@tiscali.co.uk> wrote in message
news:91**************************@posting.google.c om...
Since my last comment, the crashes have re-occurred. The only way I
could stop it was to compact and repair, when it ran OK one time and
then reverted. I then tried to create a new databse and import, but
that failed halfway.
I then decided to uninstall OfficeXP, defragged the disk and
reinstalled.
I have not yet installed any updates, but Access crashed again.

Any ideas? It is getting a bit desperate.

Nov 13 '05 #7

P: n/a
I will try your amendment. Actually, I did give the version no etc in
my first message.

In any event, I have cracked it!

I put a break on the line of acOutputTable and then pressed F8 and
there was never a crash (Access closed, I now have 13 backups,
although the MS error reporting did not always appear). If I restarted
the code, I got a crash.

As the crashes always occurred at the point where the table was being
output, the output ticker at the bottom of the screen had started, I
took a look at the table being output. There were a number of
redundant fields, so I copied the table, deleted those fields and
using the new table the beauty has not crashed since. Fingers crossed!
I may say that I have been outputting tables for ever using virtually
the same code, but with other tables.

One of the fields lost was a memo field, but I have that elsewhere
with no problem. I will test further to see if there was some
corruption in the original table and report.

Thanks
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.