Named Range Not In DAO Tabledefs Collection

Hi folks,

I'm creating a DAO database in an excel spreadsheet to use some SQL for data manipulation.

I've got some code that creates a named ranges for different sets of data I then create a DB object and use SQL to manipulate the data in the named range.

I use this method several times in my code and it works fine except for one instance where I get a "Run-time 3011 The Microsoft Jet database engine could not find the object 'Name of the Namedrange' "

I've checked the tabledefs collection and my worksheets and other named ranges are in it. I've also checked the names collection of the workbook and all the named ranges are in that.

I've tried using the refresh method of the tabledefs collection and created an entirely new db object after adding the named range but the named range still doesnt show up in the tabledefs collection. Anyone have any ideas?

Cross Posted

All sorted now go to where it was cross posted to see the whole solution.

The brief description of the fix is instead of creating a new db object just close the previous object and then reopen.