Access 97 Gurus?

[Larry Struckmeyer[SBS-MVP]]

Anyone have a good grasp of a 15 year old program?

One of my customers is having an issue with Access 97.

Briefly:

4 different mdf files live on Server 2003, which is also a TS.

All 4 are opened and used directly on the TS by RDP clients, over the LAN
and over VPN from remote locations.
All are opened and used by same or different workstations on the LAN over
the wire in the traditional way without RDP.

Some Access is 97 SR1
Some Access is 97 SR2

Some databases seem OK
Some throw errors from SR2 but not SR1 versions.

Originally, It was only one database that had issues. That one was used
only by SR2 versions on the LAN but the TS was SR1, so I installed SR2 on
the TS. That database now seems fine, works as expected, afaik, from the
SR2 stations and on the SR2 TS. According to the literature there is one
more possible step, involving Jet Database 3:

http://support.microsoft.com/kb/182867

The 3197 error in that KB is the one that started this, on the database mentioned
above.

But installing SR2 on the TS, along with all the subsequent patches, fixes
and permissions issues that must be repaired, has “broken†another database,
which seems fine on the two remaining SR1 stations on the LAN, (which only
open this one database), but not on the SR2 TS or the SR2 stations.

Thinking that the databases don’t like being opened by two different versions
of the program, I would like to install SR2 on the remaining stations and
see if that fixes it across the range, but this is the Time Clock database
and payroll is Monday, so I can’t do anything until after Monday. I also
can’t leave it unusable, as they run their entire payroll system from there.
But for the remote offices to record their time, this has to work over the
TS as well.

Access 97, indeed the entire Office 97 suite leaves all sorts of crap behind
after uninstall and eraser, so uninstalling whatever parts of Office 97 are
installed does not clean up the computer, so a reinstall of Access seems
to be still SR2 without a LOT of manual work. So my plan would be to image
the drive, put in a spare drive and install SR2 on the spare so I can go
back and forth.

Another option would be to close the database from any SR1 station to see
if it would then open with SR2.

-
Larry Struckmeye

 

[Douglas J. Steele]

What exactly do you mean by "broken"?

If I recall correctly, since SR2 installed a new version of the Microsoft
DAO 3.5 Object Library, references could indeed get fouled up. The solution
is to open the database that itsn't working, open a code module (or simply
open the Immediate Window using Ctrl-G) then select Tools | References from
the menu. Look for MISSING: in front of any of the selected references
(they'll all be at the top of the list). Assuming that the reference to
Microsoft DAO 3.5 Object Library is, in fact, marked as MISSING:, unselect
it, back out of the References dialog then go back in, scroll through the
list of references until you find the one for the Microsoft DAO 3.5 Object
Library and select it.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Larry Struckmeyer[SBS-MVP]]

Thanks for your help.

Dosen't seem to be anyting missing in Resources. There have been a few different
errors when attempting to use this database with SR2, and the KB's we have
found have been helpful. But the current one, which I have not been able
to fix is:

Run Time Error '13': Type Mismatch

This occurs when using the database from the TS, where both it and Access
97 Lives. it did not occur prior to installing SR2.

If we get that one fixed there may be more, but won't know intil we get past
this one.

I can't do this until Monday, but thinking of coping the database to a local
station with SR2 and playing with it there until I get a fix so the one station
that works will allow employees to record there time in the main office.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[Larry Struckmeyer[SBS-MVP]]

More Info:

The References are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Utility

none of the others are checked.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[Steve]

Larry,

If you click on the right button in the error message, it ought to take you
back to the code window to where the error is highlighted.

Steve
(e-mail address removed)

 

[David W. Fenton]

Some Access is 97 SR1
Some Access is 97 SR2

Get them all to SR2 and the last Jet 3.5 service pack (SP3). Don't
waste time on anything else until that's done.

 

[David W. Fenton]

The References are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Utility

none of the others are checked.

It's 99% likely that the Utility reference is unnecessary -- that's
a legacy of Access 2.

 

[Larry Struckmeyer[SBS-MVP]]

Thanks everyone. I think we may be getting to the bottem of this.

The info to move to SR2 and Jet 3.5 SP3 is hopefully the info I was looking
for. I had seen the KB article, but I was just holding off doing this because
the rest of the databases seem to be working properly. However, without
auditing every machine that opens every database, it is impossible to say
for sure.

One of the stations is Windows 98 SR2. All the others are Windows XP SP3.
Any issues here that anyone knows about. The W98 station is the main source
for employees clocking time using the time mdf, along with the Server 2003
TS.

Only one XP station opens this particular mdf, the payroll clerk. And the
time mdf is the only one opened by W98.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[Arvin Meyer [MVP]]

In addition to David's comment, which I agree with. Every machine needs to
be totally upgraded. There are some really nasty bugs that randomly occur,
if I remember correctly, and you need a full set of SPs to fix them.

Also, and this is crucial. Make sure that all databases are split and only
the data is on the server. In the case of TS connections make sure there is
a folder for each user with a copy of the front end in it. Anything other
than that will eventually cause corruption, sometimes irreparably.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Larry Struckmeyer[SBS-MVP]]

Thanks Arvin.

Question: Every machine needs to be totaly upgraded? Are you referring
to Access? Beyond SR2 and Jet 3.5 as suggested, what are you thinking of?

If you mean Windows, all are XP SP3, except the one 98. This program only
runs on that 98 station, one XP station for the payroll clerk, and 6 instances,
one for each remote office, in the TS. That's it.

There are other mdf files that run on other XP - Access 97 stations, none
of them are having any issues.

Regarding spliting the databases: unfortunately, I am not the programer,
just the network support person. The programer is away at medical school
and will be returning as a new shiny doctor in August. Meantime, one of
the established docs is filling in. This (and the cost of new licenses and
the learning curve) may explain why the version of Access is still '97.

These are simple routines, really. One table to keep 50 employees, one table
to keep 6 office names, one table to keep 6 doctors names, and a transaction
table to keep employee, time in, time out, doctor and location. A few simple
reports run only by the payroll clerk... time by employee, time by doctor,
time by location. The file grows to about 250MB then is archived.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[Norman Yuan]

In addition to David's comment, which I agree with. Every machine needs to
be totally upgraded. There are some really nasty bugs that randomly occur,
if I remember correctly, and you need a full set of SPs to fix them.

Also, and this is crucial. Make sure that all databases are split and only
the data is on the server. In the case of TS connections make sure there
is a folder for each user with a copy of the front end in it. Anything
other than that will eventually cause corruption, sometimes irreparably.
--

Well, since the OP has mentioned "mdf" file several times in his post, there
isn't dabase to "split". The Access applicaion he was talking must be a
front end app of SQL Server database.

To the OP: your Access application does not use MDF file. MDF file is used
exclusively by SQL Server, or you can consider MDF file as SQL Server's
internal data file, no other application can use it directly. So, as long as
the SQL Server works, your issue is purely Access issue, most likely caused
by different version of update, or even OS update (ODBC driver), since the
Access front end connects to SQL Server via ODBC, whether it runs on
Terminal Server, or on user computer.

 

[Larry Struckmeyer[SBS-MVP]]

Guess I should ask, out of all ignorance, what kind of file one would expect
from an access 97 database? I see .mdf and .ldf with the same names. I
was thinking the .ldf file was the file that allowed/tracked/? multiple users?


-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[Larry Struckmeyer[SBS-MVP]]

Sorry, My Mistake! The files are .mdb and .ldb. Guess I had .mdf on the
brain. Thanks for correcting me.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[John W. Vinson]

Sorry, My Mistake! The files are .mdb and .ldb. Guess I had .mdf on the
brain. Thanks for correcting me.

<whew> That's a relief. The most common file format for Access (up to 2007) is
..mdb.

 

[David W. Fenton]

I had seen the KB article, but I was just holding off doing this
because the rest of the databases seem to be working properly.
However, without auditing every machine that opens every database,
it is impossible to say for sure.

In several of my apps, I use logging routines that record machine
name, user name and the versions of MSACCESS.EXE and MSJETXX.DLL
when the user logs on, so I can very easily check who has bad
versions and needs to have their workstation upgraded.

This is also a big deal with Access 2000, where you must have SR1 or
later of Access and at least Jet SP6 before you'll have a stable
installation. Workstations revert to release versions when somebody
uninstalls and re-installs, or reformats or sets up a new machine.

And, for completeness sake, Access 2 is simply unusable with Jet
SP2.5.

I have found the later versions of Access to be much less
problematic in this regard.

 

[David W. Fenton]

mdf files

You mean MDB files. MDF is the file extension for a SQL Server data
file.

 

[David W. Fenton]

Regarding spliting the databases

If you don't split, you'll likely end up with an additional round of
troubleshooting. It's not that hard to do, and with Tony Toews's
front-end updater, it's really simply to push out updates (and the
updater is designed to work correctly with TS):

http://www.autofeupdater.com/

 

[Larry Struckmeyer[SBS-MVP]]

Following my own advice and request for people to post back their resolution:
<g>

Installing SR2 and Jet 3.5 on all stations has indeed resolved the issues
we were seeing on opening the .mdb files from multiple locations.

Thanks to everyone who helped.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com