Upgrading to Access 2010 from 2003 issues

[Pat]

Hi,


Until I upgraded my laptop I was running Windows XP Service Pack 3 and Office (Access) 2003. I wrote a database for work on my old system that contains a lot of VBA code. I provided the database to a coworker who is running Windows 7 and Office 2010. The program worked perfectly for him out of the gate. I have just received a new laptop with Windows 7 and Office 2010. When I tried running my databases (I have several) many have errors which I assume are all related so I will focus on the one mentioned above.


When I open the database and then click on a control that is supposed to open an Excel spreadsheet and manipulate the spreadsheet data, the spreadsheet opens but no data is updated. When I close the spreadsheet there is an open Access dialog box that states: Run time error '48': Error in loading DLL. I gave an EXACT copy of this database immediately after it failed on my machine and had my coworker try it on his machine. As expected, it worked perfectly. The spreadsheet is embedded in an OLE, therefore, the coworker and I are actually manipulating the exact same spreadsheet when we were both working on the same copy of the database.


If I click on debug in the dialog box it takes me to the following line in my DBA code: Set objXL = GetObject(, "Excel.Application")
I don't want to start changing anything in my code without understanding what is going on since this same database runs perfectly on a machine that is configured with Windows 7 and Access 2010.

I checked the selected references on each machine and they are identical:
* Visual Basic for Applications
* Microsoft Access 14.0 Object Library
* OLE Automation
* Microsoft DAO 3.6 Object Library
* Microsoft AxtiveX Data Objects 2.1 Library
* Microsoft Excel 14.0 Object Library
* Microsoft Office 14.0 Object Library
* Microsoft Visual Basic for Applications Extensibility 5.3

I know that there is something called "late binding" that I believe assists with compatibility issues, however, I'm not versed in this technique and hate to do anything drastic when this database works on a similar environment. Any direction would be greatly appreciated as I'm now paralyzed with so many of my databases acting up.

Thanks,
Pat

[June7]

Is your coworker pc on network? Is your laptop? How can coworker be working on EXACT same spreadsheet if they are working on a copy of the database?

Is this a split db design?

Re Late Binding: http://word.mvps.org/faqs/interdev/e...atebinding.htm

If you want test on another laptop running Windows7/Access2010, follow instructions at bottom of my post.

[Pat]

Yes, we are both on a network. We are using the exact same spreadsheet because it's an embedded OLE, not linked. I tried the database on my laptop, it had the error, I saved it to a jump drive and had him try it on his desktop. I don't know what a split DB is so I have to believe it is not. Thanks

[June7]

I've never used embedded OLE files but if each of you opens an independent copy of db and each edit the spreadsheet, then the spreadsheet will be different in each copy?

If you want me to test it, follow instructions at bottom of my post to provide file.

[Pat]

Hi June,
The instructions are as follows:

1. Open the database
2. Click on “Create Super BOM” (BOM = Bill of Materials)
3. Click on “Export Super BOM”
4. The Excel file will open, but on my system it has data from the previous time it was opened and manipulated. Once I close on minimize Excel I can then see the DLL error message.

I attached a screen shot of what the Excel spreadsheet looks like when opened on my laptop. The data shown in this attachment is incorrect. When I take the database to my co-worked on a jump drive and he runs through these steps the spreadsheet is populated correctly by the current table data and looks much different.

The OLE fields are on the main screen and normally View is set to NO since the user does not need to access any of these. I have set View to YES in case you wanted to open the Excel spreadsheet independently. Thanks, Pat

[Pat]

Hi June,
Any chance you had time to try this out? Thanks

[June7]

Lost track of thread.

Just tried your db twice. No error.

[Pat]

Lost track of thread.

Just tried your db twice. No error.

Hi June,
That's what I thought and hoped would happen since it worked fine on my co-workers computer. On my new laptop I started off with Office 2010 WITHOUT Access plus Office 2003 WITH Access. Some of my databases, including this one, had issues, so I added Access 2010. That didn't help so I removed Office 2003 completely, still no luck. Any last ideas on what I can try? Thanks again.

[rpeare]

Did you check your references to make sure you're running the same SP/DLL's as the one that works? Go to your code editor and compare the references you have vs the other person and see if there's any difference. I've had this problem between 2007 and 2010 machines because of differences in references (dll's)

[June7]

I did not have do anything with references. Just downloaded the db and ran as instructed. The spreadsheet opens (although I get the 'enable macros' popup).

Maybe Access 2003 did not fully uninstall. I ran into this with Access 2007/2010. Found a third party script that would scrub system of vestigial Access references in config files, etc.

Don't know, you might be facing a reinstall of entire system, Windows, Office, everything or you have a bad computer.

[Pat]

Hi June,
Thanks for all the evaluations and help. Do you have the name of the script you use to wipe Access completely clean? Thanks Pat

[June7]

The scripts are named OffScrub07.vbs and OffScrub10.vbs. Review http://blogs.technet.com/b/odsupport...-products.aspx

[Pat]

Hi June,
I've done some more research and I agree, I need to go through ALL my databases and use Late Binding. However, one thing I've discovered is that I might have a corrupt reference. There are several suggestions on how to repair, with the simplest to simply open the database, uncheck all references, close the database and finally re-open the database. I started down this path and now I don't see two of the references that I would think are basic to all versions of Access:
* Microsoft Excel 14.0 Object Library
* Microsoft Office 14.0 Object Library

Should these be available in Access 2010? If so any suggestions on how to "make them visible"? If not, besides late binding what are my choices as I'd think there must be some compatible library’s.

Thanks,
Pat

[June7]

Yes, those libraries are still incorporated in 2010. They should show in the reference list. I have never encountered situation where they are not.

Create a new db file. Do those libraries show?

[Pat]

Hi June,
I tried opening a new database in Access 2010 and still don't see the Excel reference. I've attached a screen shot that shows what I see. It goes from Microsoft Disk.... to Microsoft Fax.... This is very odd. Thanks, Pat