How to programatically convert access 2.0 database to access 2000/2003

[amudhan]

I have a Access 2.0 database with queries, forms, reports, macros, modules and of course tables with relationships & indexes. I want this database to convert programmatically to a higher access version(access 2000 or 2003) format. I tried compact database and it converts without errors, but when I try to open in the newer version of access it gives errors.MS by themselves in one of their KB article give that Compact database may not convert forms, reports etc., but only data.
My questions are
1) is there anyway to programmatically convert access 2.0 db to access 2000/access 2003? Any clues will be of great help.
2) Will there be any issues if the access 2.0 db is secured with user level permissions?

 

[KenHigg]

My recommendation is to make a copy of the db and .mdw and work your way through the conversion from within Access '03. Then make a log of each issue you have to resolve all the way through the point where you have it all working correctly.

Then review the log and see if any of the issues can programmatically addressed. I'm guessing the hassle of figuring out a 'programmatic conversion' routine will not be worth the effort...

My experience has been that the best method would be to simply work through the list of conversion issues, figure out the solutions, then schedule a time to shut the production db down to do the real conversion. By doing a test conversion you shouldn't be caught by any surprise issues you'll have to scramble around trying figure out a solution to while everyone is waiting for the db to come back up...

Just my opinion...

 

[amudhan]

Just had gone and done the same way. I was able to convert the tables, index, queries & relation. but I am unable to convert the forms, reports, macro and module. Is there a way to do it?
Actually my access 2.0 database is out of the production and is in the field and my applications are using it. I am trying to upgrade the dB in the field programatically.

-Amudhan

 

[KenHigg]

My experience has been that until you are able to convert it step by step, you never be able to convert it programmatically...

Try converting the form stuff and when each error occurs that you don't know a fix for, post it here and see if someone can help you. Then document each fix and you'll at least have a starting point...

 

[amudhan]

Sorry to be like amateur. I don't know how to move the forms or reports or modules from one dB to another. Can I do this with DAO? or is it possible using ADO? can you give me some clues.
thanks
-Amudhan

 

[KenHigg]

When you try to open your old db in the newer version of Access, you should get a message asking if you want to convert it, and you should respond with yes. Then, it sounds like you'll get some errors. Work through each of these errors and document what you do to work through them. Do all this on copies of the original, of course...

 

[amudhan]

hi,
I think I am not clear in posting my orginal problem. I am able to convert my database by opening the access 2.0 version of my database in access 2000 and asking it to convert to access 2000. there is no problem in doing this step and hence I assume that I can convert my database.
My problem is that my access 2.0 version is being used by my VB application and this is currently used by 1000's of customers. to convert these databases I need to write a utility which on click of one button has to convert the access 2.0 database to access 2000 database. This has to happen in the customer machines whether he has access 2000 installed or not.

 

[KenHigg]

So you're using Access as a vb back end?

 

[amudhan]

yes. I am doing that. Also write now I am able to write a utility which uses DAO as the data access method and create a new database in 2000 format and copy all the tables with it's fields, relationships and data.
Also I am able to create the indexes and also the queries the user has stored in the database.
My question is that how can I copy the modules, reports and forms?

 

[KenHigg]

Sorry, you've lost me again. If you have a vb front end, why do you have have Access forms, etc to worry about?....

 

[amudhan]

To 80% of the customers it will be VB front-end and the remaining customers they use these forms and reports and the worst part is that my application doesn't know whether the customer falls under 80% or under 20%.

 

[KenHigg]

Hum...

If you have a front-end/back-end, why don't you simply distribute an updated copy of the front-end?

 

[amudhan]

The front end is the new updated copy. but the backend access database data and forms are to be moved to the newer version becaues of maintenance issues. so when my new front end goes there will be a menu which tells the VB application to convert the database from access 2.0 and access 2000 and utility will run and convert the database.

 

[KenHigg]

Hum...

If would indulge me a little further...

So:

1. You have developed this database.
2. There are two versions, both with an Access 2.0 back-end.
3. One version has a VB front-end and the other has an Access front-end.
4. You hand over the entire db, front-end and back-end to the customer.
5. They load and run the thing from their own computer, or possible from their own server.
6. You never see, or have anything else to do with the db except for updates or support issues.

Is all this correct?

 

[amudhan]

except for step 2 & 3 all are correct.
I have only one version of access 2.0 dB. This has forms, report and others and also will be used by front end. A operator profile customer will use the VB front end and his technicial admin will use the forms, reports..

 

[ghudson]

Why not have everybody use the VB front end and retire the Access front end if you already have a working Visual Basic version for your front end?

 

[KenHigg]

Ok. Getting more clear...

You have the 2.0 Access database that has the tables and the forms, etc for one user role, and the the VB app. links to this .mdb for the second user role type.

Now, you want to send the user a file that has some code that will automatically convert the 2.0 .mdb to a newer version.

Correct?

 

[amudhan]

Yes. also I want the code to convert outside the dB and to be on the front end.

Also the option of retiring the forms is not an option since some functionalities are not available in the VB and is only in the forms and reports.

 

[KenHigg]

In my humble opinion, you should fix this by putting all of your front end in a .mdb and do away with the VB thing. I'm guessing that an auto-convert thing is not do-able. At best, I'm guessing a good set of conversion instructions is going to be your only option... (But I could well be wrong, and hopefully for your sake, someone else will chime in and prove me wrong...

Is this a db that addresses specific needs for a specific industry that you've developed and sold to multiple companies?

 

[workmad3]

From the sounds of it, you are either going to have to give instructions, as KenHigg suggested, going step by step through the process of converting (and on the front page saying "MAKE A BACKUP OF YOUR DATABASE FIRST!!!!", otherwise you have no fallback for the fool who breaks it). Other option is to get the list of things you need to do, go through them step by step, and at each step, come up with a routine that will handle them. If you have no way of telling if the user has modified or altered the original db, then you are going to have to do the first option and also say "Only works on an unmodified db. For help converting a modified DB, phone tech support, and they will assist you" or some such.