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

[David Schofield]

On 7 Dec 2004 10:24:11 -0800, te**@i-vibe.com wrote:

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.field 1 --> from oracle_tbl.fiel d1
access.field 2 --> 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

Hi
Sorry don't understand whether you are looking for a schema-type map
or whether you need to track the underlying source objects for queries
which have AS clauses.

The DAO properties sourcefield, sourcetable do the latter if query
fields are not expressions (but can give funny answers if they are).
Don't think this functionality is is in ADO.

If you boss likes reports he may be interested in FMS Total Access
analyser which "contains over 300 reports" see www.fmsinc.com

David

[temp]

Thanks for the reply,

I guess I made my statement complicated.
I just want to see all the quey structure and put it on excel to see
which tables and fields are used.

I saw the use of MsysQueries and MSysObjects, but it does not show the
fields that are not marked to show in the query.

The thing is, i'm still waiting for admin access to my station before I
can install the FMS demo. Can FMS TA do what I need?

Temporarily, does anyone knows how to do what I need with a macro or
VBscript or a shareware?

Thanks.
Ross

[temp]

Thanks for the reply,

I guess I made my statement complicated.
I just want to see all the quey structure and put it on excel to see
which tables and fields are used.

I saw the use of MsysQueries and MSysObjects, but it does not show the
fields that are not marked to show in the query.

The thing is, i'm still waiting for admin access to my station before I
can install the FMS demo. Can FMS TA do what I need?

Temporarily, does anyone knows how to do what I need with a macro or
VBscript or a shareware?

Thanks.
Ross

[Bas Cost Budde]

te**@i-vibe.com wrote:

Temporarily, does anyone knows how to do what I need with a macro or
VBscript or a shareware?

Try NavQueries from my site

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[temp]

Thanks for this one. Your form is quite neat. It shows the fields and
the tables.

But it won't generate the report for all of the queries. I have to do a
cut and paste one by one which is what i'm trying to avoid. Our DB has
a lot of queries.

But thanks for your code.

[temp]

By the way, can you tell me how you extracted the exact queries from
NavQueries?

Thanks.
Rosss

[Bas Cost Budde]

te**@i-vibe.com wrote:

By the way, can you tell me how you extracted the exact queries from
NavQueries?

What do you mean?

I question the MSysObjects table for all queries; I wrote my own
routines to analyze the call structure.

I could try to create something that takes the SELECT apart so you get
all columns in a query.

Getting all SQL statements is easy:

sub DumpQueries
dim db as database
dim qd as querydef
set db=currentdb
for each qd in db.querydefs
debug.print qd.sql
next
set db=nothing
end sub

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Matthias Klaey]

On Wed, 08 Dec 2004 20:08:20 +0100, Bas Cost Budde
<b.*********@he uvelqop.nl> wrote:

te**@i-vibe.com wrote:

By the way, can you tell me how you extracted the exact queries from
NavQueries?

What do you mean?

I question the MSysObjects table for all queries; I wrote my own
routines to analyze the call structure.

I could try to create something that takes the SELECT apart so you get
all columns in a query.

Getting all SQL statements is easy:

sub DumpQueries
dim db as database
dim qd as querydef
set db=currentdb
for each qd in db.querydefs
debug.print qd.sql
next
set db=nothing
end sub

It is not neccessary to parse the SQL. QueryDefs do have a Fields
collection:

Sub DumpQueries()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field

Set db = CurrentDb
For Each qd In db.QueryDefs
Debug.Print qd.Name
For Each fld In qd.Fields
Debug.Print " " & fld.Name
Next
Next
Set db = Nothing
End Sub

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

[David Schofield]

On Wed, 08 Dec 2004 20:23:05 +0100, Matthias Klaey <mp**@hotmail.c om>
wrote:

On Wed, 08 Dec 2004 20:08:20 +0100, Bas Cost Budde
<b.*********@h euvelqop.nl> wrote:

te**@i-vibe.com wrote:

By the way, can you tell me how you extracted the exact queries from
NavQueries?

What do you mean?

I question the MSysObjects table for all queries; I wrote my own
routines to analyze the call structure.

I could try to create something that takes the SELECT apart so you get
all columns in a query.

Getting all SQL statements is easy:

sub DumpQueries
dim db as database
dim qd as querydef
set db=currentdb
for each qd in db.querydefs
debug.print qd.sql
next
set db=nothing
end sub

It is not neccessary to parse the SQL. QueryDefs do have a Fields
collection:

Sub DumpQueries()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field

Set db = CurrentDb
For Each qd In db.QueryDefs
Debug.Print qd.Name
For Each fld In qd.Fields
Debug.Print " " & fld.Name
Next
Next
Set db = Nothing
End Sub

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

-and you can add the sourcetable and sourcefield to this. useful when
fields have been renamed, though these properties sometimes name one
field from an expression.
David