Set DB = dbengine Item Not Found

[AccessNub]

Hey All,

I have an Excel file that has been working just fine for the past 2 years.

Yesterday it started to debug saying Item Not Found.

I was able to determine the exact line of code that was causing the error, but I haven't been able to find the cure.

Dim DB as DAO.Database
set DB = DBEngine.Workspaces(0).Databases(0)

I have also tried

Dim DB as DAO.Database
set DB = DBEngine(0)(0)

I have the Reference for Microsoft Office 14.0 Access database engine Object


Any ideas? TIA

 

[MarkK]

I don't see how there could be a database in the DBEngine's default workspace in Excel unless you explicitly open one. What database would be there by default?

To open a DAO.Database in Excel, check out the DAO.DBEngine.OpenDatabase or the DAO.Workspace.OpenDatabase methods.

 

[AccessNub]

A little more info:

I am using excel to open a recordset, the recordsource is 2 tables in 2 different databases with an Inner Join. I do this by specifying the whole path of the database in the table selection, ex:

String = "SELECT * FROM [;DATABASE=Path\Database.accdb].tablename INNER JOIN [;DATABASE=OtherPath\OtherDatabase.accdb].othertablename ON tablename!ID = othertablename!ID" (might be off on the syntax).

Then I want to open the recordset and I was using:

set rst = DBengine(0)(0).OpenRecordset(String, dbreadonly)

This was working fine for 2 years, it just stopped suddenly.

 

[MarkK]

Right. So you could do...

dim dbs as dao.database
dim rst as dao.recordset

set dbs = dbengine.workspaces(0).opendatabase("Path\Database.accdb")
set rst = dbs.openrecordset( _
   "SELECT * FROM tablename INNER JOIN " & _
   "[;DATABASE=OtherPath\OtherDatabase.accdb].othertablename ON tablename!ID = othertablename!ID"

So you use "Path\Database.accdb" to open one database, and then you don't need it in the SQL, because it is the default, but leave the "[;DATABASE=OtherPath\OtherDatabase.accdb]" in the SQL, since that is the remote database.

Makes sense? That's what I'd try next. Cheers,

 

[AccessNub]

That sounds like a good place to start. I will give it a whirl on Sunday.

Thanks for the idea.