finding same fields in access tables

[Guest]

Hi , I have a linked Access db from a SQL server db that contains some 150
tables. I need to find out :

A. which tables contain the same, specific field.
B. Then change some values in all these tables to a single constant.

Should I be using a query or something else? When I've tried to design a
query, I've need to know which tables wthis field exists on, hence the 'going
round in circles ' feeling!

Many thanks, Rob.

 

[Pieter Wijnen]

Finding a Field by Name

Function FindTables(FldName As String) As String
Dim Db As DAO.Database
Dim TDef As DAO.TableDef
Dim Fld As DAO.Field
Dim Ret As String
On Error Resume Next
Set Db = CurrentDb
For Each TDef In Db.TableDefs ' All Errors are ignored, so don't bother with
exclusions
Set Fld = Nothing
Set Fld = TDef.Fields(FldName)
If Not Fld Is Nothing Then
Ret = Ret & "," & Tdef.Name
End If
Next
Set Fld = Nothing
Set Db = Nothing
FindTables = Mid(Ret,2)
End Function

HTH

Pieter

PS seems that your db is pretty denormalized!

 

[Guest]

So Pieter, judging by the quick reply, it's quite simple.
Just two things,
I don't understand (what to do with) the code
the Db in question belongs to something called Recorder 6. It's anything but
normal

Thanks now, Rob.

 

[Pieter Wijnen]

Just went of air after that reply
Paste it into a new module & save
You can either view the results in the debugger
(ctrl-G)
?FindTables() {Enter}


or use it as the controlsource for a TextBox , or .. or

Pieter

 

[Guest]

Hi again Pieter,
ok, I did as you said:

"> Paste it into a new module & save"

Then tried your next instruction, got lost in code, & went back to what is
now a normal state-going round in circles of code.

This bit here makes no sense to me either:

You can either view the results in the debugger
(ctrl-G)
?FindTables() {Enter}


or use it as the controlsource for a TextBox , or .. or

I'm sorry, but I just don't understand how to implement this.

In plain English, I want to ask the mdb this question :

" Which tables contain this field? ie 'CUSTODIAN' "

Then once it tells me this, I will do a simple 'find & replace'.

That's it. Maybe there's an idiot-proof guide to coding, but I thought it
may be a simple solution out there,

Thanks now, Rob.

 

[Pieter Wijnen]

Ok, My mistake

Press Ctrl + G to bring up the debugger
?Findtables("Custodian")
should return a list of all tables containing the Field "CUSTODIAN" (Access
is not case-sensitive)
?Findtables("cUstoDian")
will produce the same

Pieter

 

[Guest]

Hi again,

Ok, so I pasted ?Findtables("Custodian")
into the bottom window 'Immediate', pressed return & got an error: Compile
error:
Sub or Function not defined

As you've probably gathered, I've no idea what I'm doing with Visual Basic.
Is there no query or something very simple I could use instead?