converting form '97 to 2000: getting error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nico - 27 Aug 2004 18:29 GMT

Hello,
I have a database in '97 format.
I want to upgrade it safely into 2000 format.

I used the command:
tools>database utilities>convert database
... but something went wrong.

For I have understood, the problem is that tables have many fields (let say
each table has an average of 150/180 fields)
and for each field there is a quite long description to give advice to users.

Tables with few fields don't cause any problem. The ones with more than
100/120 fields make the process crashing.

Now... I Know ADO i'm unconfortable with DAO.
There is anyway to delete fields' description from all table using code?
What I have in mind is creting a blank database in 2000 which has a command
button that will launch a procedures against
my '97 database.

Before wasting time... there's a better solution?

Thanks,
Nico

Reply to this Message

Jeff Boyce - 28 Aug 2004 12:46 GMT

Nico

A scan of this newsgroup (tablesdbdesign) and a visit to the mvps.org/access
website will reveal a strong consensus against tables with that many fields.
Depending on the situation, a well-normalized table & database may never
need more than 30 (or so) fields.

If your conversion is failing due to the number of fields in your tables,
perhaps you need to re-evaluate your table design?

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Nico - 28 Aug 2004 23:23 GMT

Hi Jeff,
thanks for the advice.
I totally agree with you but the database wasn't build by me and cannot be
changed... since who did it doesn't want to change it.
Actually, i have tried the built-in conversion features and it works pretty
well also when tables have up to 180 fields.
The main problem is having used the description building tables in design
view. This seems to cause the crashing. So i'm trying to work around that
building a second Db which open a connection to the first one (the one that
need to be converted) and deleting the description on each field of each
table trough code.
My trouble is that I have never used DAO (which i should use -I think-
'cause the Db to be converted is a '97). My question is:
there is any other way?
I' m trying to access fields using a routine like:
Dim tobeconvertedDB As Database
Dim nomino As String
Dim i As Integer
Dim e As Integer

Set tobeconvertedDB = OpenDatabase("C:\Documents and Settings\rocco n
forgione\Desktop\pina1.mdb", , , "uid=gestore;pwd=paolarucci")

With tobeconvertedDB
For i = 0 To .TableDefs.Count - 1
If .TableDefs(i).Attributes = 0 Then ‘to avid working on
hidden tables
For e=0 to .TableDefs(i).Fields.Count-1
.TableDefs(i).fields(e).properties(“description”)=””
Next
End if
Next
End With
End Sub

This seems not working properly. I have tested it using some msgboxes and it
always come out that .fields.Count is =0 (obviously tables HAVE fields).
Last: the F1 help doesn't works. It says "help not available"... and i dont'
know why...
Thanks!
Nico

> Nico
>
[quoted text clipped - 5 lines]
> If your conversion is failing due to the number of fields in your tables,
> perhaps you need to re-evaluate your table design?

Reply to this Message

Jeff Boyce - 29 Aug 2004 14:52 GMT

Nico

Not sure I can help with that code, but you raise an interesting point. If
the data already exists in A'97 format, why do you need to change it? As
you've already determined, you can link to it from A2K.

Just a thought...

Jeff Boyce
<Access MVP>

(if I had a "customer" such as yours, I might consider freelancing and
building an example of a better designed db, then demonstrating the
difference between the failing conversion of his/her version, and the simple
conversion of my sample.)

Reply to this Message

Nico - 29 Aug 2004 16:07 GMT

Hi Jeff,
Thanks for spending time to reply my question.
To make things more clear:
database can't be linked. Don't waste time asking why: It can't be linked.
They HAVE TO convert it into 2000: that's the task. No re-desing,
no-freelancing "thing". They need a totally new database in 2000 format.
You can build it from scratch... but note that there are almost 120 tables
and so many queries you can barely immagine.
the short way to go is: converting the existing DB inti 200 format.
My doubt is just a bit more general. I have a '97 database I can call it
"tobeconverted", I have the .mdw file used by this database. I have created a
2000 database (we can call it "converter") with just one form and two
buttons. The first button have to delete all data in tobeconverter's tables.
Now... i would like to know:
a) since tobeconverted is a '97 DB, should I use DAO in converter to connect
and make changes? (i think "yes")
b) i have used the opendatabase method to conncet to "tobeconverted" from
"converter". Anything went fine: I have tested the connection with this
statement
for i=0 to .tabledefs.count-1
MsgBox .tabledef(i).name
next
and i could read tables' name.
WHY the same kinf of stuff doesn't allow me to ready how many fields each
table has?
I Mean, the following statements gives me always "0" (zero)
for i=0 to .tabledefs.count-1
MsgBox .tabledef(i).fields.count
next

It seems I can't go further than tables' name.

Again: the problem with converting procedures is not the "tobeconverter"
desing (it could be better, but... IT CAN'T BE CHANGED). The problem is
having filled each field with a huge description while designing tables. So:
a routine launched against "tobeconverter" which clean descriptions will
solve the problem. That's is what I'm asking for.
Thanks,
Nico

> Nico
>
[quoted text clipped - 11 lines]
> difference between the failing conversion of his/her version, and the simple
> conversion of my sample.)

Reply to this Message

Jeff Boyce - 29 Aug 2004 21:56 GMT

Nico

Sorry, don't think I can help with that. I'd suggest re-posting.

Regards

Jeff Boyce
<Access MVP

Reply to this Message

John Vinson - 30 Aug 2004 02:45 GMT

>the short way to go is: converting the existing DB inti 200 format.
>My doubt is just a bit more general. I have a '97 database I can call it
>"tobeconverted", I have the .mdw file used by this database.

One suggestion: try creating a new A2000 database. Open VBA and use
Tools... References to unselect ActiveX Database Objects and to select
Microsoft DAO x.xx Object Library; then close VBA and use File... Get
External Data... Import to import everything from the A97 database.

Open VBA again and use Debug... Compile, and compact the database.

Does this get around the conversion wizard limitations?

John W. Vinson[MVP]
(no longer chatting for now)

Reply to this Message