Converting user-secure 97 to 2000

[Llelan D.]

I'm trying to convert an old user-secured Access 97 database application to
Access 2000. It has a set of MDB files, an MDE file, and an MDW file. It runs
just fine, asking for a user and password, using an Access 97 runtime with the
following shortcut:
"C:\test\Office\msaccess.exe" /runtime /wrkgrp "C:\test\system.mdw"
"C:\test\test.mde"

I now have Access 2000, and no longer have Access 97. I would like to convert
this database application to Access 2000 so that I can upgrade it. I follow
the instructions in the Access 2000 help files.
Start Access 2000 using the c:\test\system.mdw workgroup file.
Use the Convert Database menu item to choose an MDB file to convert.
Give the user and password that has the administration privileges.
Use the Compact and Repair Database menu item to compact the new MDB file.
Open the new MDB file.
Unhide the database.

The database window appears for the new MDB file, but it has no objects in any
of the categories. No tables, queries, forms, reports, and so on. The
converted file size is of a similar magnitude to the original 97 file, and the
original has data that the database application definitely uses.

This same thing happens when I try to convert every MDB file. Access refuses
to even try to convert the MDE file. When I try to import the 97 MDB file into
a new 2000 MDB file, the import dialog does not see any tables or other objects.

I no longer have Access 97, so I cannot alter the original files directly, nor
can I eliminate the user-security. I need to convert them over to Access 2000.

Can anyone tell me how this can be done for all the MDB files, and the MDE file?

Any assistance you could provide would be greatly appreciated.

--
--------------------------------------------------------
The Snake Pit - Development www.TheSnakePitDev.com
Curtis Clauson (e-mail address removed)
Proprietor

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.

 

[Mingqing Cheng [MSFT]]

Hi Llelan D,

From your descriptions, I understood that you could not upgrade your Access
97 files to Access 2000. Have I understood you? If there is anything I
misunderstood, please feel free to let me know

Based on my scope, you could add your mdw file to your Access workgroup and
then
try to import it into Access 2000. You could follow the steps below

1. Find WRKGADM.EXE, it could be under C:\Program Files\Microsoft Office
2000\Office\1033 (Change the path to suit your own settings)
2. Click Join, select your mdw files for that mde file
3. Create a new mdb file by means of Access 2000
4. Files -> Get External Data -> Import...
5. Select the file you want and then select the objects
6. See whether it works fine now

If you meet any error message in the whole process, would you please show
me what it is? These are important for me to fingure out what it happenes.

Hope it helps and if you have any questions or concerns, please feel free
to let me know

Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Llelan D.]

From your descriptions, I understood that you could not upgrade your Access
97 files to Access 2000. Have I understood you? If there is anything I
misunderstood, please feel free to let me know

Not quite correct. The conversion process executed with no errors, but none of
the data tables or other objects appeared when the converted database files
were opened in Access 2000, despite the fact that the converted files had
sizes of the same order of magnitude as the original Access 97 files. So I
could successfully perform the upgrade process, but the data objects do not
appear when I open the converted database files in Access 2000. Also, I need a
way to deal with the MDE file.

Based on my scope, you could add your mdw file to your Access workgroup and
then try to import it into Access 2000. You could follow the steps below

1. Find WRKGADM.EXE, it could be under C:\Program Files\Microsoft Office
2000\Office\1033 (Change the path to suit your own settings)
2. Click Join, select your mdw files for that mde file
3. Create a new mdb file by means of Access 2000
4. Files -> Get External Data -> Import...
5. Select the file you want and then select the objects
6. See whether it works fine now

You describe two processes that my original post quite clearly states have
already been performed.
Starting Access 2000 with the MDW file can only be done in two ways: with the
/wrkgrp command line option, or with the Workgroup Administrator (BTW: There
is a shortcut to it in the Office folder). Since the Convert Database command
results in a user/password dialog that accepts the correct user/password in
the MDW file, the MDW file is being loaded correctly.
This is the import process that you described above.

No errors were quoted in the original post because no errors were encountered
in either the conversion or import processes.

Can anyone shed some light on this? I'm at a total loss as to why the
converted MDB files with comparable relative sizes to the originals do not
show any data objects when opened in Access 2000 using the same MDW file. Nor
can I find how to deal with the MDE file.

Does anyone know of a source for the MDB/MDE file formats for 97 and 2000? I'd
be able to diagnose the problem and write a converter if I knew these.

--
--------------------------------------------------------
The Snake Pit - Development www.TheSnakePitDev.com
Curtis Clauson (e-mail address removed)
Proprietor

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.

 

[Chris Mills]

I'm not sure I followed your entire post, but I have some observations about
converting "user-secured" databases.

I think, if you have not already, you should get hold of the Security FAQ. You
can find it in the Security newsgroup (I don't immediately have the URL to
hand).

A major problem when converting between Access versions, is converting the
system.mdw file. Even though Access provides a facility to convert mdw files,
it does not work properly and the SecFaq says so.

In particular, if your A97 system.mdw was originally converted from an A2.0
system.mda, then it cannot be converted to later Access even though the
conversion may appear initially successfull (you would get a specific error
message which you have not said is the problem). If the mdw was "conceived" in
A97 then it should convert OK (by experience), however the SecFaq specifically
recommends against any conversion of an mdw.

Of course, this is a nuisance. Who can remember all the security settings or
want to set it up again? Nevertheless, this is what the SecFaq, also promoted
by Microsoft, recommends. I can only confirm that, if any of it was ORIGINALLY
written in Access2.0, and even though converted before your time, the warnings
in the SecFaq regarding versions will come to pass.

If you didn't write the original security, or otherwise aren't sure about it,
the advice is to de-secure the db before converting. That is covered in the
SecFaq.

but it has no objects in any
of the categories

This does not sound like my above dire warning, which only kicks in when you
try to modify the converted database. It sounds like a "corruption".
Access2000 is notorious for difficulties in conversions. You are lucky if it
didn't just "hang-up". The best advice I have (also from reading these
newsgroups), is to do a complete decompile/compile/compact both BEFORE and
AFTER conversion. If you don't have A97 to do the before part, then you may be
out of luck (Access 97 was notorious for creating but tolerating subtle
corruptions, which may be unnoticed until A2000 gets it sticky hands on it)

An mde file can NEVER be converted, by design, by anyone. It sounds to me like
you need direct assistance of an IT or Access professional at your site. I can
only say, that if I came across your description and did not have access to
Access97, I'd go sailing or backpacking or something.

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.

Aww, I think that must be a takeoff of Arthur C Clarke, who said "Any
sufficiently advanced technology is indistinguishable from magic". Arthur C
was first?

Chris

 

[Chris Mills]

I took the trouble to look up your URL, Llelan D.

You are SO right, emphasizing the stone-age and a snake pit.

The trouble is, when you mentioned difficulty with mde, I should have realised
that not all snakes are venemous. Or potent.

Do you actually get any sales from that site? Do you know what you're about?

Chris

 

[Chris Mills]

I took the trouble to look up your URL, Llelan D.

You are SO right, emphasizing the stone-age and a snake pit.

The trouble is, when you mentioned difficulty with mde, I should have realised
that not all snakes are venemous. Or potent.

Do you actually get any sales from that site? Do you know what you're about?

Chris

 

[Chris Mills]

Llelan D.,

I took the trouble to look at your web site.

As it suggests, you appear to be in the stone-age. How you purport to offer
"engineering support resource", inasmuch as Microsoft Access is concerned,
beats me.

I loved your site! Much of what it says is true. It just doesn't seem the best
way to attract customers, some outright bullshit is necessary for that!

Regards
Chris

 

[Llelan D.]

I think, if you have not already, you should get hold of the Security FAQ. You
can find it in the Security newsgroup (I don't immediately have the URL to
hand).

I'd never heard of it, but I found it. Thanks.

KB Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;165009

FAQ Online
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

Download
http://download.microsoft.com/download/access97/faq1/1/WIN98/EN-US/Secfaq.exe

A major problem when converting between Access versions, is converting the
system.mdw file. ...

Fortunately, I only need to deal with the original user-security MDW for as
long as it takes to convert the MDB/MDE files to A2000. After that I intend to
completely recreate the security setup. I have all of the user/id/password
entries for the original MDW.

So far, editing the MDW through A2000 has not worked, but I have a lot of
permutations still to try. Unfortunately, I don't have a copy of Access 97 any
more to use to remove the security.

This does not sound like my above dire warning, which only kicks in when you
try to modify the converted database. ...

It appears when I simply try to open the converted databases. There are no
objects to either browse, modify, or edit the security attributes.

An mde file can NEVER be converted, by design, by anyone.

The MDE simply has the editable sources removed and only uses compiled code so
that others may not examine and modify the VB code. This is quite easily
convertible as it is just code and data. However, it appears that MS has never
done this. If I could find a file format specification for the Access 97/2000
MDB/MDE/MDW files, I could write a converter.

Aww, I think that must be a takeoff of Arthur C Clarke, who said "Any
sufficiently advanced technology is indistinguishable from magic". Arthur C
was first?

Actually, Mr Clarke paraphrased it from other historical sources. I hit him
with my obverse version at an author party I was hosting. Laughter, drinks,
and some twisted plot debates soon followed. As one of the rare few who
actually got the reference, you may take two Karma-Kookies from petty cache
<chuckle>.

--
--------------------------------------------------------
The Snake Pit - Development www.TheSnakePitDev.com
Curtis Clauson (e-mail address removed)
Proprietor

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.

 

[Llelan D.]

I took the trouble to look up your URL, Llelan D.

You are SO right, emphasizing the stone-age and a snake pit.

The trouble is, when you mentioned difficulty with mde, I should have realised
that not all snakes are venemous. Or potent.

Do you actually get any sales from that site? Do you know what you're about?

<chuckle> That site is undergoing reconstruction. My business stems from
well-established development communities. Those who need my services
invariably work with those who have already benefited from them. The site was
not needed for this. I've recently decided to redesign it to cater to more
public business interests, and as a personal site for my open-source
activities. It'll be updated as soon as I finish playing with the local version.

Those who truly live for the art of development know what the benefits of the
local "Snake Pit" are, and are always welcome. The rest just see snakes, which
keeps them nicely out of our hair.

Do you know what you're about?

<feral grin> Enough to know what not to ask.

Thanks for the security faq info. It gave me loads of alternative to try.

--
--------------------------------------------------------
The Snake Pit - Development www.TheSnakePitDev.com
Curtis Clauson (e-mail address removed)
Proprietor

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.

 

[Chris Mills]

An mde file can NEVER be converted, by design, by anyone.

The MDE simply has the editable sources removed and only uses compiled code so
that others may not examine and modify the VB code. This is quite easily
convertible as it is just code and data. However, it appears that MS has never
done this. If I could find a file format specification for the Access 97/2000
MDB/MDE/MDW files, I could write a converter.

Since you know more than anyone about converting mde files, which no-one has
done to my knowledge, why are you asking us dude?

A principle of "knowing", is that you need to have done it at least once. It's
also OK to just "spiel off", even I have untested ideas. Like, Why would you
want to convert an mde when as developer you would have the mdb it was sourced
from?

Chris

 

[Chris Mills]

For some reason, not associated with bad language as you can attest, my post
in this sub-thread was TWICE auto-deleted from the newsgroup. I'm surprised
that you got it. Must be magic.

 

[Tony Toews]

However, it appears that MS has never
done this. If I could find a file format specification for the Access 97/2000
MDB/MDE/MDW files, I could write a converter.

1) MDB Tools is a set of libraries and utilities to facilitate
exporting data from MS Access databases (mdb files) without using the
Microsoft DLLs. In other words they are reverse engineering the layout
of the MDB file. mdbtools.sourceforge.net/

2) You can import all the forms and reports at one time using Serge
Gavrilov's free tool "MDE forms/reports extractor v1.1". It's
available here: http://accesstools.narod.ru/.

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

 

[Llelan D.]

1) MDB Tools is a set of libraries and utilities to facilitate
exporting data from MS Access databases (mdb files) without using the
Microsoft DLLs. In other words they are reverse engineering the layout
of the MDB file. mdbtools.sourceforge.net/

An excellent source of information about the MDB file format. Unfortunately,
the package is so UNIX-centeric that it appears un-compilable on Windows since
it requires a large number of Gnome libraries. I'm still working on it though,

2) You can import all the forms and reports at one time using Serge
Gavrilov's free tool "MDE forms/reports extractor v1.1". It's
available here: http://accesstools.narod.ru/.

I learned a lot about interacting with MDE files from these utilities.
Unfortunately the MDE extractor throws errors when trying to extract anything
from the MDE file.

Thank you VERY much for these links. They provided the information to point me
to a solution that actually works for my specific situation. By punting Access
entirely, and using ADO to directly interact with the original Access97
database, and open-source packages like OpenOffice for Forms/Reports
components and Java for code and GUI, I'm able to extract the Schema and data
of the original database and reconstruct it in the much more useful and
dependable database environment of the open-source MySQL. The VB code in the
MDE doesn't matter since it was going to be rewritten anyway.

Why Access2000 cannot do this simple thing is beyond me, but depressingly
consistent. Thanks again for all your help.

--
--------------------------------------------------------
The Snake Pit - Development www.TheSnakePitDev.com
Curtis Clauson (e-mail address removed)
Proprietor

"Any sufficiently over-complicated magic is indistinguishable from
technology." -- Llelan D.