Unable to compact database converted to Access 2003 format

[rdemyan via AccessMonster.com]

I've converted my application from A2K format to A2003 format.

I tried to follow Allen Browne's protocol in getting my app into A2003
(although I was unable to find informtion on the conversion process). Lots
of decompiling and lots of compacting of the original application in A2000.
Then the app was opened in A2003 and compacted, decompiled and compacted.
Next I imported everything into a blank A2003 database. Then this db was
converted from A2000 to A2003.

I had some issues with some missing references but this was fixed.

However, whenever I try to run the Compact command from the menu bar for my
converted application, I very quickly get an invalid operation messagebox.
Clicking Ok, simply restarts the app without compacting. So my DB is now 50
MB and climbing (typically it is around 20 MB after compacting in A2000.)

What can I do about this. I have to be able to compact the database?

Also, I still have one error in the converted database (that is not present
in the A2K format version).

It's on the following line where I'm trying to set an application icon.

dbs.Properties("AppIcon") = strFile

Is this error occuring because in Access 2000 you can't set a universal
application icon for each form, whereas I think you can in 2003. Still why
wouldn't it allow me to code the application icon.

Thanks.

Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

[Allen Browne]

You might be able to solve the problem by opening the new file and assigning
an icon file through:
Tools | StartUp | Application Icon

If no icon has been assigned, the AppIcon property may not exist, so the
attempt to assign something to the uncreated property will fail.

You are probably aware that Access 2003 can use an A2000 file format
natively, so you don't need to convert to the A2002/3 file format unless you
have a special reason (e.g. creating an MDE.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdemyan via AccessMonster.com" <u6836@uwewrote in message
news:64728be352aee@uwe...

I've converted my application from A2K format to A2003 format.

I tried to follow Allen Browne's protocol in getting my app into A2003
(although I was unable to find informtion on the conversion process).
Lots
of decompiling and lots of compacting of the original application in
A2000.
Then the app was opened in A2003 and compacted, decompiled and compacted.
Next I imported everything into a blank A2003 database. Then this db was
converted from A2000 to A2003.

I had some issues with some missing references but this was fixed.

However, whenever I try to run the Compact command from the menu bar for
my
converted application, I very quickly get an invalid operation messagebox.
Clicking Ok, simply restarts the app without compacting. So my DB is now
50
MB and climbing (typically it is around 20 MB after compacting in A2000.)

What can I do about this. I have to be able to compact the database?

Also, I still have one error in the converted database (that is not
present
in the A2K format version).

It's on the following line where I'm trying to set an application icon.

dbs.Properties("AppIcon") = strFile

Is this error occuring because in Access 2000 you can't set a universal
application icon for each form, whereas I think you can in 2003. Still
why
wouldn't it allow me to code the application icon.

[rdemyan via AccessMonster.com]

Yes, I want to create an MDE, not only to protect my code but to also provide
a stable app resistant to corruption. I'm going to be deploying to a client
who has the macro security to Medium. I purchased a certificate from Thawte
to remove those annoying messages on startup. Digital signing does not work
with A2K formats.

I guess I'm a bit concerned that you didn't provide any advice on the
compacting issue as I know that this is one of your areas of expertise. That
suggests I may be screwed.

Is there anything I can do? Have you seen this before?

Allen Browne wrote:

>You might be able to solve the problem by opening the new file and assigning
an icon file through:
Tools | StartUp | Application Icon

If no icon has been assigned, the AppIcon property may not exist, so the
attempt to assign something to the uncreated property will fail.

You are probably aware that Access 2003 can use an A2000 file format
natively, so you don't need to convert to the A2002/3 file format unless you
have a special reason (e.g. creating an MDE.)

>I've converted my application from A2K format to A2003 format.

[quoted text clipped - 30 lines]

>why
wouldn't it allow me to code the application icon.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

[rdemyan via AccessMonster.com]

I forgot to mention. I downloaded the Jet Utility and WAS able to compact my
app in the A2003 format. However, the compact command within Access is not
working; hence, I can't create a .mde.

The version of Jet that I'm using is: 4.0.8618.0

rdemyan wrote:

>Yes, I want to create an MDE, not only to protect my code but to also provide
a stable app resistant to corruption. I'm going to be deploying to a client
who has the macro security to Medium. I purchased a certificate from Thawte
to remove those annoying messages on startup. Digital signing does not work
with A2K formats.

I guess I'm a bit concerned that you didn't provide any advice on the
compacting issue as I know that this is one of your areas of expertise. That
suggests I may be screwed.

Is there anything I can do? Have you seen this before?

>>You might be able to solve the problem by opening the new file and assigning
an icon file through:

[quoted text clipped - 12 lines]

>>why
wouldn't it allow me to code the application icon.

--
Message posted via http://www.accessmonster.com

[Allen Browne]

Recreating the application as you did is a perfectly valid alternative to
compacting.

If the recreated application won't compact, but other databases do, then
presumably something is still corrupting it. I don't know what to suggest
beyond the usual prevention approaches suggested here:
http://allenbrowne.com/ser-25.html

Presumably the code in this database does compile, the references are fine,
and you have no other Wizard problems.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdemyan via AccessMonster.com" <u6836@uwewrote in message
news:64769d7466b05@uwe...

>I forgot to mention. I downloaded the Jet Utility and WAS able to compact
my
app in the A2003 format. However, the compact command within Access is
not
working; hence, I can't create a .mde.

The version of Jet that I'm using is: 4.0.8618.0

rdemyan wrote:

>>Yes, I want to create an MDE, not only to protect my code but to also
provide
a stable app resistant to corruption. I'm going to be deploying to a
client
who has the macro security to Medium. I purchased a certificate from
Thawte
to remove those annoying messages on startup. Digital signing does not
work
with A2K formats.

I guess I'm a bit concerned that you didn't provide any advice on the
compacting issue as I know that this is one of your areas of expertise.
That
suggests I may be screwed.

Is there anything I can do? Have you seen this before?

>>>You might be able to solve the problem by opening the new file and
assigning
an icon file through:

[quoted text clipped - 12 lines]

>>>why
wouldn't it allow me to code the application icon.

[rdemyan via AccessMonster.com]

Okay. I think I discovered what the problems is. I had a feeling that it
might be the table links that were causing the problem. So I deleted all
table links and the 2003 format version of my front end compacted just fine
by using the Access menu item.

I think the solution hinges on the following statement from a Microsoft
article:

"In Access 2002, the MSysAccessStorage table replaces the MSysAccessObjects
table when you convert to the Access 2002 file format."

I noticed in my dbWindow that I had a LINKED table called MSysAccessObjects.
My backends are still in A2000 format.

With all tables linked and this MSysAccessObjects table also linked, I cannot
compact. If I delete the MSysAccessObjects link, then compacting proceeds
just fine (although after deleting that link Access did crash a few times).

So my linking routine must be allowing system tables in the BACK-ENDS to be
linked.

I tried to stop this by rewriting the actual linking part of the code as
follows:

If (tdf.Attributes And dbSystemObject) = 0 Then

'Set the properties of the new link
'and append to the tabledefs collection
tdf.SourceTableName = TableToLink
tdf.Connect = ";DATABASE=" & BEFile
db.TableDefs.Append tdf

End If

So what was added was the tdf.Attributes and dbSystemObject.

I then deleted all table links and relinked using the code. The linked
MSysAccessObjects table continues to link. I then stepped through the code
and watched as each table was linked. All of the MSys tables were able to
pass into the If statement and linking was attempted.

It's probably important that I mention that I have revoked all permissions on
backend tables and use RWOP queries. Therefore, according to MS Security FAQ,
I have to use On Error Resume Next when I attempt to link to the backend
tables. So I may be getting errors that are actually stopping backend system
tables from being linked if they are identical in name to the front end
system table. I don't know. But, if this is true, it explains why I'm only
seeing the MSysAccessObjects table (and also MSysAccessXML) as being linked
in my front end. These tables are not local system tables within the front
end. Please verify that my understanding of this is correct. As I said, I
can't test it because MS forces me to use On Error Resume Next.

Either way, it looks like I have made major progress to resolving this issue.
Now I just need to figure out how to stop system tables that are located in
the back-end files from being linked to the front end.

Thanks.


Allen Browne wrote:

>Recreating the application as you did is a perfectly valid alternative to
compacting.

If the recreated application won't compact, but other databases do, then
presumably something is still corrupting it. I don't know what to suggest
beyond the usual prevention approaches suggested here:
http://allenbrowne.com/ser-25.html

Presumably the code in this database does compile, the references are fine,
and you have no other Wizard problems.

>>I forgot to mention. I downloaded the Jet Utility and WAS able to compact
my

[quoted text clipped - 27 lines]

>>>>why
wouldn't it allow me to code the application icon.

--
Message posted via http://www.accessmonster.com

[Lyle Fairfield]

"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:64779cbe337d0@uwe:

>
Either way, it looks like I have made major progress to resolving this
issue. Now I just need to figure out how to stop system tables that
are located in the back-end files from being linked to the front end.

You didn't take anyone's advice about this.
Your code is just a giant bucket of shit.
So, yes, you do.

(I was gentle the first time!)

--
Lyle Fairfield

[David W. Fenton]

"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:64779cbe337d0@uwe:

If (tdf.Attributes And dbSystemObject) = 0 Then

'Set the properties of the new link
'and append to the tabledefs collection
tdf.SourceTableName = TableToLink
tdf.Connect = ";DATABASE=" & BEFile
db.TableDefs.Append tdf

End If

So what was added was the tdf.Attributes and dbSystemObject.

I then deleted all table links and relinked using the code. The
linked MSysAccessObjects table continues to link. I then stepped
through the code and watched as each table was linked. All of
the MSys tables were able to pass into the If statement and
linking was attempted.

You're testing the attributes of the front-end link, not the back
end table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[rdemyan via AccessMonster.com]

Thanks, David.

That did the trick. System tables from the backend are no longer being
linked.

David W. Fenton wrote:

>If (tdf.Attributes And dbSystemObject) = 0 Then

[quoted text clipped - 13 lines]

>the MSys tables were able to pass into the If statement and
linking was attempted.

You're testing the attributes of the front-end link, not the back
end table.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1