Reading table and field names in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to read the name of a table or read the fields in tables? I am
using Access 2007 (but I also have Access 2003).

I have a database with 700 tables each containing many fields. The tables
may get added or deleted and likewise fields. I want to input a field name
and get the program to search through all the tables. If the field is found
it would then tell me which table it was found in. What specific instructions
can I use to read table and field names, or is it not possible ?

Any advice or ideas gratefully received.

"LodeMan"
 
You can get a list of all of the tables using

SELECT [Name]
FROM MSysObjects
WHERE [Name] Not Like "MSys*"
AND Type In (1,4,6)

but unfortunately the field names aren't contained in any of the system
tables.

The following DAO code will give both to you:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()

For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr,Name
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing
 
Thank you Douglas! Problem solved. I was getting there but you have saved me
a lot of time. This should enable me to write a program which will speed up
part of my work considerably.

In case anyone else uses this, there is a typo - a comma (,) - in the first
Debug.Print statement that should be a dot (.)

Many thanks.
--
"LodeMan"


Douglas J. Steele said:
You can get a list of all of the tables using

SELECT [Name]
FROM MSysObjects
WHERE [Name] Not Like "MSys*"
AND Type In (1,4,6)

but unfortunately the field names aren't contained in any of the system
tables.

The following DAO code will give both to you:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()

For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr,Name
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LodeMan said:
Is it possible to read the name of a table or read the fields in tables? I
am
using Access 2007 (but I also have Access 2003).

I have a database with 700 tables each containing many fields. The tables
may get added or deleted and likewise fields. I want to input a field name
and get the program to search through all the tables. If the field is
found
it would then tell me which table it was found in. What specific
instructions
can I use to read table and field names, or is it not possible ?

Any advice or ideas gratefully received.

"LodeMan"
Click to expand...
Click to expand...
 
That was to see whether you were paying attention. <g>

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LodeMan said:
Thank you Douglas! Problem solved. I was getting there but you have saved
me
a lot of time. This should enable me to write a program which will speed
up
part of my work considerably.

In case anyone else uses this, there is a typo - a comma (,) - in the
first
Debug.Print statement that should be a dot (.)

Many thanks.
--
"LodeMan"


Douglas J. Steele said:
You can get a list of all of the tables using

SELECT [Name]
FROM MSysObjects
WHERE [Name] Not Like "MSys*"
AND Type In (1,4,6)

but unfortunately the field names aren't contained in any of the system
tables.

The following DAO code will give both to you:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()

For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr,Name
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LodeMan said:
Is it possible to read the name of a table or read the fields in
tables? I
am
using Access 2007 (but I also have Access 2003).

I have a database with 700 tables each containing many fields. The
tables
may get added or deleted and likewise fields. I want to input a field
name
and get the program to search through all the tables. If the field is
found
it would then tell me which table it was found in. What specific
instructions
can I use to read table and field names, or is it not possible ?

Any advice or ideas gratefully received.

"LodeMan"
Click to expand...
Click to expand...
Click to expand...
 
Hi There

I have captured a field name and want to use it to update the contents of
the field but keep getting a "not in collection" error. I have stored the
captured field name as strField and want to read the field content then
update it after a bit of fettling. Here's my code:

Do Until .EOF ' repeats process until all records are worked through


strCode = Nz(![strField], "") 'put contents of field into variable
strCodeFix = Replace(strCode, " ", "") ' creates a copy of first
variable without the spaces
strCodeFix = Replace(strCodeFix, ",", "")
strCodeFix = Replace(strCodeFix, "-", "")
strCodeFix = Replace(strCodeFix, ".", "")
strCodeFix = Replace(strCodeFix, "_", "")
strCodeFix = Replace(strCodeFix, "/", "")
strCodeFix = Replace(strCodeFix, "#", "")

Debug.Print strCodeFix



Let ![strField] = strCodeFix ' updates the contents of the field with
the new variable
'

.MoveNext
Loop ' back to the Do statement

If I type the field name in the nz statement it works.......Any ideas?

Cheers
Kyle
 
You can't use a variable to refer to a field in that way.

Rather than ![strField], try .Fields(strField)

BTW, was there any reason why you posted your question as part of another
thread, rather than starting a new thread?
 
Hi Douglas

Spot on! Thanks, that's been bugging me for a couple of days.

I added to this thread as my problem followed on (I've got the field names -
how do I use them constructively?) and it was a current, live thread so I
hoped my message would be spotted. Sorry if this is not the way it should be
done.

Cheers
Kyle
 
KyleGorf said:
I added to this thread as my problem followed on (I've got the field
names -
how do I use them constructively?) and it was a current, live thread so I
hoped my message would be spotted. Sorry if this is not the way it should
be
done.
Click to expand...

It's always better to start a new thread. For one thing, many of the people
who answer questions will avoid threads where there's been activity,
preferring to concentrate on unanswered questions.
 
Back
Top