Problem referring to existing tabledef object

[TheSmileyCoder]

Hi all my favorite Access elves, and happy NewYear.

Im trying to modify a tabledef by code, and I seem to be running into some trouble.
I am still in the early phases of getting an understanding on how to do it, and im allready stumped at simply refering to the tableDef.

I have tried this:

With CurrentDb.TableDefs("hist_tbl_Obs")
  Debug.Print .Name & ":" & .SourceTableName
End With

But I get the error:
Object is invalid or no longer set. (Error 3420)
The same happens for the code:

Dim T as dao.tabledef
set T=CurrentDb.TableDefs("hist_tbl_Obs")
  Debug.Print T.Name & ":" & T.SourceTableName

However if I use:

Dim t As TableDef
For Each t In CurrentDb.TableDefs
  If t.Name = "hist_tbl_Obs" Then
    Debug.Print t.Name & ":" & t.SourceTableName
  End If
Next

It will run just fine, and output the values requested.


Maybe my brain has melted during the holidays, but I simply cannot wrap my head around why the 2 first examples are giving the error, and whether there is a less clumsy way to proceed then the last posted bit of code.

[MikeTheBike]

Hi Smiley

It would seem you need to do this

    Dim T As DAO.TableDef
    Set T = CurrentDb.CreateTableDef("hist_tbl_Obs")
    MsgBox T.Name & ":" & T.SourceTableName

??

MTB

[TheSmileyCoder]

Thank you for your promt reply.

The issue is that I want to (through code) modify the tabledef, in such a way as to convert the existing autonumber field KEY_OBS to a Number, Long, and then create a new autonumber field RowID.

As far as I can see you are creating a new tabledef, not modifying the existing one.

[MikeTheBike]

Hi Again

Mabe this then

    With CurrentDb.QueryDefs("hist_tbl_Obs")
        MsgBox .Name & "  :  " & .SQL
    End With

??

MTB

[Mariostg]

Dim T As DAO.TableDef
Dim dbs As Database
Set dbs = CurrentDb
Set T = dbs.TableDefs("hist_tbl_Obs")
Debug.Print T.Name & ":" & T.SourceTableName

[Stewart Ross]

Mariostg's answer mirrors my own experience with Access tabledefs. I found in one of my projects that using With CurrentDB led to an object not set error, as it did for you Smiley. As per mariostg's answer, setting a database variable to CurrentDB instead then using that variable for the tabledefs resolved the 'object not set' issue.

After experiencing this issue I don't use the CurrentDB object in With or For Each statements at all, as it does not appear to give consistent results when used that way.

-Stewart

[NeoPa]

This is all about the function (Not object but function call) CurrentDb(). The Help System has something to say on this, but in brief it's always a good idea to return the object once and store it for future use. Mario's code does this and will be found to be entirely reliable.

[Stewart Ross]

@NeoPa: I should indeed have been clear that the CurrentDB method of the application object is not an object in itself, but a method (function) of the Application object which 'returns an object variable of type Database that represents the database currently open in the Microsoft Access window' (quote from MS Help for Access).

The shorthand use of 'object' in referring to CurrentDB relates to what it returns - a database object.

I agree entirely that mario's code will be 100% reliable - but my main point is that use of the CurrentDB method without assigning it to an object variable of type database will not be 100% reliable, and gives rise to peculiar object not set errors when accessing properties of objects which are themselves present and correct in every other respect (such as the tabledefs object collection, which Smiley's question relates to).

-Stewart

[Stewart Ross]

This MSDN question thread provides a full explanation of why CurrentDB throws errors when used to access tabledefs etc.

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/7ea9506f-5e91-4896-80b9-6712762388ea

As the first reply makes clear, in terms similar to NeoPa's:

"CurrentDB is *NOT AN OBJECT* - it is a METHOD that returns a DIFFERENT POINTER to the underlying database object for EACH INVOCATION of CurrentDB.

This is why you can't use it like that. If you don't store that particular pointer in a variable, it goes away instantly (as soon as the expression evaluation is completed), and any underlying/child object references go with it."

-Stewart

[TheSmileyCoder]

I was so focused on the TableDef aspect of it, that I never thought to look more closely at the currentDB object. I have used the Currentdb many times before, but in those cases it was usually about adding a tabledef, or doing a Execute. I guess I felt comfortable using it, so I never considered it could be the source of my trouble.


Thank you all for your time and help.

[NeoPa]

Stewart is quite correct and possibly a little kind when he says the explanation he quoted was similar to what I was saying. It's what I should have said. Unfortunately I was quite pressed for time at that point so rushed off a brief explanation and suggested checking the Help System for more details.

Pointing out that the result was a function wasn't the important point - as that was about the non-persistence of the object returned unless assigned to an object immediately. The quoted reply in Stewart's post explains it perfectly. Particularly the second line. The only part I find strange is that using the result in a With section of code doesn't cause the object to persist across that section at least. It appears it does not though, so that's how it needs to be handled (IE. by assigning the object to a variable).

Now I've had time I've checked the Help System for CurrentDb() and it doesn't explain this point at all (so even sending you off there wasn't much help I'm sorry to say) :-( However, I do recall picking this point up some while ago now, and I'm pretty sure it came from somewhere in the Help System (as I find I very rarely have to look elsewhere for Access or VBA based info).

[nsenor]

To use CurrentDb globally consistently in a project, write the following in a module

Declaration section:

Private m_dbThis As DAO.Database

In the Procedure section:

Public Property Get ThisDb() As DAO.Database
[INDENT]If m_dbThis Is Nothing Then[/INDENT]
[INDENT][INDENT]Set m_dbThis = CurrentDb[/INDENT][/INDENT][INDENT]End If[/INDENT][INDENT]Set ThisDb = m_dbThis[/INDENT]
End Property

[NeoPa]

Hi Nsenor. Welcome to Bytes.com.

I can understand where you're coming from, using a class module to handle this, but there are two problems :

1. You only include a part of what would be required in order to use it.
2. There really is no benefit in designing it as a class. It doesn't offer any value over and above the simple code that you illustrate where a DAO.Database object is declared as Private (Public is often preferable here in a Standard Module of course.) and you have a procedure (or often just a repeated snippet of code) that checks if it's set or not and sets it only when not.
   Essentially :
   

   Public X As DAO.Database
   ...
   If X Is Nothing Then Set X = CurrentDb()
   'X is now ready for use.

[nsenor]

It is not in a class module. You write the declaration and the procedure in a standard module.

The advantage is that whenever you need to use CurrentDb, you call ThisDb directly and it will return the same memory location every time without the uncertain behavior of using CurrentDb.

It solves the problem of the original question with ease of use i.e. declare once and use every time, everywhere in the project as a stable CurrentDb clone.

In your construct, if X is not a property and you don't have a private holder for its value, you have to check for Nothing every time you need to use x, whereas the property can do that for you.

[isladogs]

I would also have expected that code to be in a class module but can confirm it works in a standard module.
However, I've done a speed comparison test on both sets of code from posts #12 & #13 adapting my test database CurrentDb vs DBEngine(0)(0)

After testing repeatedly, I can state that there is no significant difference in the times for each method - if anything @nsenor's code was very slightly slower:



However, I do accept the point about checking If X Is Nothing each time. I don't do this in my own databases

Instead I just use this code once

Set db = CurrentDb

Using that approach shaved 0.1 s off the time to 8.74s. Every little helps!

On the relatively rare occasions that need to use a different database than the current db, I use a separate variable for the purpose

If you are interested in the results for all the tests done originally together with the test database, see the link above