TableDefs Problem with Upgrade.

[J.J. Lenlo]

I want to count the number of fields in the Bank Deposits record. It did not
work on the upgrade. I have been guessing at code for 8 days so this is what
I have at the moment.

Dim tblName As String
Dim fldName As Field
Dim rst As Recordset
Dim tdf As TableDef
Dim db As Database
Dim fldCnt As Byte


Set db = CurrentDb

With db
fldCnt = 0
For Each tdf In .TableDefs
If Left(tdf.Name, 11) = "AlphaErrors" Then
tblName = Left(tdf.Name, 11)
fldCnt = db.TableDefs!tblName.Fields.Count '
Error on this line!
MsgBox "Found Errors Table"
End If
Next tdf
End With

Any help appreciated.

J.J.

Confuson leads to guessing. Guessing is like playing Russian Roulette.
Rutherford B. Hayes 23 President of the US

 

[Wayne Morgan]

You upgraded from what to what??

If you upgraded from Access 97, make sure that you have a Reference set for
DAO. In the code editor, go to Tools|References. There should be a check
mark next to Microsoft DAO 3.6 Object Library. If there isn't, scroll down
the list and check it. If it has an older version listed, uncheck the older
version then scroll down the list and check the newer one. Click Ok to
close.

Next, since there are now two similar References (ADO and DAO), disambiguate
your DIM statements. Access will use the References in the order in which
they are listed in the References windows (hence the up/down arrows in that
window). However, instead of worrying about that, it is simpler and more
reliable just to specify which you want.

Example:
Dim tblName As String
Dim fldName As DAO.Field
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim fldCnt As Byte

If you choose to use ADO, you would DIM as ADODB.Recordset. Since both have
a Recordset object, this is the one that usually causes the problem, but if
you specify on all DAO objects, it makes it easier to read and assures the
removal of the problem.

 

[Graham Mandeno]

Hi J.J.

Why are you looking at only the first 11 characters of the table name? That
suggests to me that the actual table name has something appended to it, like
AlphaErrors123.

The way you've written it, tblName will *always* be "AlphaErrors".

I suspect what you need is:

fldCnt = tdf.Fields.Count

 

[J.J. Lenlo]

Wayne;
I added the DAO 3.6 Reference some time ago. It is still there.
I made all the Dimensions exactly as you indicated.

Exact same error. Error # 3265, Item not found in this
collection. I know that the field name is in the Table.

I inserted a MsgBox and it found the right Table.

I feel like I never wrote a program in my life.

Thanks for the assistance.

J.J.

 

[Dirk Goldgar]

I want to count the number of fields in the Bank Deposits record. It
did not work on the upgrade. I have been guessing at code for 8 days
so this is what I have at the moment.

Dim tblName As String
Dim fldName As Field
Dim rst As Recordset
Dim tdf As TableDef
Dim db As Database
Dim fldCnt As Byte


Set db = CurrentDb

With db
fldCnt = 0
For Each tdf In .TableDefs
If Left(tdf.Name, 11) = "AlphaErrors" Then
tblName = Left(tdf.Name, 11)
fldCnt = db.TableDefs!tblName.Fields.Count
' Error on this line!
MsgBox "Found Errors Table"
End If
Next tdf
End With

Any help appreciated.

This line:

fldCnt = db.TableDefs!tblName.Fields.Count

could never have worked, before or after any upgrade. You need this:

fldCnt = db.TableDefs(tblName).Fields.Count

Otherwise, the literal string "tblName" will be interpreted as the name
of the table.

 

[J.J. Lenlo]

Dirk, Graham and Wayne;

That is correct. The credit union sends us a Table with the exact same
format. It starts with AlphaErrors and has the date tied to the end of the
Table.

They added a field that has really complicated matters and the next
time it happens we will know.

The fldCnt = tdf.Fields.Count is what I needed. I do not know what it
was I originally changed but it works now.

Thanks a lot guys, hope I can return the favor.

J.J.