Understanding the constraints of a WITH statement

[Rob Wills]

This one line of untidy code works....

CurrentDb.TableDefs("TableName").Fields.Append
CurrentDb.TableDefs("TableName").CreateField("New_Field", dbDouble)

=======================

However this doesn't....

With CurrentDb.TableDefs("TableName")
.Fields.Append .CreateField("New_Field", dbDouble)
End With

I have now found another way to get the code to work which I'll show below,
but I want to understand why the with statement is giving me an error saying
that the object is invalid or no longer set...

=======================
Dim DB as database
Dim tbldef as tabledef
dim fld as Field

set db = currentdb
set tblDef as db.tabledef("TableName")
set fld = tblDef.CreateField("New_Field",dbDouble)

tblDef.fields.append fld

================
Thanks in Advance
Rob

 

[Alex Dybenko]

Hi,
yes, you have to declare database and tabledef variable

Dim DB as database
Dim tbldef as tabledef
dim fld as Field

set db = currentdb
set tblDef as db.tabledef("TableName")


With tblDef
set fld = .CreateField("New_Field",dbDouble)
.fields.append fld
End With


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi Rob,

I have now found another way to get the code to work which I'll show below,
but I want to understand why the with statement is giving me an error saying
that the object is invalid or no longer set...

Normally CurrentDb is returns a new instance (indeed a shallow copy) of
your database. There some problems with that. The normal way is two use
a proxy function instead of it (placed in a normal module):

Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb

End Property

The problem with your code is, that I would assume the first one to fail
and the second one to succeed...

mfG
--> stefan <--

 

[Rob Wills]

Hi Alex/Stephan,

Thanks for your responses - however I don't think you've answered my
question which I've copied again below

Why not?

 

[Douglas J. Steele]

I believe that their answer that it was due to using CurrentDb is correct.

 

[Rob Wills]

I believe that their answer that it was due to using CurrentDb is correct.

yet this does work .... ?!?!

CurrentDb.TableDefs("TableName").Fields.Append
CurrentDb.TableDefs("TableName").CreateField("New_Field", dbDouble)

I feel like I'm missing something here

 

[Douglas J. Steele]

I believe it's because you're doing the operations one at a time in one
case, but trying to do them concurrently in the other. Since CurrentDb
returns a separate instance each time, the concurrent approach doesn't work.

 

[Rob Wills]

no that's actually one line of code - but this window isn't wide enough to
show it as one line....

so if you created a table "a" in access the following line of code will add
a field "b" into your table even from the immediate window

currentdb.tabledefs("a").fields.append
currentdb.tabledef("a").createfield("b",dbdouble)


There's obviously some constraint on the code engine that won't allow this
to work in a "With", but I'd like to understand why.... is it a standard
feature in VBA or is this a one off anomally?

 

[Alex Dybenko]

as you call currentdb 2 times you - get 2 different objects. "With" - does
not create objects, it is just a way of writing

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Rob Wills]

as you call currentdb 2 times you - get 2 different objects. "With" - does
not create objects, it is just a way of writing

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

So what you're suggesting is that the first example where I write out
currentdb twice calls two seperate instances of the currentdb - which is why
this works...

However when using the with statement I'm attempting to call the same
instance of currentdb which is why this doesn't work?

 

[Albert D. Kallal]

This one line of untidy code works....

CurrentDb.TableDefs("TableName").Fields.Append
CurrentDb.TableDefs("TableName").CreateField("New_Field", dbDouble)

However this doesn't....

With CurrentDb.TableDefs("TableName")
.Fields.Append .CreateField("New_Field", dbDouble)
End With

That is interesting (and a bit surprising).

I suspect the instance of currentdb on the one line is "fixed" by the
compiler and resolves to the SAME reference.

Note the following does work:

With CurrentDb
.TableDefs("altry").Fields.Append
..TableDefs("altry").CreateField("cc", dbDouble)
End With


Further, the following also works:

set db = currentdb

With db.TableDefs("altry")
.Fields.Append .CreateField("New_Field", dbDouble)
End With

I have a feeling that the code is using a separate instance of currentdb
(each use is creating a separate instance....we need to FORCE it to one
instance.

However, you 1st example does work:

CurrentDb.TableDefs("altry").Fields.Append
CurrentDb.TableDefs("altry").CreateField("cd", dbDouble)

Now, the above *IS* surprising, since I used currentDb twice, and it still
works.

I think since we cannot control the scope of the db reference, we need to
declare our own instance of the db object.

By declaring our own instance, we can avoid any decisions as to scope that
ms-access might choose for us.

So, you original syntax WILL work if you don't use the temporary instance of
currentdb, but declare one...

It would seem to be "safe", when you use TWO separate functions, we can't
leave the db references to chance.

I *am* however surprised that your 1st example of currentDB does work. I am
guessing that compiler optimization sees both names...and converts them into
one reference (since they are on the same line).

I suspect the compiler does that kind of optimizing when you use of any
variable. If that var is used twice on the SAME line is it safe to resolve
to one address in memory.