appAccess.currentdb() -- Interface not registered

[Magrathea]

I have the following Excel VBA code snip that has worked well for several
months on a weekly basis.



Dim db1 As DAO.Database
Dim appAccess As Access.Application 'uses the TransferSpreadsheet method
of DoCmd.
......
Set appAccess = CreateObject("Access.application")
appAccess.OpenCurrentDatabase strFPName, False
Set db1 = appAccess.CurrentDb()

Recently it broke. The error message is:
-2147221163 Automation Error
Interface Not Registered.

The error happens at the Set db1. the currentdb() function is not
recognized.
AppAccess.OpenCurrentDatabase works because I can follow it successfully
with a Msgbox appAccess.CurrentProject.Name.

We tried reinstalling Office. No change.
It is using Access and Excel version 9.0.
Running on Windows 2000 SP4.

Tools>References>
has Excel 9.0, Access 9.0, DA0 3.60, OLE Automation. checked. Nothing
missing.

Any particular DLL's we should try to re-register?

I'm going to code around it using an Access table linked to an excel range
and run append queries. But I'd much rather fix what broke. Or at least
know what to fix the next time.

-Stephen Rasey
Houston

 

[NickHK]

Magrathea,
Could be a problem with DAO, possibly re-install MDAC and/or JET drivers
depending on versions.

NickHK

 

[Magrathea]

More info on the appAccess.currentdb() error.

This works in an Access 9 Module
'References: Microsoft DAO 3.6
Dim db1 as DAO.Database
set db1 = currentdb

But
appAccess.currentdb does not work from Excel.

We haven't fixed that, but we found a workaround.

Set appAccess = CreateObject("Access.application")
appAccess.OpenCurrentDatabase strFPName, False
'Set db1 = appAccess.CurrentDb() 'old statement that broke
Set db1 = DAO.OpenDatabase(strFPName)

Now the database is open through Access and again throught DAO.
There are two connections open to the same database.
It gives all the functionality I need without the need to repair the
computer in a game of blind-man's bluff.

-Magrathea
Houston

 

[NickHK]

Your original code ("Set db1 = appAccess.CurrentDb()") work for me. That's
why I said it sound like a registration/registry problem because of the
nature of your error.

Access 2002
DAO 3.6
W2K
Don't which know version of MDAC installed.

NickHK

 

[Magrathea]

Yes, I know it is a registry problem.

Interestingly, however, currentdb working inside Access worked, but using
from Excel the currentdb method of the Access Application object did not.
Obviously, some registry is screwed up. Don't know how (it might have been
a windows update, might not) and I don't know how to fix it. I posted my
fix of using two connection to the database as an alternative.

-Magrathea