finding same fields in access tables

[Guest]

Ok, a request, plain english replies with a minium of code, thank you.

Now, I have a linked mdb to a SQL server application which has around 150
tables.

I need to find out which tables contain a particular field, in this case
'Custodian'.

Once that's done I then need to edit some data so all records in that field
are the same.

Now, from basics, Do a use a query or a form?

Once I've identifed these tables, how can I change a value simply &
efficently?

I am a new user & am getting frustrated with complicated answers to this.
Please help, thank you

 

[Douglas J. Steele]

Sorry, but what you're asking for IS a complicated thing to do.

First, there's no simple way to determine all the tables that contain a
particular field. You really have no choice but to loop through all of the
fields in all of the tables, and get a list of those tables that contain the
field in question. You can do this looping using DAO or ADOX, or you can
create a recordset that returns the column information using the ADO
OpenSchema method and work with that recordset. There's nothing simpler. In
all three cases, you'd be using VBA code. I suppose that could mean you're
using a form, but my preference would be a stand-alone function that returns
either an array of table names, or a delimited string of table names.

Once you've built a list of tables that contain that field, then what you
have to do to standardize them depends on what the existing values are, and
what you want them to be.

You really haven't given enough information for me to give you more specific
answers, I'm afraid.

 

[Brendan Reynolds]

It won't be possible to answer these questions in plain English until
someone develops a computer (and an operating system and applications to run
on it) that understands plain English. For whatever it may be worth, though,
as these are SQL Server tables, if you have access (no pun, etc) to the SQL
Server and permission to create views in the SQL Server database, you could
do it with a SQL Server view that queries the SQL Server system tables
sysobjects (which has the table names along with the names of all other
objects in the database) and syscolumns (which has the column names).
Something like ...

SELECT dbo.sysobjects.name
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id =
dbo.sysobjects.id
WHERE (dbo.syscolumns.name = 'FamilyName') AND (dbo.sysobjects.xtype =
'U')

In the above example, 'FamilyName' is the name of the column I'm looking
for. 'U' specifies user tables, eliminating system tables and other types of
object. See SQL Server Books Online for more information about these system
tables.

If you are using SQL Server 2005, I believe it has some pre-defined views on
the system tables, and that Microsoft's recommendation is to use those views
and not to use the tables directly. But I'm not familiar enough with SQL
Server 2005 yet to comment further on that. If you decide to use this
method, you might want to consider posting the question in a SQL Server
newsgroup.

 

[Guest]

Gentlemen,

Thank you for your forthright replies. COmpared with screeds of code that
other people have sent me, it was refreshing to have some clear direction.

Brendan, I have followed your suggestion & started an intial thread on the
SQL forum; Once we negiotate the myriad of permissions (something I have
asked for!), then I agree that it will probably be more straightforward with
the actual tables rather than another intermediatory.

So again, thanbk you for your time, Cheers now, Rob.