Creates db1, db2 on "compact on close"

[germaine.oliver]

Smart people: I have a split database I have been using for over a
year with no funky issues. We add and delete records every day, so I
have "compact on close" set up on the front end db. Last week, it
started a) creating the db1, db2, etc, and not deleting them and b)
not compacting. db1, db2, etc are all functional databases. My
understanding of the process is this:

1. User closes db
2. Access creates a new db, and compact it, naming it db1, db2,
whatever.
3. Access deletes original, then renames db1 to original file name.

Mine seems to be hanging at step three. I get a new, compacted db, but
it's got a new name (db1, db2, whatever), and my original uncompacted
db is still there.

So, did some testing, found this out:
1) It only occurs on network drives. C: acts normally.
2) It occurs on both back end and front end dbs. So even moduless db's
do this, so it's not a corrupt db issue (I did a decompile, made no
difference, just for the heck of it).
3) I have all permissions on the folder except "full control" and
"special permissions". That is set my out IT folks, and that hasn't
changed. I can add and delete stuff all the time, so I don't think
it's a security thing.
4) I have oodles of space, so it's not the space shortage issue.

Thoughts? I'm stumped. I can't keep creating infinite dbs, and have
the db keep growing, and growing.... I need to compact!

Help!

 

[Pete D.]

Security has changed somewhere, have your IT guys give you full rights to
directory long enough to do some testing. Check the rights on the db1,
orginal can't be deleted for some reason.

 

[Norman Yuan]

If you app is split into front/back end, there is no need to compact front
end, unless the fron end contains lot data and being added/deleted
frequently (such as lots of temp data is used in front end's local table).

The front end is supposed to be installed locally. Do not have multiple
users share one copy of front end on network share.

 

[germaine.oliver]

I have the front end on the server, or else I have to update everyone
when they're actually in the office. Each person has their own front
end (located on the server), so there are no multi-user issues on the
front end...

And for some reason, even my front end grows, even though there is
literally no data in it. I'm thinking of adding a table to the front
end for version numbers (which would be a way to install to local
computers without my having to be actively involved). So I want to
compact it, if possible, unless it's just a total waste of time. And I
can delete the db1, db2, original file, or do any other file thing
with it from within the Windows browser. The files/folders all have
the same permissions set on it (all except full control and special
permissions). Could there be something set on Access that they
changed, that prevents Access from dinking with files? To avoid
horrible Access-born viruses, perhaps? My version of Access is Access
2003 (11.8166.8172) SP3, if that tells anyone anything. Perhaps
everyone will have this problem at next service update

I'll have to see if I can talk my IT guys into giving me full control
for five minutes... that seems to be a good next step, if politically
challenging

Thanks for the responses, and any other help would be appreciated....

 

[Pete D.]

You need to look at
http://www.granite.ab.ca/access/autofe.htm
for option to automatically update front end on workstations. Having
frontend on server makes it very slow and you lose half of the gains made by
splitting the datafile. Also if the user does anything that makes the one
frontend go into design mode it will lock everyone else out. Just a
suggestion.
Pete D.

 

[germaine.oliver]

Thanks for that link.. I actually found another link on that page
(http://www.databasejournal.com/features/msaccess/article.php/3286111)
that I think is the solution I'll utilize for deploying to a server...

Each user has a folder on the network, tied to their user name (like
john.smith) that holds a copy of their front end. So locking other
folks out not an issue... and so far, we've had no speed problems....

BUT, whether or not I'm doing something dumb with where I have my
front ends sitting, I don't think that explains my creation of db1,
db2, and failure to finish the compact cycle. Every user has db1 from
10/17, db2 from 10/18, etc. So something changed on the 16th (evening)
or 17th with SOMETHING... a folder setting, an Access setting, an
Access update, etc, etc.... I'm just stumped with what....

 

[Ron2006]

I am not sure you answered the question.

If there is not a significant difference between the user's copy and
the db1 etc that are being created there is no need to even do the
compact and repair on exit of the user's copy. Although if it just
plain keeps getting bigger, then maybe you do need to put something in
for that.

Now at some point you need to have something in your whole system
structure/design that can do a compact and repair on the BE. That is
where the data is and where you can get savings, both in space and in
response. BUT everyone has be be out of the application what that is
done.

Have you tried running the system with the FE on the person's work
station? It may be worth trying to see if you can see better response
time. (But then it can depend on the types of updates, etc that you
are doing.)

 

[DiHo]

I have the same exact issue that started just after SP3 was installed. I was
trying to track down security/permission issues (since, like you, it works
fine on C drive), but came up blank. I have since had more serious problems
(crashes!) that I just learned are SP3 bugs, so I would have to assume, based

 

[germaine.oliver]

It keeps getting weirder. I was in on the weekend, and it was doing it
consistently over the network, not at all on C: drive. I made a one
table, one record db, it didn't compact (made db1, db2, etc). On
Monday, it was only doing it on SOME dbs over the network. Some are
fine, some aren't. And if I move a "non fine" one, it stays "non
fine". But the one I'm the most worried about (that I tested a zillion
ways on Saturday) is fine today, as are three others I used for
checking...
(including my backend).

As far as SP3, we've had that installed for a while... I don't think
that is related to my issue, but who knows. If you changed something
and now have a problem, it's easy to attribute that problem to it.

My IMD folks say NOTHING was changed when my problem started, and
NOTHING was changed when it sporadically stopped. Getting full
permissions makes no difference, it doesn't do it on the local, etc,
etc.

Weird bug... that's all I can think of. I can come up with no other
explanation of variable, sporadic behaviour. It's my least favorite
diagnosis, but I can see no other explanation.

And I appreciate everyone's input on unrelated issues, as well (the
"should front-ends be on the server", and "should front ends compact
on close"). Those side issues have caused me to look at a couple of
other things as well, and I'm smarter for it. Alas, it appears there
is no solution for "weird bug"....

Any other thoughts on how/why this issue started, I would appreciate
it!

 

[germaine.oliver]

The only difference between db1, db2, and the original file (and each
other, btw) is size. They are all fully functional versions. But the
size is notably different: Original: 6792kb. db1: 4008kb. db2: 4012
db3: 3960. But if I open db1 (functional version) and close it, it
grows to 7684, and db4 is created, which is 4008. So my front end
(with no organic tables, only linked) grows when I open it. Is that
unique? I always thought things grew when you opened them... that's
why I have "compact on close" set.

Here's something odd... I took "c on c" off, and my db stays about
6800. Put it on: my db grows to 8400, and dbx is created that is 4204.
Do it again: db is 8418, db(x+1) is 4204. So my front end keeps
growing...

If that is the issue: what would cause that? I explicitly set all
created objects and recordsets to nothing. Maybe there is one or two I
missed? I'm going to go through it with a fine tooth comb...IIRC,
that's the biggest culprit in "growing" dbs...

 

[Pete D.]

One more thought, it may be user related to someone's logon. Might have
each user do it one at a time. As nothing changed on the network maybe
someone changed a user profile and he/she creates a file others can't
delete...or something like that. One of the reasons I don't have compact on
close is depending on user profiles other problems can arise. Better to
add something to your scheduler with admin rights to compact it once a day.

 

[germaine.oliver]

The FE is only open by one user at a time....and when I first noticed
this problem was on the weekend, when I was the only one using it. It
had been going on for a few days by then, but it's not a multi-user
issue. But thanks for the thought....

I wonder if it's only the two of us having the problem....

 

[germaine.oliver]

yes. I have the same issue. Need help

EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com

Figured it out: it's a SP3 issue. SP3 got pushed 10/17, which is when
this magically appeared. So I emailed MS, as did my IT dept, and we'll
see if anything happens...

Very annoying... how many hours did I put into dinking with it. But on
the plus side, I got smarter on a couple of things...

 

[djscoville]

Figured it out: it's a SP3 issue. SP3 got pushed 10/17, which is when
this magically appeared. So I emailed MS, as did my IT dept, and we'll
see if anything happens...

Very annoying... how many hours did I put into dinking with it. But on
the plus side, I got smarter on a couple of things...

We have the same issue university-wide on network drives. Has anyone
found a soultion yet?

Thanks,
djsco