Access 97 won't play with .xls files

[visacrum]

My MS Office had been upgraded from Access 97 and Office 2k to Office 2k7.

I had to get Access rolled back to 97, and that worked fine.

When I try to import or link an .xls table (created in Excel 2007, but saved as an .xls file), the wizard itself gives me errors. First it says:

There is no object in this control

Then it pops up with a blank wizard window. Clicking Next or Finish returns:

The expression you entered refers to an object that is closed or doesn't exist.

I've tried searching around, and most of the solutions I've seen involve registering certain .dll's through regsvr32.exe on the Run command. But they're for Access 2000. I've found the files, but before I bother my I.T. department, who doesn't support anything, with these solutions, I wanted to see if a) this was plausible to try on 97 or b) if there is a specific solution for 97.

Yes, I have an I.T. department where I have to find a solution and ask them to run it because they've got the Admin rights, but they won't search for it. Ain't beaurocracy grand?

Oh! P.S. I'm on Windows XP. I don't think I have SP3, the computer won't run updates without I.T. running them for me.

 

[The_Doc_Man]

Rolling back Access to 97 only solved half the problem. Excel is still at a higher version of Access, and the .DLL files associated with that version are therefore still at a higher version. So when AC97 tries to open the XL97 support library, it ain't there. You would have had to roll back Excel, too.

Try this. Take your .XLS file in Excel and save it as a .CSV file. See if AC97 will touch that instead.

 

[visacrum]

Rolling back Access to 97 only solved half the problem. Excel is still at a higher version of Access, and the .DLL files associated with that version are therefore still at a higher version. So when AC97 tries to open the XL97 support library, it ain't there. You would have had to roll back Excel, too.

Try this. Take your .XLS file in Excel and save it as a .CSV file. See if AC97 will touch that instead.

Same thing happened. Any way to register the older .dll's just so it will run the import wizard? Is there any way to import this spreadsheet without the use of the wizard? Or any way to run excel 97 alongside 2007? I need the 2007 features in excel, I can't roll that back or I'm sunk.

 

[The_Doc_Man]

Try to roll your database forwards, then. Used to be that you could run AC97 and AC2K, then with AC2K3, but not sure about a 10-year differential. You REALLY don't want to do what you are trying to do, no matter how important it seems to use AC97. Trust me, it ain't that important.

Among other things, there are security issues and data compatibility issues all over the place with AC97. I'm sure that with some extra work, you can do something useful, but you have to remember that xx97 rolled off the support charts five years ago.

Here's a thought... do you have a machine you can use that does NOT have Office on it so that you could try to install Office 97 to do some data exports? That way, you could perhaps use networking methods to move data around to where you needed it - including the idea of using that data as a base for upgrading.

Though I'm sure that the AC97 code works just fine, you are going to run into more and more compatibility issues as time passes. Even if you make it work now, once you upgrade to various service packs, each one will vex you more and more. And let's not even THINK about Vista.

 

[boblarson]

Same thing happened. Any way to register the older .dll's just so it will run the import wizard? Is there any way to import this spreadsheet without the use of the wizard? Or any way to run excel 97 alongside 2007? I need the 2007 features in excel, I can't roll that back or I'm sunk.

Gotta agree with The Doc Man here. You're going to need to "bite-the-bullet" so-to-speak soon, so instead of living in the past you need to work at moving into the here and now. I know it is painful. I know it is not fun. I know that your boss probably doesn't care that you have to do extra work to get it upgraded. But, things are only going to get worse and not better.

As much as you are satisfied with using Access 97, it is an unsupported database and, once any of your users go to Vista (I WILL go there, unlike Doc) it is no longer going to work at all. So, you had better fix it now before you get to that point.

Now, that being said, I believe we have enough expertise on the forum here to help you get it upgraded. But, it probably isn't going to be a quick and easy fix.

So, anyway I just wanted to chime in on that. I know there are a lot of people that swear by Access 97 and that is fine but life moves on and so do computer programs.

 

[gemma-the-husky]

can ypou clarify please

does that mean A97 won't work with vista?

will A2003?

 

[boblarson]

does that mean A97 won't work with vista?

That is my understanding (although I don't use 97 so I can't verify that 100%, but that has been what I've heard from many

will A2003?

most definitely - that I CAN say 100% because I actually do use it with Vista.

 

[visacrum]

I know that the day will come when there will be no choice but to go with Access 2k7. But:

I'm waiting for a day when I actually learn how to import my forms and reports from 97 to 2007 withough having to recreate them - most especially with regards to unbound controls, lookup boxes that insert appropriate fields when a list item is selected, etc.

I'm waiting and hoping that the 'find and replace' add-on module is re-written for 2007.

We are not going to vista in the foreseeable future. This building has no standard, the IT department does not want to perform upgrades on most software (I've heard them complaining and the manager vowing he wouldn't upgrade Lotus notes to v7, much less going to Vista), and obviously they don't know anything about security flaws, they rolled me back without a peep through their virtual voyeur program.

I just won't have time until the beginning of the year to do some serious learning, and I know a lot of what I'm forced to deal with isn't the Utopian database world I'm constantly striving for. And I'm up against a sullen IT department whose idea of help is different than that of say, India, or the rest of the civilized world. If there's any way I can get these 2 programs to play nicely for a few more months, I'll take it and fix it next year.

If there's no way to do it without rolling excel back or access forward, then I'm sort of stuck.

 

[visacrum]

Try to roll your database forwards, then. Used to be that you could run AC97 and AC2K, then with AC2K3, but not sure about a 10-year differential. You REALLY don't want to do what you are trying to do, no matter how important it seems to use AC97. Trust me, it ain't that important.

Among other things, there are security issues and data compatibility issues all over the place with AC97. I'm sure that with some extra work, you can do something useful, but you have to remember that xx97 rolled off the support charts five years ago.

Here's a thought... do you have a machine you can use that does NOT have Office on it so that you could try to install Office 97 to do some data exports? That way, you could perhaps use networking methods to move data around to where you needed it - including the idea of using that data as a base for upgrading.

Though I'm sure that the AC97 code works just fine, you are going to run into more and more compatibility issues as time passes. Even if you make it work now, once you upgrade to various service packs, each one will vex you more and more. And let's not even THINK about Vista.

Thanks Doc Man. I don't have a virgin machine unfortunately. Everything's been upgraded at the insistence of my VP. Strangely, IT grumbled about the upgrade, but I got no resistance on the access downgrade.

I'm not exactly satisfied with 97, but until I can learn how to make certain things work in 2007, which I can't do til next year, I'm stuck with it. I liked a lot of Access 2007's stuff, there are just 2 things I can't get to work that were ruining my days here. So I need a workaround that will hold me over for just 3 months. I had even had Access 2007 and 97 simultaneously on the machine, but that failed after a while. And service packs aren't a problem, IT doesn't let us download updates, and they haven't forced them either. But I just need 3 months and I can get with it.

 

[boblarson]

I'm waiting and hoping that the 'find and replace' add-on module is re-written for 2007.

What "add-on module" are you talking about?

I'm waiting for a day when I actually learn how to import my forms and reports from 97 to 2007 withough having to recreate them

Not a problem that I know about. There are just some things that have to be done which you probably wouldn't have had to worry about if you had been upgrading as things moved along (I understand that wasn't an option) so yes there will be pain and work involved. I'm not trying to say it will be painless.


So it sounds like you are stuck. The problem is that Access 97 has no concept of future versions of other Office software. So, if you are dependent upon other parts of Office to do your work, you should really be using the same version (although with later versions of Access - 2000 and on it isn't such of a problem).

 

[visacrum]

What "add-on module" are you talking about?


Not a problem that I know about. There are just some things that have to be done which you probably wouldn't have had to worry about if you had been upgrading as things moved along (I understand that wasn't an option) so yes there will be pain and work involved. I'm not trying to say it will be painless.


So it sounds like you are stuck. The problem is that Access 97 has no concept of future versions of other Office software. So, if you are dependent upon other parts of Office to do your work, you should really be using the same version (although with later versions of Access - 2000 and on it isn't such of a problem).

There was an add-on that I purchased to make my job here easier. It's called 'Find and Replace'. It let me create a new database for a new meeting (my databases deal with meetings), find table names within the various and sundry queries and forms, and replace them with the new table names (year, month, location) in one fell swoop. Prior to that I had to do it using a module I imported from database to database, and it was a major hassle.

Find and Replace (www.rickworld.com. Believe me I'm not selling anything) only goes up to Access 2002.

Painful isn't the word for how great a time suck trying to update a database's queries and forms. I'm not in a place where table names can be generic. Well, some of them can, but not the main ones.

I'm accustomed to pain. But I'm not going to spend more 70-hour work weeks with this crud if I can avoid it. Hence my bright and brittle hope for a filthy workaround without masticating ammunition.

I think I'm stuck too.

 

[Rabbie]

can ypou clarify please

does that mean A97 won't work with vista?

will A2003?

I run Access 2002(XP) without problems on Vista.

 

[boblarson]

There was an add-on that I purchased to make my job here easier. It's called 'Find and Replace'.

You might check out the free V-Tools as it has something called Total Deep Search which can do essentially the same thing:
http://www.skrol29.com/dev/en_vtools.htm

...replace them with the new table names (year, month, location) in one fell swoop.

This tells me that you really do not understand database creation because your table names should not have any dependence upon the year, month, or location.

I'm not in a place where table names can be generic. Well, some of them can, but not the main ones.

That is because you don't know enough to do it. Properly designed databases have no need to have table names, or object names, renamed in order to work. You make one database and it will work for ALL of your meetings. So, yes I do believe you need some additional database training and hopefully you will get a chance to do so because it would probably blow you away if you learned to do what I am saying. You would never have to do another database for one of your meetings. You can use one for ALL of them. (that is the power of relational databases).

So, I wasn't trying to insult you by telling you this, but as I do Access development as my full-time job, I do know what I'm talking about and just want you to understand that the pain you feel actually doesn't have to be there.

 

[visacrum]

You might check out the free V-Tools as it has something called Total Deep Search which can do essentially the same thing:
http://www.skrol29.com/dev/en_vtools.htm


This tells me that you really do not understand database creation because your table names should not have any dependence upon the year, month, or location.


That is because you don't know enough to do it. Properly designed databases have no need to have table names, or object names, renamed in order to work. You make one database and it will work for ALL of your meetings. So, yes I do believe you need some additional database training and hopefully you will get a chance to do so because it would probably blow you away if you learned to do what I am saying. You would never have to do another database for one of your meetings. You can use one for ALL of them. (that is the power of relational databases).

So, I wasn't trying to insult you by telling you this, but as I do Access development as my full-time job, I do know what I'm talking about and just want you to understand that the pain you feel actually doesn't have to be there.

Thank you. Honestly, I don't question anyone's knowhow here. I know what the databases SHOULD be, truly. And I've been trying to get the boss to accept facts for 3 years now. Even to get to where I am has been a struggle (you should have seen the week-long argument over using if/ then fields in a mail merge), and every time I leap forward to a concept she can't grasp, I get pulled back (Godfather style). So I'm stuck with lousy database structure and renamed tables until she gets her head around normalization. If there's a way to make someone ELSE learn something they can't understand, I'm all ears.

In the meantime, I'll check out your link and suffer. Thank you again for your help.

 

[gemma-the-husky]

the thing is, when you are using a dbs you shouldnt ever really (need to) ever see the tables ever, so the table design shouldn't matter

[in fact its dangerous to see the tables, because you can edit data in an uncontrolled fashion - the tables should just contain the data, structured in a normalised fashion]

bear in mind that access isnt excel , although it resemble excel - its far more powerful for data handling - interaction with the dbs should be through queries and forms, which give you a controlled organised window on to a subset of the data.

just do it so it works, and your boss surely wont have any objections

 

[The_Doc_Man]

And I've been trying to get the boss to accept facts for 3 years now.

I work with the U.S. Dept. of Defense where sometimes it LITERALLY takes an act of Congress to do something. No smilies when I say that, either! So I understand all too well the nature of an environment where "the boss" doesn't want to change things. Well, here's some ammunition. If you want to print this, please go ahead.

To visacrum's boss: Please be advised that per the U.S. Dept of Defense, it is no longer allowed to run AC97 on any government computer that deals with any personal or mission-critical data. We were mandated to upgrade several years ago. Part of the reason for this mandate is the MDAC module that is part of MS Access 97. According to several different security group surveys, MDAC is always in the top 20 security risks of any organization that has it. MDAC is so bad that no patches are available to bring it up to a reasonable standard. The only available fix was a total upgrade to a much newer version. Currently, the U.S. Dept. of the Navy uses Access 2003 and is slowly migrating forward towards Access 2007.

For our new member visacrum to be able to help you, it is imperative that he be given a consistent set of tools. The Excel upgrade he described is not compatible with Access 97usage, and to our way of thinking, never will be. Despite the apparent cost of this operation, you need to recognize that "getting along with older software" is like letting your cholesterol build up until you have a heart attack. At some point, something will stop working completely and no quick-fix will be available. Only a full-blown surgical intervention will save the situation.

We on this forum live and die by our ability to work with MS Access and other Office products. We are experts in the field. It is the considered opinion of several of us, including two current Microsoft Most Valuable Professionals (in Access), that you should upgrade the database despite the pain associated with this change. If you are in doubt about the Microsoft MVP program, search for Microsoft MVP on your web site and read up on what is implied by that title. Mr. Larson and I have the honor of being current MVPs, and this forum includes several other such persons who were on previous MVP lists.

We wish you good luck in your business endeavours and hope that you will continue to allow our new friend visacrum to expand his usefulness to your business by becoming an expert in the Microsoft Office product line including Access.

sincerely,

Richard W Hunt Jr., PhD
Microsoft MVP (Access)
35-year veteran of computer administration, programming, and operation