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/For...9-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.