Microsoft Service Pack causing problems

[Coleen]

We have a database that was originally created in Access 97. It has been
converted into Access 2000. We have multiple users who need to access this
database, so the .mdb currently resides on one of our Networks. When the
main user attempts to open this particular database, it works until she
exits one of the forms, which then throws an error which is corrupting her
database if she continues. Her service pack is #4. One of my colleagues
and I who have been trying to troubleshoot this problem have Service Pack 2,
and Service Pack 3, respectively. I do not get the error that she is
getting and neither does my colleague (who is using SP2.) We have also
noticed that the version of VBA differs from Service Pack to Service Pack.
We tested by loading a machine with Access 2000 and Service Pack 3, then
another with A2K and Service Pack 4. The error ONLY occurs with Service
Pack 4. It is a critical issue to get this resolved! Has any one heard of
this problem, and/or what can be done to fix Service Pack 4 to work
correctly with A2K? Please help... TIA



Coleen

 

[anon]

Hi,

Think about it - are you asking the MDB to send all the
forms, reports, querydefs, VBA, & macros down the wire
each time a user does anything? If so, why? The Service
Pack isn't the issue in this case: the design is.

Split the MDB in two: data at the back (Access MDB is OK
instead of SQL Server etc for up to 50 concurrent users,
depending on how well the data schema is optimised) and n
copies of the same front end - which can reside on
everyone's desktop PC. Using the database splitter wizard
gives you a start. You can then write a DBA front end with
added features / permissions, and control the world!

 

[Coleen]

It is NOT the design!!!! This database was working perfectly for three
different users before Service Pack 4 was installed. What's more, I DID
split the database! YOU HAVE NO IDEA how much research we have done to
narrow it down to the Service Pack - we didn't come to that conclusion
lightly!!! If you don't have any suggestions on how we can fix the error
with the Service Pack, PLEASE don't ASSUME that it is the database design!
I DID NOT design this database, I merely have to maintain it, so I AM NOT
being sensitive about being criticized - my colleagues and I have done
EXTENSIVE research to find out why we are having this problem, and it IS the
Service Pack!!!

(e-mail address removed)> wrote in message

 

[Lynn Trapp]

Coleen,
Have you taken a look at Tony Toews' corruptions page? If not, I would
suggest you look through there to see if you can find any possible
connections.

http://www.granite.ab.ca/corruptmdbs.htm

The most common cause of corruption is hardware. Access is very sensitive to
bleeps in a network connection.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Coleen]

Thank you This is a much more helpful response. I will check it. It
is just kind-of weird that I can access the database on the Network without
getting any error, and so can two of my colleagues that do not have Service
Pack 4 - the three machines that DO have Service Pack 4 are the machines
returning the error....which is exactly why we believe it is caused by the
Service Pack - not to mention that our Network guy first loaded his machine
with Access 2K using Service Pack 4 and got the error, then went to another
machine using Service Pack 3, loaded Access 2K on it and did NOT get the
error...thanks again.

 

[Lynn Trapp]

You're welcome. It actually could be something as simple as the NIC in the
offending machine. I'm not ruling out the Service Pack all together, but I
tend to look at hardware first. You will also want to look closely at the
Ops Locks problem that Tony describes on his page.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Coleen]

We did check out Tony's page, and one of the machines we are testing on does
have the Jet Pack 4, but is still getting the error. It seems after doing
some more testing, that the Service Pack 4 does not interpret IIF statements
when used in conjunction with an IsNull statement. We've run across 3
different places in this database where the original programmer has used
long strings of IIF statements renamed as a field in the Select Statements,
it generates an error. When we put the IIF statement directly into the
field instead of renaming it, it works; sort-of. One of the statements is
too complex uses three named fields - each has an IIF statement used in
conjunction with an IsNull Statement. Like this:

ExpCheck: IIf([Category]![Category]="Farmer-Rancher" And
[WarningDate]<DateValue(Now()) And IsNull([StopDate]),"Warning: F/R Expired
or Expiring Soon!","")

Warning date uses:
WarningDate:
DateAdd("yyyy",3,IIf(IsNull([RenewalDate]),[StartDate],[RenewalDate]))-60

There is also a DLookup which is causing problems - I've found previously
that DLookups will cause problems, and don't use them in my own development
of Access databases. Do you have any suggestion what to use instead? The
code with the error is:
=DLookUp("[PaymentDate]","Orders","[OrderID]=" & [OrderID])

BTW, the original programmer used a Microsoft Template to create this
database, which would be alright, however what he is trying to do doesn't
involve orders and inventory, it involves taxes! (Taxes are a PITA
anyways!) Thanks for any and/all of your help - I do appreciate it.

Update...I just got a call from our Network Tech, and it looks like Jet 4.0
Service Pack 8.0, may solve all of the problems...I guess it IS available
now.

Thanks again

 

[Tony Toews]

We did check out Tony's page, and one of the machines we are testing on does
have the Jet Pack 4, but is still getting the error. It seems after doing
some more testing, that the Service Pack 4 does not interpret IIF statements
when used in conjunction with an IsNull statement.

Jet 4.0 SP8 is just out. Give that SP4 installes Jet 4.0 SP7 does
going to SP8 fix the problem?

We've run across 3
different places in this database where the original programmer has used
long strings of IIF statements renamed as a field in the Select Statements,
it generates an error. When we put the IIF statement directly into the
field instead of renaming it, it works; sort-of. One of the statements is
too complex uses three named fields - each has an IIF statement used in
conjunction with an IsNull Statement. Like this:

These could easily be replaced with code on the form or report.
Which would make things easier to fix and debug in the future anyhow
instead of having a monster IIF statement.

Update...I just got a call from our Network Tech, and it looks like Jet 4.0
Service Pack 8.0, may solve all of the problems...I guess it IS available
now.

Ah, ignore my first paragraph. <smile>

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

We have a database that was originally created in Access 97. It has been
converted into Access 2000. We have multiple users who need to access this
database, so the .mdb currently resides on one of our Networks. When the
main user attempts to open this particular database, it works until she
exits one of the forms, which then throws an error which is corrupting her
database if she continues. Her service pack is #4.

Is someone else in the MDB at the same time? If so then they likely
have a different version of Jet than your main user. So what is
happening is that the "cannot change object now" or similar error
message should be popping up but Access is likely puking and
corrupting at this point because the two different Jet versions are
causing wierd things to happen.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Lynn Trapp]

Coleen,
Have you managed to install SP8 yet? If so, did this solve your problem?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

We did check out Tony's page, and one of the machines we are testing on does
have the Jet Pack 4, but is still getting the error. It seems after doing
some more testing, that the Service Pack 4 does not interpret IIF statements
when used in conjunction with an IsNull statement. We've run across 3
different places in this database where the original programmer has used
long strings of IIF statements renamed as a field in the Select Statements,
it generates an error. When we put the IIF statement directly into the
field instead of renaming it, it works; sort-of. One of the statements is
too complex uses three named fields - each has an IIF statement used in
conjunction with an IsNull Statement. Like this:

ExpCheck: IIf([Category]![Category]="Farmer-Rancher" And
[WarningDate]<DateValue(Now()) And IsNull([StopDate]),"Warning: F/R Expired
or Expiring Soon!","")

Warning date uses:
WarningDate:
DateAdd("yyyy",3,IIf(IsNull([RenewalDate]),[StartDate],[RenewalDate]))-60

There is also a DLookup which is causing problems - I've found previously
that DLookups will cause problems, and don't use them in my own development
of Access databases. Do you have any suggestion what to use instead? The
code with the error is:
=DLookUp("[PaymentDate]","Orders","[OrderID]=" & [OrderID])

BTW, the original programmer used a Microsoft Template to create this
database, which would be alright, however what he is trying to do doesn't
involve orders and inventory, it involves taxes! (Taxes are a PITA
anyways!) Thanks for any and/all of your help - I do appreciate it.

Update...I just got a call from our Network Tech, and it looks like Jet 4.0
Service Pack 8.0, may solve all of the problems...I guess it IS available
now.

Thanks again

You're welcome. It actually could be something as simple as the NIC in the
offending machine. I'm not ruling out the Service Pack all together, but I
tend to look at hardware first. You will also want to look closely at the
Ops Locks problem that Tony describes on his page.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



I
DID done
to it

 

[TimE]

I had a similar problem on a Windows 2000 Pro computer with SP4 installed.

I found that when I uninstalled SP4, that error stopped but I couldn't call any of my VBA functions. I kept getting a 'Function Name Not Known' error. I noticed that SP8 for MS Jet is included in Windows 2000 SP4. I tried our database on two identical laptops running Windows XP and found that it worked on one and not the other. I also found that when I uninstalled SP8 on the one it didn't work on, I got a change identical to the one I got when uninstalling SP4 from the Windows 2000 computer.

Eventually, I traced my problem to a Microsoft Jet 4.0 setting in the problem computer's registry.

The Key;
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandBoxMode
contained a value of 2 on working computers, but contained a value of 3 on the problem computer.

There is information on the SandBoxMode on the Microsoft web site at
http://support.microsoft.com/default.aspx?scid=kb;en-us;239482

Hope this helps!

 

[david epsom dot com dot au]

I tried to recreate SQL like that on my copy of Access 2000
and had no problems with it. I have Access 2000, Office Service
Pack 3 for Office 2000, and Service Pack 4 for Windows 2000
(which includes Jet Service Release 7 for Jet 4.0).

These are the most recent versions for Windows 2000 and Office
2000. There is a more recent version (8.0) of Jet 4.0

If you have Jet SR-4, you should upgrade all machines to
Jet SR-7 or Jet SR-8

(david)

We did check out Tony's page, and one of the machines we are testing on does
have the Jet Pack 4, but is still getting the error. It seems after doing
some more testing, that the Service Pack 4 does not interpret IIF statements
when used in conjunction with an IsNull statement. We've run across 3
different places in this database where the original programmer has used
long strings of IIF statements renamed as a field in the Select Statements,
it generates an error. When we put the IIF statement directly into the
field instead of renaming it, it works; sort-of. One of the statements is
too complex uses three named fields - each has an IIF statement used in
conjunction with an IsNull Statement. Like this:

ExpCheck: IIf([Category]![Category]="Farmer-Rancher" And
[WarningDate]<DateValue(Now()) And IsNull([StopDate]),"Warning: F/R Expired
or Expiring Soon!","")

Warning date uses:
WarningDate:
DateAdd("yyyy",3,IIf(IsNull([RenewalDate]),[StartDate],[RenewalDate]))-60

There is also a DLookup which is causing problems - I've found previously
that DLookups will cause problems, and don't use them in my own development
of Access databases. Do you have any suggestion what to use instead? The
code with the error is:
=DLookUp("[PaymentDate]","Orders","[OrderID]=" & [OrderID])

BTW, the original programmer used a Microsoft Template to create this
database, which would be alright, however what he is trying to do doesn't
involve orders and inventory, it involves taxes! (Taxes are a PITA
anyways!) Thanks for any and/all of your help - I do appreciate it.

Update...I just got a call from our Network Tech, and it looks like Jet 4.0
Service Pack 8.0, may solve all of the problems...I guess it IS available
now.

Thanks again

You're welcome. It actually could be something as simple as the NIC in the
offending machine. I'm not ruling out the Service Pack all together, but I
tend to look at hardware first. You will also want to look closely at the
Ops Locks problem that Tony describes on his page.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



I
DID done
to it