Two versions of Access: problematic!

Topic

New Reply

Hi All,

I currently have both Office 2003 Pro and Office 2010 Pro installed on my main desktop machine.
Of course only Outlook 2010 is installed as you can have only one version of Outlook installed.
Here are the problems I have beeh having.

[*]Every time you switch between versions you go through this installation/configuration process {happens w/Word also but NOT Excel go figure!}.
[*]Any Database with references set to Outlook will error out upon starting if you start it on the wrong version, e.g. if I work on a DB on another machine with only 2003 installed then bring it to my desktop and start 2010 it errors out because of the missing reference.
[*]When switching back to 2003 from 2010 Access retains the reference to the Access Ver. 14 library and will not let you switch it back. See graphic.

I don’t know if there is a way around all this other than working on DB that have to be used on 2003 on my second desktop that has only 2003 on it. :cheers:

Update: Please note if I work on the DB in 2003 on another machine then transfer it to the machine with 2010 as long as BOTH 2010 Word & Access are the current programs, e.g. last versions run, things work just fine.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

Can’t you just use Save as 2003 from 2010 and get rid of 2003 from that PC?

Save databases as MDB files

Bruce

Reply | Quote

I usually just do all the work with 2010 (using mdb files in 2000 format) then fix the References when I install on a machine with an older version. I have to be careful not to use things that depend on having the newer version.
I do have 2007 installed but rarely use it. So I can set Refences to the 2007 version if I want, then they stick.
Outlook Refence needs to be to 2010, and this does not cause me problems if I open in 2007.

Reply | Quote

John & Bruce,

Thanks for the input.

I do save the DB as .mdb as it will always be installed on a 2003 Pro system. I did forget to mention that when delivered the DB is locked down as far as I can get it. The VBA Project is PWed and unviewable, DB Window hidden, Menu hidden, Toolbars locked, even the startup bypass key is disabled along with the big red X on the Switchboard. Why you ask? Too many ID10T errors by the users. It is also important to realize that I support this DB while traveling in our RV by emailing a replacement front end which makes it hard to change references at install. I know I could use TeamViewer or similar program but in the RV I very seldom have a connection with enough speed to make that practical. Also this is a volunteer effort for our home owners association.

I’ve pretty much come to the conclusion that I’ll have to dual boot my laptop so I can have both office versions installed w/o conflict. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek,

The issue you are having with the Office App references is due to using Early binding or setting the reference in the VBE. Access will usually do a automatic upgrade of the Object References. But it will not automatically downgrade References due to possible backward compatibility issues. . The good news is there is a solution. Use Late Binding. I have clients that run my apps with a mix of office Apps without any issues using Late Binding. This also make is critical to have a split database with the front end not shared to avoid reference issues.

See: Late Binding in Microsoft Access

FWIW: As part of your lock down process is there some reason you are not making a MDE? This would avoid the need for the VBA Project password since all source is removes and design mode is disabled. It will protect your app a lot more than a VBA password. The VBA Project password can easily be retrieved and removed.

Reply | Quote

HTC,

Thanks for the input. I do have the database split FE/BE. I’ve never used the .MDE option I’ll explore it and see how it works. Thanks for the link about late binding. I knew about it but didn’t think of it as a possible solution, thanks again for jogging my memory. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You’re welcome.

Good luck with your project.

Reply | Quote

Hello Again,

Update: I tried to create a .mde file on two different machines and get the same message: “Microsoft Office Access was unable to create an MDE database.”

The help associated talks about large DB with many objects, I counted 115 in mine, and that it has to create TableId for them and can only have 2048 open at a time. Well, there should be nowhere near that many in my DB. Any suggestions? :cheers:

FYI: I did a decompile and compact and tried again…same result!:angry:

Update: Fixed! I did some googling and found out that a compile error could cause the problem. Found error in code that used to work! Checked code but not in error! Turns out that the References have to be in a certian order for the code to compile {which I had noted at the top of the Module but forgot about}. I think they got out of order when I was switching Office versions and trying to fix them when I switched back. Once the references were reordered everything is copesetic and I can now create a .MDE file. Moral of the story don’t only write documentation READ IT!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

The most common cause for a database to not compile into a mde or accde is due oi code issues. This includes reference issues.

If you can make an MDE/ACCDE then you know your database does not have any VBA code syntax issues. There still could be bugs in your logic, but you staill are better of then if you don’t compile to check for errors.

Reply | Quote

HTC & All,

Well, I got through the conversion to using Late Binding along with using the Compiler Directives so that I can do it either way Early or Late {Early for development, Late for Production}. This works great!:clapping: I can switch between Office 2010 & 2003 w/o a problem when using Late Binding.
The article HTC pointed me to was just concise and easy to understand.:thewave::thewave:
It did leave out one thing you need to be aware of though. The Compiler Constants {#Const} have a Module Level Scope. So if you have bindings in more than one module you need to set up a #Const at the top of each and remember to change them all when switching between Early & Late binding. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Again All,

As a follow up I found out you can create a Global Scoped compiler constant if you set it in the VBA Project Properties under the Tools menu in the VBE. If you do this however I highly recommend you leave comments in your modules to that effect, e.g.:

Code:

Option Compare Database
Option Explicit

'Set Compiler Constant for Early/Late Binding conditional code
' 0 = Early Binding
' 1 = Late Binding

'Set in Tools->ARB Properties... Global Scope if set here Module Scope
'#Const LateBinding = 1

Note: You do not use the # when setting the constant in the Project Properties dialog.

Here’s a MSDN Article on Conditional Compilation. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for posting that info. Most people have never even heard of or use Compiler Constants. They work great for developers for switching between Early and Late binding, debugging code, etc.

Reply | Quote

Getting back to the issue of the Access 14 reference, with two versions of Access installed, you can prevent this problem if you use virtualization. I use VMWare Workstation, which, while not free, is a very good product. Microsoft offers a free alternative (Virtual PC). There are some other free virtualization software products available as well. One requirement: you need to have a separate license for any software that you install in a guest machine, including the operating system. But, setting up virtualization allows you to essentially have one or more computers within your main (host) computer. I have guest machines available with Windows 98, 2 with Windows XP (one for A2003 and one for A2007), Windows 7 RTM, etc. It really is slick.

You will want plenty of hard drive space and RAM memory to effectively run a host machine with a guest machine.

Reply | Quote

Windows 7 Professional (and above) provides XP mode for free with Virtual PC.
http://www.microsoft.com/windows/virtual-pc/

You would still need a licence for the Office version you install.

Reply | Quote

I myself know very little about MS Access, but I’ll pass along a comment from an expert Access user. A lady in my church does Access programming for a living, and wrote the Access programs we use for our church management databases. We recently moved up from Office 2003 to 2010. She said Access databases written for 2003 will not run under 2010 and must be extensively rewritten to do so. Her directive to me, as the one doing the upgrades, (new computers, etc.) was do not install Access 2010 but rather continue to run Access 2003. Later, when she has time, she will rewrite the databases to run under 2010 and THEN we can continue the upgrade to Access 2010.

Reply | Quote

I myself know very little about MS Access, but I’ll pass along a comment from an expert Access user. A lady in my church does Access programming for a living, and wrote the Access programs we use for our church management databases. We recently moved up from Office 2003 to 2010. She said Access databases written for 2003 will not run under 2010 and must be extensively rewritten to do so. Her directive to me, as the one doing the upgrades, (new computers, etc.) was do not install Access 2010 but rather continue to run Access 2003. Later, when she has time, she will rewrite the databases to run under 2010 and THEN we can continue the upgrade to Access 2010.

Sorry, but I have to disagree with her statements. The database runs just fine in 2010 the only problem was with using automation which requires references to library’s for other Office products, e.g. Word & Outlook in this case. Simply switching to late binding from early binding (as indicated in posts #10 & #11 in this thread indicate). I didn’t have to rewrite anything else and this is quite a complicated DB with lots of VBA. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

She said Access databases written for 2003 will not run under 2010 and must be extensively rewritten to do so.

Your 2003 databases should run just fine in Access 2010, without further modification (assuming they do not contain old methods that *should* have been updated long before Access 2003 was released). Are you able to post a copy, perhaps with some dummy data in it?

Reply | Quote

Or, even as a quicker fix, simply reset any references marked as MISSING to the appropriate version specific reference. That should take maybe one minute of your database “professional’s” time. I agree with RetiredGeek that using late binding, when possible, is the preferred long-term solution.

Reply | Quote

While I certainly agree with the general proposition that most aspects of a database developed in earlier versions of Access should work OK with 2010, it is worth being alert to the prospect that some changes might be needed.

A few examples I have encountered.

[*]If you have a Report with “Page Break After Section” Access 2010 will always print a blank page at the end. Earlier versions of Access suppressed the blank page. I have reports that are usually printed one at a time on a single page. 2010 always churns out a second page.
[*]If you change the Record Source of a Report when you open it, and also apply a Filter, Access 2010 ignores the filter.
[*]If an unbound text box has a default value, Access 2010 will not let you set the value to Null.

Some of these might have been fixed with Service Packs or Hot Fixes, but I don’t like to rely on users having applied these.

Reply | Quote

While I certainly agree with the general proposition that most aspects of a database developed in earlier versions of Access should work OK with 2010, it is worth being alert to the prospect that some changes might be needed.

A few examples I have encountered.

[*]If you have a Report with “Page Break After Section” Access 2010 will always print a blank page at the end. Earlier versions of Access suppressed the blank page. I have reports that are usually printed one at a time on a single page. 2010 always churns out a second page.
[*]If you change the Record Source of a Report when you open it, and also apply a Filter, Access 2010 ignores the filter.
[*]If an unbound text box has a default value, Access 2010 will not let you set the value to Null.

Some of these might have been fixed with Service Packs or Hot Fixes, but I don’t like to rely on users having applied these.

I’m still using Access 2003 myself. What can I say….I’m just not in love with the Ribbon (or the Navigation “Pain”). The first issue you mentioned, with the report page break, should not be a show stopper that requires an extensive re-write, as the original poster quoted. Disappointing? Sure.

I guess I’d never encounter the 2nd issue, since I do not filter open reports. I do my filtering at the recordset level, to minimize network traffic. This approach follows the golden rule: request only the data you need. I have heard of the third issue, but I think it may be corrected with a service pack.

I think these days we pretty much *have* to rely on users having the latest service packs installed, or make darn sure you test throughly with an earlier version if there are one or more service packs available. At my place of work, I ran into a TON of errors with one particular Access application, used by hundreds of users, when a few people did a voluntary upgrade to Office 2007 (including Access 2007). This was with the original RTM version of Access 2007, as well as with SP-1. It was not until SP-2 was made available that many of these errors were fixed. As an MVP, I personally reported many of these errors directly to the Access PMs at Microsoft, and they were always able to replicate them with the samples I provided. It seems easy enough to use code to check for the existance of a service pack at startup. I have since implemented this type of code in the application at work.

Snapshot to .pdf issue:
I have used Stephen Leban’s code for personal projects, but I haven’t had any luck getting his download made available on an internal download site, at the Fortune 100 company that I work at. As a result, I have not been able to implement his methods for work-related applications.

I just recently used the Win2PDF utility, for an Access 2003 database at a company that my sister runs. I had heard good comments about this product, for several years, from another Access MVP. The opportunity presented itself in this application, as I needed something that I could program with VBA code to automate some tasks. Yes, I did have some learning issues, but once I worked through these, the result was beautiful. She is now able to produce 30 customer invoices, in .pdf format, at the click of a button. It takes approx. 90 seconds to write these 30 files, which she then emails to her customers. This compares with the process she showed me, which was taking her over 4 hours start to finish each month to produce 30 invoices.

Reply | Quote

I’m still using Access 2003 myself. What can I say….I’m just not in love with the Ribbon (or the Navigation “Pain”). The first issue you mentioned, with the report page break, should not be a show stopper that requires an extensive re-write, as the original poster quoted. Disappointing? Sure.

None of these things are show stoppers. They are just examples of the fact that sometimes things that work in one version cause problems (big or small) in other versions.
I prefer 2003 myself, but I have clients who ask “Does this wok with 2010?” So I force myself to use it.

Reply | Quote

None of these things are show stoppers. They are just examples of the fact that sometimes things that work in one version cause problems (big or small) in other versions.
I prefer 2003 myself, but I have clients who ask “Does this work with 2010?” So I force myself to use it.

I agree that none of these issues are show stoppers. I’m really curious about the advice that the OP received from his consultant. From what I’ve heard, 2010 is a vast improvement over 2007, but, I have to qualify this statement because I haven’t used it myself.

Reply | Quote

I’m really curious about the advice that the OP received from his consultant.

Tom,

[RANT ON]
Over the course of my career I’ve seen this type of advice from consultants and co-workers. It is usually 1 of 2 cases.
1. It actually would be a massive re-engeneering job.
2. What they are really saying is…”I don’t know this new version and I’m not about to learn it because I’m comfortable with what I know and don’t want to expend the effort to learn something new.”

Unfortunately, far too often number 2 is the real reason!

[RANT OFF]
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

John,

Thanks, I’ll keep an eye out for those situations. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

As John pointed out there as logs of issues, I would say bugs, in Access 2010.

In 2003 there as not built in support to save a report as a PDF. If your code save a report in snapshot format hen it will NOT work in properly in Access 2010.

I have found that I have a lot less issues with Access 2010 if I use the older .mdb (Jet4) format and not the new .accdb format.

So far I find Access 2010, or should it be called Access ME :o: , just to buggy for production use. (Reminds me a lot of Windows ME) For all my clients that have Office 2010, I have then use the Access 2007 runtime to run my apps. This solves all the Access ME:o: (2010) issues

I myself know very little about MS Access, but I’ll pass along a comment from an expert Access user. A lady in my church does Access programming for a living, and wrote the Access programs we use for our church management databases. We recently moved up from Office 2003 to 2010. She said Access databases written for 2003 will not run under 2010 and must be extensively rewritten to do so.

While it should not be true, I do find it to to be partial true. You have to write/do of workarounds to get past the issues in Access 2010 that worked fine in all other previous versions.

Reply | Quote

If your code save a report in snapshot format then it will NOT work in properly in Access 2010.

Have you actually tried this? I have not had any problems with it. I know they have removed the snapshot viewer, and don’t mention snapshot format, but they have not actually removed it.
I still use the Lebans Report to PDF in some databases built in older versions, and that works fine in 2010. It first creates a snp, then converts that to pdf.

Reply | Quote

Using a .accdb format with Access 2010:
If you save a report as a snapshot then try to open it with the snapshot viewer is gets an error. I have not been able to find a version of the snapshot viewer that will work with a 2010 generated .snp file. I have seen other posts on this same issue on several other forums.

I have not tried Lebans’ Report to PDF with a .accdb.

I do use Lebans’ Report to PDF in an Access 2003 created MDE with Access 2010 and it works just fine.

Reply | Quote

That explains it. The problem is with 2010 and accdb. I always use mdb format, and the snapshots seem OK with them.

Reply | Quote