Moving Access 97 to Aceess 2007

First post and hopefully something that hasn't been asked and answered already. Although it probably was 10 years ago when everybody else made this switch to Access 2000. I have been fighting to tooth and nail to stay the Access 97 course as long as I could. I am not a programmer, but I play one at work. We have been using Access 97 (DAO 3.5.1) for 17 years and it has worked wonderfully. But since the most recent computer crash, it is time to get the upgrade and update religion flowing. 32 bit XP won't be around forever. I now have a Windows 7 - 64 bit computer running the trial.

So moving to Access 2007 is going to be a chore. As I prepare for the switch over, I have a 60 day trial version of 2007 on which I have converted the front end A97.mdb to 2007. I haven't tried linking tables to the back end mdb (a97), where all the tables are. Of course when I converted it stripped all and I mean all the modules from the database.

My first issue will be getting the A97 VBA modules imported to 2007 and learning what changes need to be made with the newer vba language so that they will function again. I have saved all the A97 modules as text files and am assuming that I can just create a new module and import the text files, even though they won't work initially. They contain some laborious code, mostly just calculations for invoice line items and grand totals.

I don't create from scratch vba code, although I can pretty much edit what has been written, as long as it is not programming related. Where I fall down is references that are no longer available DOA for example. It is now something else. Recordset properties, dim statements etc.

My question is, is this something I can handle as an amateur, or is this something that needs to handled by a pro? Can the pro look through the code text files and point to code that won't work and know how to replace it with current vba code? Or is it more involved like all the forms and reports that won't work anymore and will need to be updated as well?

Is there some cross reference guide that says a line of code in VBA 3.X.X should now be replaced with this updated format and show an example?

I apologize if my question is kind of vague. My mind is a little overwhelmed, the navigation of the new interface of Access 2007 alone is just not as easy as 97 and certainly not familiar.

Any advice, references would be greatly appreciated.

Dave

Welcome to Forums! :wave:

Since you are [finally :)] ready for an upgrade much better option would be to move to MS SQL 2008 Express - not only it's free but also is much more robust and better database engine.
You can also built much more flexible gui using perhaps VB 2008 Express which is also a free tool.

Although it's very hard to keep up with technologies it sounds like it's about time you (or perhaps your business) do that.

That's all I have to say.

I would have to agree with RhinoBull. If your going to be starting fresh, and either paying someone to code, or learning something new, you might as well go with a (free) and more advanced SQL Database.

I have nothing against Access, I use it myself for some projects.

One major difference is that there is no longer Jet User/Workgroup security:
Access 2007 files no longer supports Jet user (Workgroup) security , so if you had usernames and passwords with workgroup security in your old database, you would have a completely open database in 2007. All users would have full access to the database with no unique security settings.

Also, if you are planning to have others use this on different computers, each computer would need to have Access 2007 installed on it. (Licensing costs)

I don't have all the answers, as I am working on migrating some databases to SQL Server Express myself.

Rhino and Rack thank you for your responses. I was fully prepared to spend some time learning the new vba code to make this work, but...

I don't know if I am just plain lucky or if I am in the middle of a twilight zone episode where I wake up and this all goes away. Here is what I did last evening:

I have Access 2000 which I don't like, but installed it again. I opened my front end mdb in A97, compiled it, converted it to A2000. And then took it over to the 64 bit W7 machine with the Office trial on it. Before I attempted to convert the A2000 version to A2007 I downloaded and installed the free express sql that Rhino mentioned with the intent to check it out. I only mention this because it may have a bearing on why the later converted to A2007 version is up and running with some minor changes to locations of Outlook and Ghostscript in a couple call statements. References seem to be all good, none are missing and they show the current version of Outlook 12.

The only thing I have not been able to test are the actual printing of reports as I have no printers connected to the W7 machine.

Creating invoices and quotes has some extremely complicated code as we have so many variables with our products and I was expecting to do tons of battle in this area of the program. Lots of macros that are intertwined with the vba modules. Aging for statements is another similar area. So all of this is working. I was also able to get the automated process of emailing quotes and invoices working (this is where ghostscript comes in) by installing the 64 bit version of ghostscript 8.70.

So I am extremely happy for the moment and fully expect something to come up and bite me in the you know what at some point.

So if this is all truly good that will give me more time to spend converting the back end A97 tables to sql and implementing that.


Edit:

One other reason that this might be working so smoothly - I forgot that I have Windows XP mode installed where I have Access 97 installed. I also have SR-1 and SR2 as well as Jet 3.5 installed. Is it possible for the two systems XP mode and W7 to share files that would make the A97 code compatible or work within W7 while running A2007? XP mode is not running. I would probably need to uninstall Virtual PC and XP mode to find out. Could ruin my whole day.

I was almost ready to mark this thread as solved - hmmm

Well, good luck to you.
If you are serious about experimenting with different database engines then [besides MS SQL] I'd recommend to also look at Oracle Express 10g and MySQL.
Both db engines are free and far more superior then MS Access.