Mickey$oft – Access97 –> Access 2k conversion err

Topic

New Reply

How can people reliably trust M$ when you upgrade and have to re-write your apps? I just finished a project for a university in Access 97 and they wanted to see what would happen when its converted to Access 2000. Guess what? It bombed on the first form. Field name errors and message boxes that need to be corrected. And this was in the first 60 seconds? What else will need to be fixed heaven only knows. Sorry for the rant but how do I justify this to the customer?

Reply | Quote

Replies

Did you use the built-in wizard to convert the database or did you just import everything into a new Access 2000 database? Or did you try to turn it into an ADP?

I’ve converted a lot of Access 97 applications to Access 2000 with nary a problem, and I haven’t had to rewrite any of them unless I decided to use ADO instead.

However, if you just import everything, you’re going to start off with difficulties because the default object model changed between versions. To make your 97 code work, you shouldn’t have to do anything but turn off the ADO reference and turn on the DAO 3.6 reference. If you tried to switch to an ADP, I would not surprised by the problems, since ADPs are largely based in SQL Server and rely on that version of SQL and T-SQL.

Oh … you weren’t using a compatibility library, I hope. Those things have caused more problems than nails on the freeway, and I wish they would evaporate. They allow and even encourage you to write code that is several versions out of date and will NOT run in the current version of the application. They aren’t a problem that’s new to Access 2000, either …. There, that’s the end of MY rant!

Reply | Quote

I’ve been testing the conversion of quite a few of our databases to Access 2000 (we’re planning on converting “for real” in the fall and we don’t want surprises). When the automatic conversion completes without errors I haven’t had to do anything about ADO; the conversion process does the appropriate things with the references.

If the automatic conversion fails (and I’ve had that happen several times) and you convert by importing everything, that’s when you need to watch your references or you’ll get lots of compile errors. Make sure (unless you want to make changes to your code) that the DAO stuff comes before (i.e. closer to the start of the list of references) than the ADO stuff. I haven’t tried turning off the ADO since it didn’t appear necessary once the order is right; I’ll have to look at that sometime.

Reply | Quote

I just copied my 97 database to a separate folder and then opened Access 2K and pointed it to my copy of my 97 db. I let the wizard to the conversion and that is what generated the errors. How do I then turn off the ADO ref and turn on the DAO 3.6 ref? I’m not sure what an ADP is so I don’t think I did that. Also I’m not sure what the compatability libraries are so I don’t know if that is a factor here either.

Thanks for responding to my rant Charlotte. As I mentioned before, I’m a 15 year veteran RPG programmer, but was laid off at the beginning of the year. Since then I’ve been trying my hand at Access and doing some sideline work between jobs. If I thought I could make a living doing stricly Access coding I might consider it, but little trivial things like these errors keep me from abandoning my AS/400 platform and skill set. But I will hang in there and continue obsessing about Access, and will hopefully learn enough to be dangerous.

Reply | Quote

What you did is the usual way to convert, and if it didn’t work it has to be because of the code itself. In a conversion, the DAO reference is set automatically and ADO left off, so that shouldn’t be the problem. Don’t worry about the ADP because they aren’t created by direct conversion.

It’s hard to guess at what might have caused the problem because I have no clue as to what your VBA coding level might be. If you built your Access app using macros and wizard-generated code, then it isn’t completely surprising if it failed. The wizard-generated code in 97 was actually 95 code (don’t ask me why, I’ve often wondered myself), so there may very well have been stuff that bollixed up the conversion. If you go to the VB editor in Access 2000 and select Tools–>References, you’ll see a list of references set for the project. If the Microsoft DAO 2.5/3.5 compatibility library isn’t checked, try turning it on and then compiling the code after you close the references dialog. That allows obsolete code to run under the current engine, although it’s a poor substitute for keeping your VBA skills up to date. You said the code threw errors, so I assume the conversion completed, right? If the wizard couldn’t complete the conversion, that’s a different problem and dealt with differently. In that case, post the details and we’ll walk you through the alternate route.

I do make my living building Access applications, so don’t give up on it yet. Access is a slightly schizo program because it is marketed as an end-user database and so has a lot of stuff that serious developers should never use (like the compatibility libraries!) and few tools for professional developers. It’s getting better as it goes, but building high quality in Access is just as hard or easy as building them in any other development environment. It’s primarily a question of knowing your tools extremely well and using them appropriately.

Reply | Quote

This is the error. I copied it from another posted thread. I have the following statement in an Access 97 database, that refers to a field on a different form. Problem is when the statement executes I get an error saying Access can’t reference the field ‘forms’. How do I correct this in Access 2K?

If Forms![Events Select]![event id] “” Then….

Reply | Quote

Actually, Charlotte, I’ve got databases that do not convert using the automated method, but when manually converted (by importing and then moving the DAO reference up near the top of the reference list) compile and run fine. So, you may be right that there is some code that the automated method hiccups on, but that does not mean that the code won’t compile in A2000.

Reply | Quote

I’ve had the same experience, but I’ve never had code absolutely break down in a conversion unless it was bad to begin with. Access 97 apparently ignored some things that cause Access 2000 to spit up.

Reply | Quote

When you say “a different form”, are you talking about an independent form or a subform? Invalid references to subform controls can cause this error even within Access 97.

Reply | Quote

It’s an independent form.

Reply | Quote

If the form exists and is not misspelled AND is loaded when the code executes, then the only thing I can suggest is that the form is corrupted. Sometimes that will show up in a conversion, and the simplest way to test it would be to create a new Access 2K database and try importing all the objects from your Access 97 app. If it is corrupted, Access should fuss about it. Even if it isn’t corrupted, it still has to be loaded for your code to work, so make sure that’s happening as well.

Did you check to see if you had any missing references? That can cause normal code to break down, so it’s always the first place to look when the unexpected happens.

Reply | Quote

Ok – the form exists and it is loaded when the code executes, however it is not the form with the current focus. The form with the error is not a subform. I created a new 2K db and imported all of the objects. Nothing appeared to be corrupted but upon trying to display the switchboard, I received an error on the following statement:

“Dim dbs as Database”

And all I’m trying to do is replace the A97 statement ‘If Forms![Events Select]![eventID] “” Then’ with the 2K equivalent. How hard can this be?

Reply | Quote

Back up to earlier in the thread. When you create a new Access 2000 database, the default object model is ADO. You have to set the reference to DAO and uncheck the ADO reference so your code will compile.

As for the other, if you still have a problem, look at the form you’re referencing. Is the control’s visible property set to True? If not, you won’t be able to reference it from another form, because its scope is private to that form.

Reply | Quote