count the length of each record

[Samuel]

Hi,

Could somebody help me better my code? I want to count the length of each
field in a recordset of more than 1 table.
At the moment i manually change the name of the table in the code so i can
count, but i know that this can be done automatically.

I should loop through the tabledefs where the type is odbc linked table, but
i can't managed it.

Here's the code:

Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim CC As Long
Dim fd As DAO.Field

Set rs = CurrentDb.OpenRecordset("Select * From table 1")<<<<<this
should be done automatically
Set rs1 = CurrentDb.OpenRecordset("Select * From tblLengths")

Do Until rs.EOF
On Error Resume Next
For Each fd In rs.Fields
With rs1
.AddNew
!FieldName = fd.Name
!Fieldvalue = fd.Value
!FieldLength = Len(fd.Value)
.Update
End With
'Debug.Print fd.Name & " : " & Len(fd.Value)
CC = CC + Len(fd.Value)
Next fd
rs.MoveNext
Loop

Set rs = Nothing
Set rs1 = Nothing

 

[Brendan Reynolds]

I think you just need to know how to identify the ODBC-linked tables, right?
You seem to know how to do the rest of it.

Public Sub ListODBC()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set tdfs = db.TableDefs
For Each tdf In tdfs
'The UCase$() is probably redundant, just being careful.
If UCase$(Left$(tdf.Connect, 5)) = "ODBC;" Then
Debug.Print tdf.Name
End If
Next tdf

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tim Ferguson]

Could somebody help me better my code? I want to count the length of
each field in a recordset of more than 1 table.
At the moment i manually change the name of the table in the code so i
can count, but i know that this can be done automatically.

I hate to be picky, but I don't think this code will do what you want; or
else what you want incredibly bizarre. This code will write to a table
one record for every field in every record in the table(s) concerned.
There is no way to know which table or row the field comes from, just its
name! In other words you get a row that looks like:

"Colour", "Red", 3

Of course, the third column is redundant because you know that "Red" has
three letters already. And in any case, if the field is not text, then
the whole thing becomes meaningless.

If you want to know the field size for the columns in the table (for
example, to duplicate the table structure), you don't have to go anywhere
near recordsets: just iterate the .Fields collection of the Tabledef
object. I would guess that the .Type property would also be helpful?
Again, if you really just wanted to duplicate the table structure you
would do an empty maketable command like

SELECT *
INTO NewTable
FROM OldTable
WHERE FALSE

(the WHERE clause makes sure that no records are transferred)

Hope that helps


Tim F