Field Mapping tool for queries

[temp]

Hi,

My boss is asking me to generate a column mapping report of all the
queries. Basically, we get our data from ORACLE. There's a queary that
create new table from ORACLE tables. Then, there are reports and
queries that uses the new table.

Is there an add in or tool that can generate mapping reports of the
queries.

Example:
========
Query 1:
Field1 --> from access.field1
Field2 --> from access.field2

make table query from oracle table to new table:
access.field1 --> from oracle_tbl.fiel d1
access.field2 --> from oracle_tbl.fiel d2

Better if it can map it straight to oracle source:
Field1 --> from oracle_tbl.fiel d1
Field2 --> from oracle_tbl.fiel d2

Thanks in advance!
Ross

[temp]

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

[Matthias Klaey]

On 10 Dec 2004 15:40:58 -0800, te**@i-vibe.com wrote:

Bas!

Thank you so much, you're really good!

Well, just one last thing. My goal will be completed if somebofy here
has a sub-routine that parses a SQL string to get the tables and
fields used. I know this is easy as I can do this in Java, but it would
be great if someone has a logic in VB. For example:
Query1:
TABLE1.field 1
TABLE1.field 2
and so on....

Thanks!

In the DumpQueries procedure, use someting like

Print #nFile, vbTab & fld.SourceTable & "." fld.Name & _
" " & fld.SourceField

Please look up the online help for other properties of the Field
object in the Fields collection of the QueryDef object, you might find
other interesting things.

HTH
Matthias Kläy
--
www.kcc.ch

[temp]

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

[Matthias Klaey]

On 11 Dec 2004 11:32:33 -0800, te**@i-vibe.com wrote:

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

You do use linked tables, or do you use passtrough queries?
Maybe a concrete example could help here.

Greetings
Matthias Kläy
--
www.kcc.ch

[Matthias Klaey]

On 11 Dec 2004 11:32:33 -0800, te**@i-vibe.com wrote:

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

Ok, let's do an example:

I'm linking table "dbo.Custom er" from the Northwind database (sample
database on SQL Server) to my MDB, and rename the table as
"tblCustome r". I *cannot* rename the fields in the linked table.

I'm settting up the followeing query, named "qryCustome r":

SELECT tblCustomers.Cu stomerID, tblCustomers.Co mpanyName,
tblCustomers.Ad dress, tblCustomers.Ci ty,
"Phone " & [Phone] & ", Fax " & [Fax] AS Comm,
"Phone " & [Phone] AS Comm2
FROM tblCustomers;

I run the following code:

Sub DumpCust()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field
Dim td As DAO.TableDef

Set db = CurrentDb
Set qd = db.QueryDefs("q ryCustomer")
Debug.Print qd.Name

For Each fld In qd.Fields
Set td = db.TableDefs(fl d.SourceTable)
Debug.Print " Query Table.Field: " & fld.SourceTable & "." &
fld.Name
Debug.Print " Source Table.Field: " & td.SourceTableN ame & "." &
fld.SourceField
Debug.Print
Next
qd.Close
Set qd = Nothing
db.Close
Set db = Nothing

End Sub

I get the result

qryCustomer
Query Table.Field: tblCustomers.Cu stomerID
Source Table.Field: dbo.Customers.C ustomerID

Query Table.Field: tblCustomers.Co mpanyName
Source Table.Field: dbo.Customers.C ompanyName

Query Table.Field: tblCustomers.Ad dress
Source Table.Field: dbo.Customers.A ddress

Query Table.Field: tblCustomers.Ci ty
Source Table.Field: dbo.Customers.C ity

Query Table.Field: tblCustomers.Co mm
Source Table.Field: dbo.Customers.

Query Table.Field: tblCustomers.Co mm2
Source Table.Field: dbo.Customers.P hone

Thus I get all the information about the source table and fields, with
one exception: The query field "Comm" relates to two different fields,
and this is not covered. You would need to parse the field expresssion
only in this case.

HTH
Matthias Kläy
--
www.kcc.ch

[Matthias Klaey]

On 11 Dec 2004 11:32:33 -0800, te**@i-vibe.com wrote:

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

You do use linked tables, or do you use passtrough queries?
Maybe a concrete example could help here.

Greetings
Matthias Kläy
--
www.kcc.ch

[Matthias Klaey]

On 11 Dec 2004 11:32:33 -0800, te**@i-vibe.com wrote:

Hi Matthias,

Thanks. But, I've done that. It does not print the fields coming from
external sources. We have lots of queries that actually just replicate
tables from Oracle. And using your suggestion does not print the field
names of the query. It was just empty.

ross

Ok, let's do an example:

I'm linking table "dbo.Custom er" from the Northwind database (sample
database on SQL Server) to my MDB, and rename the table as
"tblCustome r". I *cannot* rename the fields in the linked table.

I'm settting up the followeing query, named "qryCustome r":

SELECT tblCustomers.Cu stomerID, tblCustomers.Co mpanyName,
tblCustomers.Ad dress, tblCustomers.Ci ty,
"Phone " & [Phone] & ", Fax " & [Fax] AS Comm,
"Phone " & [Phone] AS Comm2
FROM tblCustomers;

I run the following code:

Sub DumpCust()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field
Dim td As DAO.TableDef

Set db = CurrentDb
Set qd = db.QueryDefs("q ryCustomer")
Debug.Print qd.Name

For Each fld In qd.Fields
Set td = db.TableDefs(fl d.SourceTable)
Debug.Print " Query Table.Field: " & fld.SourceTable & "." &
fld.Name
Debug.Print " Source Table.Field: " & td.SourceTableN ame & "." &
fld.SourceField
Debug.Print
Next
qd.Close
Set qd = Nothing
db.Close
Set db = Nothing

End Sub

I get the result

qryCustomer
Query Table.Field: tblCustomers.Cu stomerID
Source Table.Field: dbo.Customers.C ustomerID

Query Table.Field: tblCustomers.Co mpanyName
Source Table.Field: dbo.Customers.C ompanyName

Query Table.Field: tblCustomers.Ad dress
Source Table.Field: dbo.Customers.A ddress

Query Table.Field: tblCustomers.Ci ty
Source Table.Field: dbo.Customers.C ity

Query Table.Field: tblCustomers.Co mm
Source Table.Field: dbo.Customers.

Query Table.Field: tblCustomers.Co mm2
Source Table.Field: dbo.Customers.P hone

Thus I get all the information about the source table and fields, with
one exception: The query field "Comm" relates to two different fields,
and this is not covered. You would need to parse the field expresssion
only in this case.

HTH
Matthias Kläy
--
www.kcc.ch