going from Access97 to Access2007 (1 Viewer)

[reptar]

Hi, our company currently uses Access97 to contain all their information on clients. It is time to upgrade and i was wondering if there is anything that we should know before we upgrade to Access2007.

Thanks,

 

[Simon_MT]

There seems to be a good migration to 2007.

It you have s split database do the backend first and then the front end.

Major issue is that you can not have duplicate Objects so if you have a table called Clients, a form called Clients and a Module called Clients these have to unique.

Some db Declarations need changing:

Dim db As Database >> Dim db As DAO.Database
Dim rs As Recordset >> Dim db As DAO.Recordset

Set db = CurrentDb instead of referring to Databases(0) Workspace(0)

Only issues were minor. On the datasheets down arrow appeared on all the columns which could be removed by setting the datasheet from Propery Shortcut Menu to No.

Database Window has disappeared but in Access Option turn on Navigation Pane and on the Quick Access ToolBar add More Windows to be able to switch between objects. I would also suggest Using windows themed control on Forms as this makes the command buttons more presentable.

The developers interface has changed completely but this is something you have to get familar with. Couldn't find Align to left or right but if you select the Controls, the Right Click gets you there.

Good luck, I have to admit the migration was not too bad.

Simon

 

[ListO]

Perhaps I'm the nutcase here, but after avoiding the issue of upgrade for quite some time, i've finally downloaded a 2007 demo, and I cannot believe how #$@*ed up 2007 seems to be! Wow! I've been programming in this environment for 10 years and I cannot get a handle on WHY MS would make this SO MUCH MORe difficult. Custom Menus seem to have been removed. I can't even figure out how to close a table. That ribbon! WTF? Must the menu's be so HUGE?- taking up so much screen space?

Am I alone? Or is the AWF full of frustrated rants and I'm not finding them? AAAAAAAHHHHH!

 

[Simon_MT]

Yes, I do understand, but I never used dropdown custom Menus anyway I used forms instead. You can minimise the Ribbon but I still feel that before you dismiss 2007 completely, and admittedly, it does taking some getting used, it is as bigger change as going from Windows 3.12 environments to Office 97 was, but persevere with it.

There are a few glitches and 1997 is a great environment to develop in but sometimes change is "good" for us seasoned practioners!!!

Simon

 

[jwhite]

Converting from Access 97 to Newer Versions
Converting from Access 97
Converting to Access 2007

 

[ListO]

WOW!!!!

I thought I was just being grumpy, but---

Simon, you seem to be a bit apologetic about 2007. Perhaps YOU don't use drop-down menus, but I've written a pretty complex database that has a custom menu for each major form, and it's for a reason- the menus are context-relevant and designed to give users an appropriate set of choices for each form in which they work. If I have to re-write each and every one - now in XML - it's a lot of messing about just to get myself back to square one.

fdcusa, thanks very much for the very frightening links. It would lead me to believe that the professional world is not likely to be moving away from '97, or at least '00 or '03.

My '97 database opened and works pretty well in '07, (except for my custom menus being buried below the 'Add-Ins' menu) but I'm concerned about maintenance and improvements that I would like to do.

According to a Microsoft conversion doc I found in their knowlegebase, their emphasis seems to be on eliminating VBA and replacing it with Macros! Huh? I thought it was supposed to be the other way 'round! If I want to use a lot of macros, I might as well be using Filemaker! No way can I do the calculations and data manipulation with macros that I've been doing with VBA. Input parsing and validation is such an essential part of this- in the depths of my ignorance I cannot see how to do most of this with macros.

This whole thing seems like some bureaucratic decision to make the product more 'secure' than useful. Microsoft could make this product COMPLETELY 'secure' by discontinuing it. Excel and Word, too.

Again, perhaps I'm just a myopic ranter, railing against change. Perhaps someone can suggest things that are superior in '07. 11 years should have brought some constructive additions.

Maybe there are some great new additions to Access '07 that I haven't encountered that make all the conversion efforts worthwhile? I was hoping for the ability to make buttons that weren't gray, but that doesn't seem to have happened. Is there anything that's remarkably better?

 

[jwhite]

Actually, it's my experience that very few in the professional world still use Access 97 -- the reason being that installing a full version of Office brings along the next version of MS Access. I started with Ac2003 and haven't had any interest to look at Ac1997 code.

You are correct that many are delaying (or not) converting from Ac2003 to Ac2007, mainly because of the Ribbon Interface - having to convert Menubars to Ribbon XML. And the worst of it is doing away with User Level Security. If we choose to convert our current flagship product to work with Ac2007 new features, we will be moving Security 100% to the Back-End (SQL Server), and have to re-tool the many features that work in Ac2003 but do not in Ac2007 (User modifiable MenuBars comes to mind). Currently our Back-End is SQL Server with the Front-End as MS Access using ULS.

I deplore Macros, and haven't read where MS states doing away with VBA -- never could happen. At the end of this article it talks a little about the new Macro functionality in Ac2007.

Here's some more links (all available by googling...) that may interest you:
Ac2007 Developer Extensions and Runtime are FREE!
Microsoft Office Access 2007, Features List
Guide to the Benefits of Microsoft Access 2007
Information Sharing is a Key Feature of the New Access 2007
Microsoft Office Access 2007 top 10 benefits
Microsoft Access 2007 Home Page
A discussion of what is new in Access 2007 (MSDN Blog)
Ac2007 MSDN Developer Reference / Technical Articles / Visual How Tos
Access 2007 Specifications
Avenius Gunter, site specializing in Access 2007 Ribbons

With regard to Command Buttons, yea, still the gray background, but you can get around this by using a Raised Label with the Backcolor of your choice. Though the Event collection is slightly more in Command Buttons. And now you can have a pretty picture and a Caption.

In speaking with my Senior Director who was with Microsoft as an Access Team member, said at some point Access was handed over to the "Office Group", which explains why Ac2007 seems primarily an 'eye-candy' upgrade, and Ac2007 resembles more of an "Office Tool" feel (like Outlook, Word, Excel) rather than an RDBMS. Dare to say, "dumbing-down"... Some of the new features, like multiple attachments in one field and adding fields on the fly, seem atrocious. IMO, MS does not see Access as an 'Enterprise Solution' anymore, and are pushing people to their Visual Studio products and SQL Server as the RDBMS, which is geared for Web based applications.

Me, personally? Still using Ac2003. However, have a look at the new features yourself to determine if the changes are favorable to you.

Not looking to start a debate... Just thoughts.

 

[Mike375]

This link form fdcusa's post seems to give a positive view although the article is short

http://www.databasedev.co.uk/access2007_four_things_to_love.html

As a side note do Microsoft still sell the A2003 runtime version? This could seem worthwhile if A2007 is bad. Would there be problems with A2003 runtime on a computer that had Office2007/Access 2007

 

[jwhite]

The Ac2003 runtime was always distributed with full Access 2003, and developer licenses could be bought for distributing the runtime version with applications for execution on systems without full MS Access. MS no longer sells Ac2003. The Ac2003 Developer Extensions can still be had by buying one of the Visual Studio packages (Access 2003 Developer Extensions), or can be had on the used software market (watch out for bootlegs...)

It is incorrect to label Ac2007 as "bad". It is an evolution of the MS Access application that enhances (and deprecates - ADP/ULS/Menubars for instance) features. It is up to the Developer to determine if the new version is inline with their design, if they need/want to modify it to work with Ac2007, or stick with Ac2003. The same has played-out with VB to VB.Net, and is being played out now with SQL 2000/2005 >> SQL 2008. Microsoft has written a "book" to help the transition: SQL Server 2008 Upgrade Technical Reference Guide.

There are no issues with running an application calling Ac2003 Runtime with the PC having Office 2007. However, having Full Access 2003 and Full Access 2007 introduces issues with workable solutions in-hand. The biggest thing with the latter is there is a delay between switching to/from Ac2003/Ac2007 Design Mode (msaccess.exe), as Access has to 're-configure' itself.

 

[Mike375]

The Ac2003 runtime was always distributed with full Access 2003, and developer licenses could be bought for distributing the runtime version with applications for execution on systems without full MS Access. MS no longer sells Ac2003. The Ac2003 Developer Extensions can still be had by buying one of the Visual Studio packages (Access 2003 Developer Extensions), or can be had on the used software market (watch out for bootlegs...)

I did not realise that. I have 5 copies of Office 2003. So each one of those has the runtime version but I need to buy a licence to use it, is that correct. I am about go down the track of the runtime if I can do it on Access 2003 then that would be great as I would like to avoid 2007, at least for now

It is incorrect to label Ac2007 as "bad". It is an evolution of the MS Access application that enhances (and deprecates - ADP/ULS/Menubars for instance) features.

I am sure you are right. Similar to Vista, I have not found it to be as bad as is often reported and in fact I tend to use my computers that have Vista as opposed to those with XP, except when testing etc.

There are no issues with running an application calling Ac2003 Runtime with the PC having Office 2007. However, having Full Access 2003 and Full Access 2007 introduces issues with workable solutions in-hand.

I would only use the runtime when the people did not have MS Office Professional. Although it would appear that one advantage of the runtime is not being limited to number of computers. That is, the business only has one copy of MS Office but has 7 computers etc.

 

[jwhite]

You would have to buy the license to DISTRIBUTE a copy of Ac2003 Runtime with your application installer. Your installer would then first check if full MS Access is already installed -- if not, install the Ac2003 Runtime before installing the rest of your application.

Correct, getting the license to distribute Ac2003 Runtime with your application will preclude installed-to computers from having to have full MS Access installed. Thus saving your Clients money.

 

[Mike375]

Thanks for that. Is the move to runtime a big learning curve. Is there some sort "average time" from day 1 to be able to do what is required with run time. My understanding is that you have to make the tool bar.

Do you basically make what is required in A2003 and then convert it to runtime is a simila rmanner to converting to MDE file?

In short, if I had run time tomorrow what would be a reasonable time before I could say to a little business....it does not matter if you don't have Access

 

[jwhite]

The time it takes you to install your package...

MSAccess.EXE (Full Version): Loads accessible Wizards for Design "Assist".

LaunchAccess.EXE (RunTime): Does NOT load the wizards. It is a 'deprecated' Design Mode, unless you include a startup form or use the Autoexec Macro to automate starting the application appropriately, which would disable the Database Window, for example.

A long-winded example shortcut Target for Runtime might be:

"C:\Program Files\Microsoft Access Runtime\OFFICE11\Launchaccess.EXE" /excl /profile "XYZ" /wrkgrp "\\Server1\shared\MyDatabase.MDW" "C:\Program Data\MyDatabase.mdb"

Side Note: MSAccess.EXE = 6,502KB, LauncAccess.EXE = 115KB -- BIG LOAD-TIME difference!

 

[Mike375]

I have the picture. Many thanks.

 

[AN60]

Great, for a user with limited access skills 2007 is a learning curve that makes my life difficult when I try to open my old 2003 db's & edit anything. So far I haven't found anything that has helped in any way or saved me time. In fact just the reverse, I've spent lots of time trying to figure the thing out. What would be wrong with making access easier to use & have the ability to use old db's without having to fiddle around so much? I am not moving from 2003 until I positively need to.

 

[boblarson]

Great, for a user with limited access skills 2007 is a learning curve that makes my life difficult when I try to open my old 2003 db's & edit anything. So far I haven't found anything that has helped in any way or saved me time. In fact just the reverse, I've spent lots of time trying to figure the thing out. What would be wrong with making access easier to use & have the ability to use old db's withoug having to fiddle around so much? I am not moving from 2003 until I positively need to.

Yes, it is a radical change from what you've been used to. Yes, it will take more time to learn than in the past. However, it is what it is and it is the future. So, I would continue to learn it while working with the other versions because the path is set and, just like you cannot go back to a life without computers, you aren't going to get your old way of doing things back. There will be a point at which you will have to change, regardless of whether it feels good or not. So, you can either fight it or just swim along with the flow. I know that it isn't easy, but then again nobody said life was going to be easy.

So anyway, my point is that you can complain all you want about it, but it is what it is and the sooner you start learning to work with it instead of fighting it, the better your life will be. But that is your choice.

 

[boblarson]

And actually, it is harder for someone who has used previous versions of Access to learn Access 2007 than it is for someone who has never used Access to learn to use it. And, it is easier for someone who has never used Access to learn to use Access 2007 than it is to learn how to use previous versions.

 

[Banana]

And actually, it is harder for someone who has used previous versions of Access to learn Access 2007 than it is for someone who has never used Access to learn to use it. And, it is easier for someone who has never used Access to learn to use Access 2007 than it is to learn how to use previous versions.

You know, I would love to round up some grannies and throw them in front of 2003 and 2007 and see which group of grannies get it first.

Someone have to be scientific about this, you know.





EDIT: And for sake of being on-topic, I have to second Bob's assessment- 2007 is the future, so best to learn it sooner and deal with what it have and doesn't have. If you care to, google for Allen Browne's article about when to upgrade to 2007. I think he's pretty spot-on on his assessment.

 

[Simon_MT]

An alternative to multiple users could be using a Terminal Server comes with a 5 user licence and using mde or accde files. Load Access onto the terminal server and individual front ends and a backend on a file server or the Terminal server itself. Then use Remote desktops to avail the Terminal Server. The advatange over the distributing access to each desktop is that the application is in one place.

So far as Access 97 is concerned, it is still probably the best Access environment, but it just looks a bit tired. We used to use Labels on the Menus in 97 but created Command Buttons in 2007. We're a bit old fashioned and like proper Menus.

Our application also substanital and I understand the grief the Ribbon has created, it is disproportionate to its functionality and "ease of use". We never use macros. VB is so much easier to use and manage.

In essence the experience from Access for Windows 3.x and Office 1997 was a lot more arduous than the upgrade to 2007. It only really worth the effort for its appearance and web like features. I didn't bother with Access 2003 because of issues with SP3.

Simon

 

[Darth Vodka]

we have a lot of 97 at my work

my initial thoughts:-

if you have a db where you amend the live if it goes wrong whilst users are in it - you cannot in 2000+ you are forced to do things better, using a dev and a live version and almost ceratinly having a seperate back end

if you have lots of 97 apps linked...make sure you convert the ones that aren't linked into another one first...bit of a nightmare, i recently wrote code that identified which of our 100 1997 apps linked to each other (got it if you want)

when 2000 opens 97, it needs to convert it to open it. when 2007 opens 2000, it's fine, it's leaves it alone. so a conversion to 2007 could *really* be a conversion to 2000