Index number not working properly with variable

[Ronald R. Dodge, Jr.]

OS: Windows XP, SP2
Access 2002, SP3
MDAC 2.8
DAO 3.60

Using DAO Coding

I have a code within a class module and the variable used for the index
number with the TableDefs doesn't work like it should.

Dim l_lngCurrentTableDefCount As Long
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_objDAORecordSets = New clsDAORecordSets
For l_lngCurrentTableDefCount = 0 To CurrentDb.TableDefs.Count - 1 Step
1
m_objDAORecordSets.Add
CurrentDb.TableDefs(l_lngCurrentTableDefCount).Name
Next l_lngCurrentTableDefCount

l_lngCurrentTableDefCount = 3

CurrentDb.TableDefs(l_lngCurrentTableDefCount).Name

The above line returns "". The Watch Window shows the above Table Def
Object as invalid.

The line below returns the actual name of the table. The Watch Window shows
the below Table Def Object as valid.

CurrentDb.TableDefs(3).Name

What's going on? Do I need to change the data type or just why would this
happen?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

OS: Windows XP, SP2
Access 2002, SP3
MDAC 2.8
DAO 3.60

Using DAO Coding

I have a code within a class module and the variable used for the index
number with the TableDefs doesn't work like it should.

Dim l_lngCurrentTableDefCount As Long
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_objDAORecordSets = New clsDAORecordSets
For l_lngCurrentTableDefCount = 0 To CurrentDb.TableDefs.Count - 1 Step
1
m_objDAORecordSets.Add
CurrentDb.TableDefs(l_lngCurrentTableDefCount).Name
Next l_lngCurrentTableDefCount

l_lngCurrentTableDefCount = 3

CurrentDb.TableDefs(l_lngCurrentTableDefCount).Name

The above line returns "". The Watch Window shows the above Table Def
Object as invalid.

The line below returns the actual name of the table. The Watch Window shows
the below Table Def Object as valid.

CurrentDb.TableDefs(3).Name

What's going on? Do I need to change the data type or just why would this
happen?

It doesn't seem that it should be different, but your class
might be doing something strange.

A major issue that you really need to straighten out is your
use of CurrentDb. As the sage Lyle Fairfield likes to point
out, the expression:
CurrenDb Is CurrentDb
is **never** true!

CurrentDb is a **function** that constructs a new dababase
object with all system collections refreshed every time it
is called. If your class is adding/removing an object
without refreshing the object's collection (e,g, TableDefs,
QueryDefs, etc), then CurrentDb and the database object with
the executing code can be different.

Bottom line on all that is that you should normally set a
database object variable at the top of your procedure and
use the variable thoughout the procedure. A lot of folks
use DbEngine(0)(0) instead of CurrentDb(), but there are
some fairly esoteric circumstances where the 0 index member
in the Databases collection is different from the database
object with the executing code so it is not reliable in all
situations.

 

[Ronald R. Dodge, Jr.]

Thank you for that, though not sure if that would have resolved the issue or
not. However, I also took that into consideration. Prior to knowing that,
I had redid the code to the following, which did work:

Dim l_lngCurrentTableDefCount As Long, tdf As DAO.TableDef
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_objDAORecordSets = New clsDAORecordSets
For Each tdf In CurrentDb.TableDefs
m_objDAORecordSets.Add tdf.Name
Next

After you telling me that, I have changed it yet to the following:

Dim l_lngCurrentTableDefCount As Long, tdf As DAO.TableDef
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_ddbCurrent = DBEngine(0)(0)
Set m_objDAORecordSets = New clsDAORecordSets
For Each tdf In m_ddbCurrent.TableDefs
If tdf.SourceTableName = "" Then
m_objDAORecordSets.Add tdf.Name, m_ddbCurrent, DAO.dbOpenTable
Else
m_objDAORecordSets.Add tdf.Name, m_ddbCurrent, DAO.dbOpenDynaset
End If
Next


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

Thank you for that, though not sure if that would have resolved the issue or
not. However, I also took that into consideration. Prior to knowing that,
I had redid the code to the following, which did work:

Dim l_lngCurrentTableDefCount As Long, tdf As DAO.TableDef
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_objDAORecordSets = New clsDAORecordSets
For Each tdf In CurrentDb.TableDefs
m_objDAORecordSets.Add tdf.Name
Next

After you telling me that, I have changed it yet to the following:

Dim l_lngCurrentTableDefCount As Long, tdf As DAO.TableDef
Set m_dwsCurrent = DBEngine.Workspaces(0)
Set m_ddbCurrent = DBEngine(0)(0)
Set m_objDAORecordSets = New clsDAORecordSets
For Each tdf In m_ddbCurrent.TableDefs
If tdf.SourceTableName = "" Then
m_objDAORecordSets.Add tdf.Name, m_ddbCurrent, DAO.dbOpenTable
Else
m_objDAORecordSets.Add tdf.Name, m_ddbCurrent, DAO.dbOpenDynaset
End If
Next

I don't understand exactly what you did to get it to work,
but at least it is working.

That must be an interesting class. It seems to be a
roundabout way to mimic OpenRecordset and the Recordsets
collection.

Note that the "standard" way to check for a linked or local
table is:
If Len(tdf.Connect) > 0 Then

 

[Ronald R. Dodge, Jr.]

The class itself is one that I created myself to address some of the issues
I ran into within DAO coding. It's basically to modulate code so as I only
have to call on the codes within the various forms rather than having to
create codes within the forms themselves.

One of the reasons being, as much as I would like to use bound forms and
controls, the requirements to use such and the requirements of my users
along with my own set of requirements don't quite allow for the use bound
forms. In all actuality, it's not so much my own set of requirements that's
keeping me from using bound forms/controls, but rather with the fact that
I'm having to model to how JDE works to keep the training involved to a
minimal. One of the aspects of it doesn't allow for the use of bound
forms/controls.

ADO also doesn't allow for persistent connection against a Jet Engine
database, so can't really use it either in environments requiring such
connections. Therefore, the only way to use ADO within MS Access would be
to model more or less the ADO.NET coding in a disconnected environment,
though much more limited than ADO.NET set of codes.

Given the above situation, that only leaves DAO coding left even though it's
basically dead in the water as far as any improvements to it is concerned.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

The class itself is one that I created myself to address some of the issues
I ran into within DAO coding. It's basically to modulate code so as I only
have to call on the codes within the various forms rather than having to
create codes within the forms themselves.

One of the reasons being, as much as I would like to use bound forms and
controls, the requirements to use such and the requirements of my users
along with my own set of requirements don't quite allow for the use bound
forms. In all actuality, it's not so much my own set of requirements that's
keeping me from using bound forms/controls, but rather with the fact that
I'm having to model to how JDE works to keep the training involved to a
minimal. One of the aspects of it doesn't allow for the use of bound
forms/controls.

ADO also doesn't allow for persistent connection against a Jet Engine
database, so can't really use it either in environments requiring such
connections. Therefore, the only way to use ADO within MS Access would be
to model more or less the ADO.NET coding in a disconnected environment,
though much more limited than ADO.NET set of codes.

Given the above situation, that only leaves DAO coding left even though it's
basically dead in the water as far as any improvements to it is concerned.

I won't try to second guess your use of bound forms.

DAO is NOT dead in the water. A2007 made major changes in
relation to the new ACE file format and several new
features. ADO is the data access library that has had the
bare minimum of updates over the last several versions.

 

[Tony Toews [MVP]]

Given the above situation, that only leaves DAO coding left even though it's
basically dead in the water as far as any improvements to it is concerned.

What? Umm, the Access team has created their own version of the DAO
dlls called ACE. And they've made a bunch of improvements to it.
(Although some will debate the wisdom of those improvements such as
multi valued fields. <smile>)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ronald R. Dodge, Jr.]

Guess I stand corrected about the improvement aspect of DAO coding.

As for the bound forms, one of the problems with bound forms, it requires
knowing which edit mode one will be going into prior to even starting on the
form. Generally, a user will know that, but that is not *ALWAYS* the case.
As with anything I do, unless I have no realistic choice, I make every
attempt to avoid absolutes given absolutes gets one into trouble so much
easier than most other things.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

Guess I stand corrected about the improvement aspect of DAO coding.

As for the bound forms, one of the problems with bound forms, it requires
knowing which edit mode one will be going into prior to even starting on the
form. Generally, a user will know that, but that is not *ALWAYS* the case.
As with anything I do, unless I have no realistic choice, I make every
attempt to avoid absolutes given absolutes gets one into trouble so much
easier than most other things.

What do you mean by "edit mode"? If it's the options that
can be specified in the OpenForm method's DataMode argument,
then those same options can be set in the form itself using
the AllowEdits, AllowAdditions and AllowDeletions
properties. If those are too heavy handed for what you are
doing, then you can also use the Locked property on
individual controls.

IME, I have only found one case where I had to use unbound
data controls (instead of a subform) and that was because I
needed to allow users to specify aggregate values that code
divided across multiple records in a child table.

 

[Ronald R. Dodge, Jr.]

The problem with bound forms is the fact that one has to know rather if they
are in Add, Edit, or Inquire mode (Inquire mode though not so bad given you
can use an unbound control for the ID field to use for inquiring purposes)
before putting any data into the form. A pretty good majority of the time,
this wouldn't be an issue, but there are those rare exceptions. As with any
of my other programming work, not only am I after accurate data on a timely
basis, but also after user friendliness, especially for the group of users
that I have to satisfy. With the various things I have learned along the
way, I attempt to implement things that helps all 3 major groups of people
involved with the database (Administrators/DB Programmers, Data Entry
personnel, and Data Users), which I have been in all 3 arenas, as each group
has their own set of issues. In the past, I would have separated DB
programmers from administrators, but learned from on here that many others
prefer to have those 2 disciplines grouped together, given the programmer
has to take into account of the different things that the administrator
would need to have in place to do their job, and how much of an overlap
there is.

One such case, how do you handle a situation when a user calls up one record
to be able to spin off from that record to create a new record with minor
changes. Just by inquiring, the program itself wouldn't know if the user
intends on editing that record (if allowed to), or using that data to create
a new record (if allowed to). For this sort of reason, I and many other
users prefer to have the edit mode choice to be *AFTER* dealing with the
data rather than having the program guess which mode to be in *PRIOR TO*
dealing with the data as a requirement of Access with bound forms. That's
also why the forms that are not switchboards are setup to have 1 status
label and 6 standardized command buttons (Inquire, Add, Edit/Update, Delete,
Reset, Exit) in the footer section of the form.

Yes, I realize working with unbound forms/controls requires more coding and
work, but the flexibility is also greater. That's just one reason why I
standardize certain things.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

The problem with bound forms is the fact that one has to know rather if they
are in Add, Edit, or Inquire mode (Inquire mode though not so bad given you
can use an unbound control for the ID field to use for inquiring purposes)
before putting any data into the form. A pretty good majority of the time,
this wouldn't be an issue, but there are those rare exceptions. As with any
of my other programming work, not only am I after accurate data on a timely
basis, but also after user friendliness, especially for the group of users
that I have to satisfy. With the various things I have learned along the
way, I attempt to implement things that helps all 3 major groups of people
involved with the database (Administrators/DB Programmers, Data Entry
personnel, and Data Users), which I have been in all 3 arenas, as each group
has their own set of issues. In the past, I would have separated DB
programmers from administrators, but learned from on here that many others
prefer to have those 2 disciplines grouped together, given the programmer
has to take into account of the different things that the administrator
would need to have in place to do their job, and how much of an overlap
there is.

One such case, how do you handle a situation when a user calls up one record
to be able to spin off from that record to create a new record with minor
changes. Just by inquiring, the program itself wouldn't know if the user
intends on editing that record (if allowed to), or using that data to create
a new record (if allowed to). For this sort of reason, I and many other
users prefer to have the edit mode choice to be *AFTER* dealing with the
data rather than having the program guess which mode to be in *PRIOR TO*
dealing with the data as a requirement of Access with bound forms. That's
also why the forms that are not switchboards are setup to have 1 status
label and 6 standardized command buttons (Inquire, Add, Edit/Update, Delete,
Reset, Exit) in the footer section of the form.

Yes, I realize working with unbound forms/controls requires more coding and
work, but the flexibility is also greater. That's just one reason why I
standardize certain things.

You seem to have a fixed opinion on this topic and even if I
would do it differently, I do not want to waste any time
trying to convince you otherwise. It's your program so do
whatever you want.

 

[Ronald R. Dodge, Jr.]

It's not that I'm closed minded as if we expect to improve, we must be open
minded. It's just like when I needed to have Access to emulate the
CausesValidation property and Validate event of VB6, which I developed a
such version within Access after several others told me that I couldn't have
both (User friendliness and strict data checks as the form is being filled
out) or that it wouldn't work in Access given how it's error checking stuff
works. The initial problem I had was to get that to work with bound forms,
but then Albert had pointed something out to me that I had completely
overlooked and after taking that into consideration along with what I had
required, I was able to find a way to get that set of code to work with
bound forms. It was just a simple case that I was too deep into the forest
and not looking at it from the overall view in that case. By doing that, I
have proven such statements by others to be false. However, it was the
constructive manner that Albert took that really helped me out, so as to get
the best of both worlds.

So if there is another point of view that I'm not seeing it from or there's
some small item that's innocently being overlooked like the case Abert
helped me out with, then maybe that is all that is needed, for that to be
brought to light to be able to move on and utilize the bound forms once
again.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Marshall Barton]

It's not that I'm closed minded as if we expect to improve, we must be open
minded. It's just like when I needed to have Access to emulate the
CausesValidation property and Validate event of VB6, which I developed a
such version within Access after several others told me that I couldn't have
both (User friendliness and strict data checks as the form is being filled
out) or that it wouldn't work in Access given how it's error checking stuff
works. The initial problem I had was to get that to work with bound forms,
but then Albert had pointed something out to me that I had completely
overlooked and after taking that into consideration along with what I had
required, I was able to find a way to get that set of code to work with
bound forms. It was just a simple case that I was too deep into the forest
and not looking at it from the overall view in that case. By doing that, I
have proven such statements by others to be false. However, it was the
constructive manner that Albert took that really helped me out, so as to get
the best of both worlds.

So if there is another point of view that I'm not seeing it from or there's
some small item that's innocently being overlooked like the case Abert
helped me out with, then maybe that is all that is needed, for that to be
brought to light to be able to move on and utilize the bound forms once
again.

I don't think I have found your specific question amongst
your exposition on unbound forms. The only thing that I see
that resembles a question is something about how do you use
a bound form when you don't know what a user intends to do
when the form opens. This seems to be more than a little
philosphical when phrased in such general terms. I did
respond that you can use several properties to limit what
users can do until you determine their intentions. It
seemed to me that you responded with another exposition on
the problems of bound forms so I don't understand what you
are after in this discussion.

 

[Tony Toews [MVP]]

The problem with bound forms is the fact that one has to know rather if they
are in Add, Edit, or Inquire mode (Inquire mode though not so bad given you
can use an unbound control for the ID field to use for inquiring purposes)
before putting any data into the form.

<shrug> I have no idea why you would care on this issue. That said I
have, on some apps, introduced a Lock command button which the user
has to click on, before they are allowed to do any changes.

Otherwise what does it matter.

One such case, how do you handle a situation when a user calls up one record
to be able to spin off from that record to create a new record with minor
changes.

Add a button which copies the record then positions them at the new
record. However I've never had to do this.

Yes, I realize working with unbound forms/controls requires more coding and
work, but the flexibility is also greater.

It's seldom I work with unbound forms. Mostly because I like working
with Access's powerful "limitations".

tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/