469,273 Members | 1,726 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

Problem referring to existing tabledef object

TheSmileyCoder
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. With CurrentDb.TableDefs("hist_tbl_Obs")
  2.   Debug.Print .Name & ":" & .SourceTableName
  3. End With
But I get the error:
Object is invalid or no longer set. (Error 3420)
The same happens for the code:
Expand|Select|Wrap|Line Numbers
  1. Dim T as dao.tabledef
  2. set T=CurrentDb.TableDefs("hist_tbl_Obs")
  3.   Debug.Print T.Name & ":" & T.SourceTableName
  4.  


However if I use:
Expand|Select|Wrap|Line Numbers
  1. Dim t As TableDef
  2. For Each t In CurrentDb.TableDefs
  3.   If t.Name = "hist_tbl_Obs" Then
  4.     Debug.Print t.Name & ":" & t.SourceTableName
  5.   End If
  6. 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.
Jan 4 '12 #1

✓ answered by 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

26 15710
MikeTheBike
637 Expert 512MB
Hi Smiley

It would seem you need to do this
Expand|Select|Wrap|Line Numbers
  1.     Dim T As DAO.TableDef
  2.     Set T = CurrentDb.CreateTableDef("hist_tbl_Obs")
  3.     MsgBox T.Name & ":" & T.SourceTableName
??

MTB
Jan 4 '12 #2
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Jan 4 '12 #3
MikeTheBike
637 Expert 512MB
Hi Again

Mabe this then
Expand|Select|Wrap|Line Numbers
  1.     With CurrentDb.QueryDefs("hist_tbl_Obs")
  2.         MsgBox .Name & "  :  " & .SQL
  3.     End With
??

MTB
Jan 4 '12 #4
Mariostg
332 100+
Expand|Select|Wrap|Line Numbers
  1. Dim T As DAO.TableDef
  2. Dim dbs As Database
  3. Set dbs = CurrentDb
  4. Set T = dbs.TableDefs("hist_tbl_Obs")
  5. Debug.Print T.Name & ":" & T.SourceTableName
  6.  
Jan 4 '12 #5
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 4 '12 #6
NeoPa
32,172 Expert Mod 16PB
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.
Jan 4 '12 #7
Stewart Ross
2,545 Expert Mod 2GB
@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
Jan 4 '12 #8
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 4 '12 #9
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Jan 4 '12 #10
NeoPa
32,172 Expert Mod 16PB
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).
Jan 5 '12 #11
nsenor
7 Nibble
To use CurrentDb globally consistently in a project, write the following in a module

Declaration section:

Expand|Select|Wrap|Line Numbers
  1. Private m_dbThis As DAO.Database
In the Procedure section:

Expand|Select|Wrap|Line Numbers
  1. Public Property Get ThisDb() As DAO.Database
  2. If m_dbThis Is Nothing Then
    Set m_dbThis = CurrentDb
    End If
    Set ThisDb = m_dbThis
    End Property
4 Weeks Ago #12
NeoPa
32,172 Expert Mod 16PB
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 :
    Expand|Select|Wrap|Line Numbers
    1. Public X As DAO.Database
    2. ...
    3. If X Is Nothing Then Set X = CurrentDb()
    4. 'X is now ready for use.
4 Weeks Ago #13
nsenor
7 Nibble
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.
4 Weeks Ago #14
isladogs
291 Expert 256MB
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
Expand|Select|Wrap|Line Numbers
  1. 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
Attached Images
File Type: jpg Capture.jpg (163.7 KB, 162 views)
4 Weeks Ago #15
nsenor
7 Nibble
You do not have to ever declare any
Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO,Database
  2. and
  3. set db = ThisDb
If you use the ThisDb construct
You just use ThisDB as if CurrentDb was a well formed Object
e.g.
Expand|Select|Wrap|Line Numbers
  1. Set tbl = ThisDb.TableDefs("SomeTable")
  2. Debug.Print tbl.Fields("SomeName"),Value
or
Expand|Select|Wrap|Line Numbers
  1. With ThisDb
  2. ...
  3. someVar = .SomeProperty
  4. ....
  5. End with
and you won't run into any problems. You can do that as many times and in as many places in the same project as you need to. Every time you make a declaration and an assignment, it is a redundant repetition and it tells on the speed

If the construct where to be in a class, unless it is PublicNotCreatable (which is more or less a glorified std module), it has to be instantiated to be used. That (I think) would be too much hassle for just CurrentDb.
4 Weeks Ago #16
NeoPa
32,172 Expert Mod 16PB
Hi people.

That's very interesting. Like IslaDogs I had no idea a property could be set within a Standard Module. This is very interesting in itself.

My own handling of the value of CurrentDb() works very slightly differently, in as much as I have places in my code which are entry points and thus I check and set a whole bunch of variables that the rest of my code expects to be present & correct. From that point forward (Logically rather than positionally.) I use the object variable and know that it's set appropriately. Thus there is no requirement to check the validity of the value each time it's referenced.

Nevertheless, I like the flow of this approach. Whether it's used exclusively or just at those points where it's necessary to check / set upon entry hardly matters.

@IslaDogs.
Your timings are interesting, and worth considering in some circumstances, but my principal interest is in logical and manageable code for portability between developers etc so I have to say I like this approach. Time delays of a millisecond, or even measured in such, are not an issue for me generally. Not unless, as in your tests, they're used so many times that the delay becomes significant.
4 Weeks Ago #17
NeoPa
32,172 Expert Mod 16PB
@NSenor.
We cross-posted there.

I agree you can use it that way - but you aren't constrained to do so if you prefer, as I do, the approach where it's reliably set in an object variable.

NB. Please remember to use the [CODE] tags as you have in previous posts. I have a feeling you may have more suggestions in you so it would be worth saving me the effort of fixing them all afterwards ;-)
4 Weeks Ago #18
nsenor
7 Nibble
Thanks for the editing NeoPa

My reply box is in plain text now for a reason i cannot fathom. I will revert to adding {CODE} tags manually.

Of course, there is no compulsion in the use of the method

The beauty of this Construct is more evident if you were to use a resource like Excel WorksheetFunction in Access frequently as I do. It has a problem that you cannot declare it once as a New object. Every time you need to use it, you have to assign it and invoke Excel Application.

Declaration:
Expand|Select|Wrap|Line Numbers
  1. Public fn_Sht as Excel.WorksheetFunction
  2.  


Use:
Expand|Select|Wrap|Line Numbers
  1. Set fn_Sht = Excel.Application.WorksheetFunction 
  2.  


This calls Excel every time you invoke it. You may reduce resource consumption a little by declaring an Excel Application Object but the master stopper is to declare the WorksheetFunction first as a module level variable and then as a Public Property that exposes the private variable (after checking for Nothing(ness).

Declaration:
Expand|Select|Wrap|Line Numbers
  1. Private m_fn_Sht as WorksheetFunction
  2.  


Use:
Expand|Select|Wrap|Line Numbers
  1. Public Property Get fn_Sht() As Excel.WorksheetFunction
  2.     If m_fn_Sht Is Nothing Then
  3.         Set m_fn_Sht = xlApp.WorksheetFunction
  4.     End If
  5.     Set fn_Sht = m_fn_Sht
  6. End Property
  7.  


The drawdown on resources is very noticeable at 1st use after Reset or Startup. After that, you hardly notice you are working in Excel from Access.

This is what informed my use of it for CurrentDb. Although the stress on resource is not an issue, but the ease of Access and shortness of code is appealing.
4 Weeks Ago #19
isladogs
291 Expert 256MB
@nsenor
Thank you for offering this as an alternate approach but I'm not convinced it has any real advantages so far.
I can see you are very keen on this approach but I've still not seen any benefit compared to the standard approach that I use.
Execution times are similar in each case. Your approach doesn't mean less code.

@NeoPa
I was interested in timing in case this approach was significantly faster or slower.
My point was that the differences were so small as to be irrelevant.
4 Weeks Ago #20
NeoPa
32,172 Expert Mod 16PB
@IslaDogs.
Absolutely. Please don't see my comments as dismissive of your offering. Just how I interpreted the results. It looks very much like we agree on that :-)

@Nsenor.
It's an approach I'll consider using in future certainly, and it's nice to know about it.
4 Weeks Ago #21
nsenor
7 Nibble
@IslaDogs
Maybe my English is not so good to explain what I mean. It's like you know how sweet honey tastes but you have difficulty explaining to someone who is used to sugar. They are both sweet.

Let us assume in a project, you need to make reference to CurrentDb in say 20 procedures in a VBA Project.

EASE OF USE:
Conventionally you will have to write at least 21 lines of code

Declare a Public variable

Expand|Select|Wrap|Line Numbers
  1. Public ThisDb as DAO.Database
  2.  
For the 20 times you need to use CurrentDb you have to write the assignment Statement

Definition:
Expand|Select|Wrap|Line Numbers
  1. Set ThisDb = CurrentDb
  2.  
If you are an Object destructor enthusiast, you will need an additional 20 lines of code for the 20 objects created

Destruction:
Expand|Select|Wrap|Line Numbers
  1. Set ThisDb = Nothing
OR you may wish to create only 1 reference to CurrentDb and use it for the entire session. In this case, you must check to make sure CurrentDb is not Nothing every time you need to use CurrentDb

Expand|Select|Wrap|Line Numbers
  1. If ThisDb is Nothing Then
    Set ThisDb = CurrentDb
    End If
  2.  
So at the minimum you need 21 lines of code for reference to CurrentDb in 20 procedures. In an ideal situation, you may need up to 91 lines.

If you use a Public Property (which is actually an embellished variable definition) to call a module level variable, you will only need 7 lines of code even for 1000 references to CurrentDb in a VBA Project.

Declaration:
Expand|Select|Wrap|Line Numbers
  1. Private m_ThisDb as DAO.Database
  2.  


Expand|Select|Wrap|Line Numbers
  1. Public Property Get ThisDb() As DAO.Database
    If m_dbThis Is Nothing Then
    Set m_dbThis = CurrentDb
    End If
  2. Set ThisDb = m_dbThisEnd Property
  3.  
Just 7 lines of code and you are done with CurrentDb problems.
No more declarations, no more definition.
ThisDb has become a self-defining, self-checking variable.
It will self-destruct because it is actually a reference to a private variable.

PERFORMANCE:
If you do the conventional method of declaring and defining

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. Set db=ThisDb
  3.  
every time you need a reference to CurrentDb, you will not get the performance (or even the ease of use) advantage.

That one declaration is enough
That one definition is enough

You just use ThisDb as a variable. The efficiency is superb.
4 Weeks Ago #22
isladogs
291 Expert 256MB
@nsenor
I appreciate your attempts to try & explain why you believe this is so efficient.
However, did you read my initial reply in post #15?

If so, you will see that your code is no faster than using Set db=CurrentDb once for the entire session.
In my opinion, there is absolutely no reason to check that value each time it is used.
I set the value at the start of the session and don't destroy it as there is no benefit in doing so.
That means I just use db.Execute each time it is needed.

I'm not denigrating your code. It works perfectly and is about as fast as using CurrentDb

However - sorry but I don't see your code as any more efficient than the approach I use.
After repeated tests, it is no faster (the differences are negligible).
It also does not use less code.

My approach needs just 2 lines :
Expand|Select|Wrap|Line Numbers
  1. Public db As DAO.Database
  2. Set db = CurrentDb
Some years ago, I got into a similar discussion with an MVP who had claimed back in 2009 that using DBEngine(0)(0) was up to 5000x faster than using CurrentDb. See https://www.experts-exchange.com/art...ternative.html.
He continued to repeat the same comments on numerous occasions over subsequent years despite evidence to the contrary.
He was similarly adamant that his recommended approach was the best.
In his case, it was easy to prove him wrong. In fact using DbEngine(0)(0) is usually slightly slower than CurrentDb
4 Weeks Ago #23
nsenor
7 Nibble
We learn every day

@Isladog
Please where do you define
Expand|Select|Wrap|Line Numbers
  1. Set db =CurrentDb
  2.  
so that all other procedures in all other modules know that it has been set?
4 Weeks Ago #24
isladogs
291 Expert 256MB
I normally set it in the load event of the startup form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Set db = CurrentDb
  3. End Sub
That value is retained throughout the session.
There is no point clearing it by using Set db=Nothing or the advantages of assigning it in the first place are lost.

That's ALL you need to do ..
So what could possibly go wrong ..and how would any issues be solved?

1. On the rare occasions that I need to start the database using the shift bypass, I can set db by just typing that code line in the immediate window

2. Alternatively, (e.g. if no startup form is used), a process similar to yours could be employed adding a sub(or function) in a standard module:
Expand|Select|Wrap|Line Numbers
  1. Sub SetCurrDb()
  2.     Set db = CurrentDb
  3. End Sub
Then just type, SetCurrDb in the immediate window or assign a keyboard shortcut using an Autokeys macro
If this is a function, then it could of course be run from an autoexec macro instead.

3. If the value of db is lost after a program crash, error 91 occurs. If you really wanted to, that error could be used to reset the value.
However, during development I would prefer to know that there is an issue - so I don't do this.

For the same reason, I wouldn't use a tempvar for this purpose as tempvars usually retain their value after a crash

4. If you need to use an external database for any procedure, just set its value as a different variable when needed

Hope that covers everything
4 Weeks Ago #25
nsenor
7 Nibble
Yes. Thanks @Isladogs.

I guess that covers everything for development that is for private use and crashes can be managed from the backend.
4 Weeks Ago #26
isladogs
291 Expert 256MB
No reason to limit the approach to private use. I have several commercial apps that have used this approach for almost 20 years without problems.
Due to thorough testing during development, program crashes are almost non-existent anyway.

Sorry but I didn't understand your reference to handling crashes from the BE.
4 Weeks Ago #27

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Rainman | last post: by
1 post views Thread by Roger (Bordeaux) | last post: by
reply views Thread by Robert Bossy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.