visual dbase 7 to access 2000

[clipperfan]

I've had to construct some visual dbase 7 tables with long table names and long field names to match a structure in Oracle 9i with an eye towards migrating the dbase to Oracle. The Oracle folks want to suck the dbase data into Access tables and then into Oracle. I upgraded from Access 97 to Access 2000 since Access 2000 talks about being able to import vdb 7 files. However vdb7 files do not show up as a file format choice in the import wizard, only db5, and when I try to do an import, access balks at duplicate field names (since the first 10 characters are no longer unique). Anybody know how to get around the Access 2000 migration problem. (Buying Access 2002 may be problematic.) Alternately, anybody have experience with OraclesSQL loader and vdb 7. This area is very new to me, having been a desktop database person.

 

[akom]

If you find an answer please tell me I have the same problem.

 

[clipperfan]

What I ended up doing was create an Access 97 .mdb that had links to the original dbase tables with their old style (db 4, Clipper compatible) file names. Then I created another Access 97 .mdb with the table and file names expected by Oracle. Then in a 3rd Access 97.mdb, I wrote modules to migrate the information from one table to another.

I haven't tried this Access 2000 or later so can't vouch for upward compatbility.


'*************
Sub EqSpecnoPop()
Dim dbs As Database, fed As Database
Dim rEQSPEC1 As Recordset, rSPCFCT_NUM_T As Recordset
...
Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\NEWWORLD\FEP2.mdb&quot
Set fed = DBEngine.Workspaces(0).OpenDatabase("C:\NEWWORLD\FED.mdb&quot

Set rEQSPEC1 = fed.OpenRecordset("EQSPEC1&quot
Set rSPCFCT_NUM_T = dbs.OpenRecordset("RRFEP2_F2_RR_SPCFCT_NUM_T", dbOpenDynaset)

....

rEQSPEC1.MoveFirst
Do Until rEQSPEC1.EOF

With rSPCFCT_NUM_T
.AddNew
!SPCNUM_SPECNO_DISPLAY_CD = rEQSPEC1!SPECNO
...
.Update
End With

rEQSPEC1.MoveNext
Loop
End Sub
'************************
In later modules, I tried queries

Dim t2kspecs As Recordset, t2ksql As String

t2ksql = "SELECT SPECNO, BASICOPTS, COMPSTRING FROM
T2KSPECBASE WHERE " _
& "Not(IsNull([BASICOPTS])) ORDER BY COMPSTRING;"

Set t2kspecs = fed.OpenRecordset(t2ksql)

or

Set rst = dbs.OpenRecordset(strSpecCriteria)

This allowed me to use ordered record sets. But when processing loops via something like
rst.MoveFirst
I found I had to take into account Access 97's processing non-blanks before blanks.

Hope this helps you akom.