spaces in tables/columns

[GazzaG]

how do i update a database which has spaces in tables and columns and change them without spaces, would i need to write some code because if i just change them its going to effect queries and reports isn't it? I must mention that everything is working ok, but I have been told that spaces are a no no!

by the way i inherited this database from a colleague who has now left the job.

thanks for any help

 

[BNPMike]

Why do you think spaces are a problem?

If you mean in the names then spaces introduce complications but it's no big issue.

 

[JonFer]

Spaces in the field and table names make it more difficult to do maintenance but if you aren't making frequent changes then you can probably leave it alone.

If you want to make field/table name changes, the SQL below will tell you what tables/queries are used by each query in the database (at least for Access 97 and probably 2000). You can use it to find which queries will be affected by your change. You'll still have to check modules, macros, and forms - references to fields can be in many places.

SELECT o.Name AS Query, q.Name1 AS

, q.Attribute, q.Expression
FROM MSysQueries AS q INNER JOIN MSysObjects AS o ON q.ObjectId = o.Id
WHERE (((q.Attribute)=5));

 

[BNPMike]

It's a shame actually that a while back some application systems had data dictionaries. IDMS for example has everything about everything in its dictionary and you can query it just like any user data.

DB2 has a 'catalogue' which is just a set of tables covering tables, indexes etc etc, that can be queried with SQL. I don't know why Microsoft decided to hide half this type of information in arcane objects.

 

[JeremyNYC]

Get yourself a copy of Rick Fisher's Find-n-Replace (rickworld.com). It will do all of the changes for you, in all of your objects. It's cheap, and it's really good.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.