trouble converting '97 format into 2000

[Nico]

Hello,
I have a ’97 Access DB (we can call it “to_be_convertedâ€) which I should
convert into 2000 format.
The DB can’t be linked or re-designed: I have to convert the existing file.
I have tried using the built-in conversion features… Not working…
The problem is The problem is having filled each field with a huge
description while designing tables in the ’97 database. This seems makes the
conversion features crashing.

I don’t’ have Access ’97 on my pc so I have tried building a 2000 format DB
(we can call it “converterâ€) with a command button on a form.

Now... i would like to know:
a) since “to_be_converted†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 connect to " to_be_converted "
from "converter". Anything went fine: I have tested the connection with this
routine:
Private sub test_click()
Dim pisa As Database
Dim i As Integer
Set pisa = OpenDatabase("C:\Documents and Settings\rocco n
forgione\Desktop\grant.mdb", , , "uid=gestore;pwd=paolarucci")
for i=0 to .tabledefs.count-1
MsgBox .tabledef(i).name
Next
End sub
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

What I have in mind is building a routine that launched against
"tobeconverter" will clean descriptions fro each field in each table. This
will solve the problem. Does anybody have any suggestion? Does anybody know
if all those problems arise because I don’t have ’97 on my pc?
Thanks,
Nico

 

[Douglas J. Steele]

What happens if you open an empty database in Access 2000, then choose File
| Get External Data and import the tables?

 

[Nico]

The procedures stops few times giving a msg: "Invalid Argument" by clicking
OK it continues running until the end.
For what I have seen all tables were transferred into the 2000 db.
But I still would like to connect to the '97 DB in order to catch as much
info as I can in order to double check if conversion went right.
I would need to know:
how many fields were on each table... the kind of datatype for each field...
the validation rule.. etc..
Then I would retrieve the same information from the converted DB and compare
one to the other.
can you tell me if the procedure i showed on my previous msg was right? Why
do I receive "0" (zero) as .fields.count?
Thank you!!!!!!!!!

 

[Douglas J. Steele]

To be honest, I'm surprised your code gives you anything (unless you didn't
cut and paste your actual code into the post).

Private sub test_click()
Dim pisa As Database
Dim i As Integer
Set pisa = OpenDatabase("C:\Documents and Settings\rocco n
forgione\Desktop\grant.mdb", , , "uid=gestore;pwd=paolarucci")
for i=0 to .tabledefs.count-1
MsgBox .tabledef(i).name
Next
End sub

You should be getting an error at the .tabledefs.count-1, because you
haven't said what object the TableDefs collection belongs to!

But even before that, if you've applied Access security to your application
(as supplying a uid and pwd implies), your OpenDatabase statement should be
failing, since Access expects values for the 2nd and 3rd parameter in the
OpenDatabase statement before it'll use the 4th parameter.

I just tested the following code in Access 2003, going against a database in
Access 97 format, and it works fine:

Sub CountFields()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = OpenDatabase("<path to database>")
For Each tdfCurr In dbCurr.TableDefs
Debug.Print tdfCurr.Name & " has " & tdfCurr.Fields.Count & " fields
in it."
Next tdfCurr

dbCurr.Close
Set dbCurr = Nothing

End Sub

 

[Nico]

Oh! well... my code retrieve each table name properly and open the connection
to the '97 database (i think...).
But i'm goign to use your, which certanly will work better than mine!!!
i have tried using the "help" file, which doesn't help too much. it seems MS
thinks anyone borns knowing how to connect to a MS database...
help on Opendatabase function is very short... and not too much explaining.
Thanks,
Nico

 

[Nico]

Hi Doug,
I have tried your code sample. Not working here…
This is what I wrote:
Dim pisa As DAO.Database
Dim tabella As DAO.TableDef
Set tabella = New DAO.TableDef
Set pisa = OpenDatabase("C:\Documents and Settings\rocco n
forgione\Desktop\grant.mdb", , , "uid=gestore;pwd=paolarucci")
With pisa
For Each tabella In .TableDefs
If tabella.Attributes = 0 Then
MsgBox tabella.Name & "--has--" & tabella.Fields.Count
End If
Next tabella
End With
pisa.Close
Set pisa = Nothing
End Sub

This at least gives me the names of the tables…but the field.count comes out
“0†for every tables.

For the opendatabase method I have tried also OpenDatabase("C:\Documents and
Settings\rocco n forgione\Desktop\grant.mdb", true,false ,
"uid=gestore;pwd=paolarucci") and it comes out an error msg saying “Run Time
error `3151` ODBC-connection to ‘C:\Documents and Settings\rocco n
forgione\Desktop\grant.mdb’ failed
Can you show me how I have to write the opendatabase string?
I have the .mdw which manages the security issue on the ’97 DB.
I have joined the .mdw file. So I’m logging through this .mdw also in 2000.

What I can't understand is: if I'm able to read the name of the tables, why
can't i go further counting fields on each tables and more? I Shouldn't read
even the names if my connection wasn't right... boh.
…thanks…

 

[Douglas J. Steele]

I'm really not sure what's happening. See whether specifying the mdw and the
uid & password to the DBEngine object solves your problem:

DBEngine.SystemDB = "c:\myapp\system.mdw"
DBEngine.DefaultUser = "LogonUserName"
DBEngine.DefaultPassword = "LogonPassword"

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("DatabaseName.mdb")