?
Solved

Identify table or query that has been aliased in ACCESS 2003 query

Posted on 2011-10-12
4
Medium Priority
?
301 Views
Last Modified: 2012-05-12
In an ACCESS 2003 database, in a query, for sources that have alias, is there a way to determine what the original object's name was?  I have dozens of queries (in an inherited database) and all the sources are alias names.  I tried properties, but that does not give the needed information.  The previous programmer built queries based on queries and tables based on tables and I have to try to figure out what is going on.
0
Comment
Question by:ssmith94015
  • 2
4 Comments
 

Author Comment

by:ssmith94015
ID: 36958707
Ok, I actually figured it out, but is there a way to list out all alias and what the real name of the table/query is?  He also aliased every column in a table so when you bring up the Datasheet view, you dont know what the field name is for the query referring to in the datasheet as the presentation name is different from the field name in the table properties.
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 36960962
go into sql view of the query....

you'll see the origins and the aliases side by side...

copy the sql into excel, move it around a bit, and use text to columns to seperate on comma's, then on spaces, and with a bit of messing around, you can get a column of source and a column of aliases from the query.

if you turn on hidden / system tables, y9ou might be able to use MsysQuery table to export all the queries sql.
0
 

Author Closing Comment

by:ssmith94015
ID: 36961859
Yes, it will get messy, but seems about the only way.  I was hoping there was a "magical" way, but it is going to be work.  Thank you for the Excel suggestion.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36964669
>>   The previous programmer built queries based on queries and tables based on tables and I have to try to figure out what is going on.  <<
That is always a challenge!  I have created attached a utility to this post (the original version I posted publicly can be found here.  the utility is described in detail there).  The attached is an updated version that I have been testing.  The premise of the utility is this --- you enter in a string of text (like the name of a Query object), then you identify which Access objects you wish to search for that string in (Tables, Queries, Forms, Reports, Macros, Modules).  The utility will search the definition of all the objects in the categories you selected for the string you specified.

So ... if you have a Table name of "tblMyTable" and you want to see which Query objects refer to that table, you would enter "tblMyTable", then select only the Query object category.  The utility will then list all the Query objects that have the string pattern of "tblMyTable" in ANY property of the Query object (including the SQL statement).

So, if your SQL statement for a Query object named "selMyQuery" is ...

SELECT * FROM tblMyTable As vTbl

The utility will list the Query object "selMyQuery", the property the string pattern was found in, and if that property is the SQL property, the character position where the string pattern was found is indicated.

>> He also aliased every column in a table so when you bring up the Datasheet view, you dont know what the field name is for the query referring to in the datasheet as the presentation name is different from the field name in the table properties. <<

I hate it when well meaning folks modify the .Caption property of a field in a Table object or Query object.  I especially hate it on Query objects because the datasheet view gives the impression the column is aliased, but its not -- its the .Caption property. When I have ran into this issue, I have used the following code to blank out the caption property.  The .Caption property, by default, is not part of the .Properties collection of a field, however, if the property is filled out, Access will create it for you.  The reason I mention that is because the does not really 'clear' the .Caption property, I remove the property if one exists.  Rest assured that Access will create it again if you, or anyone else, decides to add a value to the Caption property in Design view of either a Table or Query object.

Public Sub CleanCaption()
'Deletes the .Caption property on TableDefs and QueryDefs
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    
    On Error Resume Next
    For Each tdf In db.TableDefs
        If Not (tdf.Attributes And dbSystemObject = dbSystemObject) Then
            For Each fld In tdf.Fields
                fld.Properties.Delete "Caption"
                If Not (Err.Number = 0 Or Err.Number = 3265) Then
                    MsgBox Err.Number & "." & Err.Description
                Else
                    Err.Clear
                End If
            Next fld
        End If
    Next tdf
    
    For Each qdf In db.QueryDefs
        For Each fld In qdf.Fields
            fld.Properties.Delete "Caption"
            If Not (Err.Number = 0 Or Err.Number = 3265) Then
                MsgBox Err.Number & "." & Err.Description
            Else
                Err.Clear
            End If
        Next fld
    Next qdf
    
End Sub

Open in new window


I hope this information and utility helps you out!
SearchForText-2000-to-2010.zip
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question