Problem with an updating query in a VBA of Access 2002

[Elias Nhancale]

I have always a problem (error) in the code of my
application, when it runs an updating query. It happens
that my application was built in Access 97 (where it runs
perfectly), I converted it to Access 2000, where I had a
problem when I run query form, that runned perfectly in
Access 97.
Then I converted the application to Access 2002, to see
if the problem could persist. In Access 2002 my form,
runned perfectly as it does in Access 97.

But all updating queries (saved in the database) called by
code doesn't run, they show-me an error of DAO..., ODBC ...
( ODBC because I'm using one). But this error didn't appear
in Access 97 and in Access 2000. So I don't know what to
do, because updating queries are called in many modules of
my seven(7) databases which form my application.
Please Can you help me to fix this problem. I expect your
answer very soon.

Regards
Elias

 

[Allen Browne]

When you create a new database in Access 2000 and 2002, you do not get a
reference to the DAO library automatically.

From any code window, choose References on the Tools menu, and check the box
beside:
Microsoft DAO 3.6 Library

Deselect the reference for:
Microsoft ActiveX (ADO) 2.xx Library
or else replace all occurrances of "As Recordset" with "As DAO.Recordset"

 

[Allen Browne]

Okay, how are you executing the update queries?

Do they work if you:
dbEngine(0)(0).Execute "MyUpdateQuery", dbFailOnError
I'm particularly interested in knowing if the dbFailOnError switch makes any
difference.

Do you receive any error message when this runs?
If not, does dbEngine(0)(0).RecordsAffected give any clue?

Do they work if you run them from the query design window?

Does changing the query's Use Transactions property make any difference?

Do you have the 3 boxes checked under:
Tools | Options | Edit/Find | Confirm
Try:
DoCmd.SetWarnings True

Which Service Pack do you have (see About from the Help menu)?

 

[Elias Nhancale]

Thank you Allen for answering again and for understanding
the problem I'm facing.

About Code, it writtent as below:
'Below: data_trab is a date parameter
'Ledatatrabalho() returns perfectly a date value
Dim BD As Database
Dim rst As Recordset
Dim qdfCambio As QueryDef

BeginTrans
Set BD = CurrentDb
Set qdfCambio = BD.QueryDefs("XDelCambio")
qdfCambio("data_trab") = Ledatatrabalho()
qdfCambio.Execute dbFailOnError 'This works perfectly

Set qdfCambio = BD.QueryDefs("XInsCambio")
qdfCambio.Execute dbFailOnError 'This works perfectly

Set qdfCambio = BD.QueryDefs("XUpdDataCambio")
qdfCambio("data_trab") = Ledatatrabalho()
qdfCambio.Execute dbFailOnError 'The problem is here
'and gives the error in a message box:
-245: sqlerrm (cambio)(ODBC.QueryDef)
3146: ODBC -- call failed. (DAO.QueryDef)

Allen this is about code! It also has CommitTrans when
it succeed, and Rollback from which it searches for error
msg

So I think that in the code you see your first two
suggestion. If you think that my code is not correct to
implement your suggestions or if you think something is
missing in my code please do tell me! I remind you that
this application was running without this problem under
Access 97 and that the code was the same as above.

About DbEngine(0)(0).RecordsAffected I didn't try it.

All the queries above run in the database or design
window, including the updating query. So I can successfuly
implement the code by running manualy the queries one
after the other in the database window and giving them the
parameter asked (the data value, which I know).

I didn't try the query's Use Transaction property. Do you
think that is truly necessary ?

All the 3 boxes under:
Tools | Options | Edit/Find | Confirm are checked.
They are: Record Changes, Document deletions and Action
queries.

I didn't try Docmd.SetWarnings True. Do you think that is
truly necessary ?

And now about Service Pack, I have problem installing the
Service pack 1 for Office XP. The microsoft site diagnoses
my system and it detects that I need Services Pack 1 and 2.
I begin with 1, during the process he asks me the disk, I
insert it and then he continues (it means that he accepted
the disk I inserted and it is in fact the disk used for
installing XP). Then it stops and gives the following
message:

Error 1328: Error applying patch to file
C:\Config.Msi\PT2B.tmp. It has probably been updated by
other means, and can no longer be modified by this
patch. ...Retry or Cancel.

I thank you for the time you are according to my problem.
Regards
Elias

 

[Allen Browne]

Because this runs correctly in A97, I think there's a very good chance that
applying the service packs will solve the problem. To resolve this issue,
you might uninstall Office 2002, and install again. Then try to apply the
service packs. (There was a utility to completely remove Office 2000, but I
don't see one for Office 2002.)

Other things you could try to debug your code:
1. Temporarily remove any error handling.

2. Use BeginTrans on the default workspace, and the database in that
workspace, instead of an unqualified BeginTrans and CurrentDb(), i.e.:
Dim ws As Workspace
ws.BeginTrans
Set db = ws.Databases(0)

3. After each Execute:
Debug.Print db.RecordsAffected
so you know how many records were written.

SetWarnings is irrelevent for the Execute.

 

[Elias Nhancale]

Hi Allen!
I would like to say thank you Allen, the problem is
solved!
But I can't apply the service packs, my last hope as you
say "if it runned correctly in Access 97, then there is a
good chance that applying the service packs will solve the
problem".

Before I start writing to this discussion group I tried to
apply the service packs, in order to see if my problem
could solved.

Now you understood my problem, you adviced-me to applly the
service packs, after uninstalling Office XP (2002) and
reinstalling it again.

I did it in the Control Panel, Add/Remove Programs and it
seemed the Office XP was removed (all Office XP
application's shortcuts where removed, including the
Office toolbar).

I restarted my computer, installed again the XP, and tried
to apply Service pack 1 downloaded from the microsoft site.

But the error I referred still arises during the
installation of Service Pack 1 to Office XP, it advices me
to send a report to microsoft, I sent. Now the error has
changed the file associated to it:

Error 1328: Error applying patch to file
C:\Config.Msi\PT2F.tmp. It has probably been updated by
other means, and can no longer be modified by this
patch. ...Retry or Cancel.

I'm Asking help for you and other people that you know can
help solving installation problem. And I hope that after
installing it successfuly, the main problem is solved.

I had to convert the application from Access97 to 2000 or
2002 because the operators need new features in Word 2002,
Excell 2002,..., and in my case with the hope of having
some additional feature in Access 2002 which I can use in
new developments,.... But if the problem is not solved I
have to try, I don't know how, having only Access97 from
Office97 and Excell 2002, Word 2002, ..., from XP (without
Access 2002!) in a computer. Is it possible ? How ?

Once again, I thank you for the time you are according to
my problem.
Regards
Elias

 

[Allen Browne]

Okay. I'm not sure how to advise you about the failed service pack. If you
downloaded the patch, it's possible that the file is incomplete, and
therefore won't run. If not, it's likely that the uninstall left some values
in the Windows registry, which we used to solve in the Access 2000 days with
Microsoft's Eraser2k.exe utility, but I don't see one for Office 2002.

You may be able to talk to Microsoft Product Support about why the service
pack will not install. Alternatively, you may be able to get some help from
the groups microsoft.public.officeupdate or microsoft.public.office.setup.
Almost certainly, a clean install on a new disk would solve the problem.

It is possible to keep your Access 97 as well as Word 2002. To do that, do a
custom install of Office, and tell it not to remove the components from the
previous version. Make sure it installs into a different folder. If you
receive a message after the install saying there is no licence for Access
97, you have struck this bug:
http://www.mvps.org/access/bugs/bugs0013.htm

Hope that gives you some useful possibilities.

 

[Elias Nhancale]

Hello Juan!
The updating query I mentioned is written:
UPDATE DISTINCTROW cambio SET cambio.data_cambio =
[data_trab] WHERE (((cambio.data_cambio) Is Null));

and it is saved in the database. So it doesn't reference
to any form's control.

Thanks for trying to help.

I'm trying to install the service packs and it is still
impossible. I runned the installation directly from the
microsoft site (not from the saved pack) and it still
generates the same error:
Error 1328: Error applying patch to file
C:\Config.Msi\PT2F.tmp. It has probably been updated by
other means, and can no longer be modified by this
patch. ...Retry or Cancel.

The Auto Protect anti-virus was disabled.

And during this days, I've been asking to myself: If apply
sucessfuly the Service Packs the updating queries will run
sucessfuly in the code ? (I have many of them in several
databases, which form the application, and I didn't look
for how many times are they called in code!)

Another question: Does microsoft knows this problem
(because I think is an Access 2002 problem, in my case)?
I'm getting stressed with it and consequently losing my
trust on the Access 2002, because I think probably it will
generates more errors!

Allen and Juan if you want I can send to you the log files
produced during the installation and also a bmp image
illustrating when the error arises.

I have read all the advices for the error 1328 in the
microsoft site and I could get clear about what to do!