Migration from Miscrosoft access 97 to MS Access 2000

[sowree]

Hello All,
I have worked on SQL databases till now on 2000/2005.
I have never used or worked on MS access.
There is a user request assigned to me to migrate from MS access 97 to MS access 2000, can som3body please give any articles or information of how I go about doing the migration or steps I need to follow?
Also is there something with VB8 that does it?
I have no information on how to do this.Please help!!


Thanks
sowree

[Ron Weller]

Try This Code. Also notice the Reference requirement.
Public Sub dbConvert()
'Required Reference: Microsoft Jet and Replication Objects 2.6 Library
' NOTE: - 2.6 verison number may be different on your machine
Dim JE As JRO.JetEngine
Dim cn1 As String
Dim cn2 As String

' Create JetEngine object
Set JE = New JRO.JetEngine

' Create Connection string for Access97.mdb
cn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\My Documents\db97.mdb;" & _
"Jet OLEDB:Engine Type = 4;"
'cn1 = cn1 & "Jet OLEDB:Database Password = MyPassword" 'if old db has a password

' Create Connection string for Access2000.mdb
cn2 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\My Documents\db2000.mdb;" & _
"Jet OLEDB:Engine Type = 5;"
'cn2 = cn2 & "Jet OLEDB:Database Password = MyPassword" 'if you want new db to have a password

' Compact the database to perform conversion
JE.CompactDatabase cn1, cn2

End Sub

[Hack]

Won't simply opening the '97 database using Access 2000 do the conversion?

[Slope]

Won't simply opening the '97 database using Access 2000 do the conversion?

Yes.

[sowree]

If I open the 97 access mdb in access2000 and it makes the conversion, will there be still a copy of the original 97 mdb?

If I want to keep a copy of the original 97 access mdb, can I just copy the .mbd file some where and when I open that in 97 will it stiill work?

I am sorry I still need to learn the concept of MS access. Am I right when I say "Save .mdb file" and "Reopen the file in 97 access"

Please help

[Slope]

Yes, you can just make a copy of it. However, I believe the wizard gives you the option to save the original database or it may be just the option to open the database in 2000 without converting but then you can't make any changes. I don't remember exactly, as I haven't done it in a while.

[Hack]

Once you have done the conversion and verified the results, why would you need to keep a copy of the '97 database?

You can always simply delete it, or back it up to CD and archive it (then delete it from the machine.)

[sowree]

Hello,
The reason I am little worried and want to keep the orginal mdb in 97 is, this is a crucial application running every day, this is new to me and the previous ex-employee who handled this project had problems (which i am not aware as what problems) and had to revert back to 97 while trying to migrate it to 2000.
So I need to copy it (have a copy before I migrate it)

[Slope]

If it is a crucial app, it is probably worth migrating to SQL Server in the long run.

[Ron Weller]

When you do the convert from within Access it prompts for the file and defaults to the same file name; but you can use a different file name and the converted database will use that name leaving the original untouched.
One thing to be aware of is that the conversion does not convert your references. So first open any piece of code from Access 97 and then open the references and write down the list.
After you convert, open the new version in Access 2000 but hold the shift key during the entire open process. This will stop any auto starting code from trying to run.
Next open the Code editing window, Alt-F11. From there open up the references menu item and add any missing references from your list.
Now you should be able to compile without any reference errors, although there are other programming issues that may trigger a compile error. You will have to fix those by hand as they come up.
Turn off Auto Name Change Feature. Go back to the database window and from the options menu item uncheck Auto Name Change. If this feature is on and you do a compact and repaire of your database it will do many nasty things to your database. It's not perfect anyway, but you can still use it, just turn it on, make your changes, then turn it back off right away.
Good Luck:)

[Slope]

One other thing - I believe it was in 2000 that Access switched Unicode Text. When you convert, all Text columns will have a new property value called “Unicode Compression” which will be defaulted to “No”. This will cause your database to be twice as large if you have lots of Text columns. You will need to go in and manually set them to “Yes”, then run a Compact and Repair to shrink the database back down.

[Ron Weller]

Another little known property on all of your new 2000 tables is called Subdatasheet Name which defaults to [Auto]. Change this to [None] and you will gain increased performance when accessing these tables.

[Hack]

So I need to copy it (have a copy before I migrate it)Yes, that is what I would suggest.

Keep a copy of the original in a backup folder.

Do the migration on another copy, and rollout the new results. If you need to do a rollback, the original will still be in your backup folder.

[pclement]

Something to keep in mind, if you use CompactDatabase it will not perform a true conversion. Only Microsoft Access will convert certain database objects to the newer version.

[iam_navaneeth]

I understand