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.field1
access.field2 --> from oracle_tbl.field2

Better if it can map it straight to oracle source:
Field1 --> from oracle_tbl.field1
Field2 --> from oracle_tbl.field2

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.field1 --> from oracle_tbl.field1
access.field2 --> from oracle_tbl.field2

Better if it can map it straight to oracle source:
Field1 --> from oracle_tbl.field1
Field2 --> from oracle_tbl.field2

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.*********@heuvelqop.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.com>
wrote:

On Wed, 08 Dec 2004 20:08:20 +0100, Bas Cost Budde
<b.*********@heuvelqop.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

[Albert D. Kallal]

I would assume that if you are doing some mapping, you simply build some
quires..and use alias names..right?

Are you just looking for a way to list out and print all of the queries you
are using in ms-access?

(the documenter will do this for you).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[temp]

Yes, I am doing some mapping. I just like to know what tables and
fields are used in all of the queries. Sadly, i'm not quite familiar
with the Microsoft coding arena. I would really appreciate if someone
has a VB code that I can just cut and paste in Access.

NavQueries that Bas recommended is close. But I want to see the result
for all the queries. I am trying Matthias suggestion but I am getting
"not defined error" in the first part of
....
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field
....
About documenter, I'm getting some error for some of the queries.
thanks.

[temp]

I see that DAO library is not installed in 2000. Does anyone know how
to this with ADO?

[Bas Cost Budde]

Matthias Klaey wrote:

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

Oh yes! I forgot. For some reason that is not in my NavQueries... mmm,
time for an update.
--
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

[PC Datasheet]

Access2000, XP and 2003 all have the DAO library; however they default to
the ADO library. Open a standard module and go to Tools - References.
Uncheck the ADO library and scroll down to Microsoft DAO library and check
it. You will then be able to code in DAO.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<te**@i-vibe.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

I see that DAO library is not installed in 2000. Does anyone know how
to this with ADO?

[temp]

Ok, I managed to get all the queries all at once. My problem now is,
it's telling me that "the text is too long to be edited" in the textbox
in a form. I just want to copy and paste it in excel.

- is there a way to increase the textbox size (cant seem to finde it)
- i was trying to dump it in a file, using the code below but i'm
having some error. I guess it's a library problem again which i don't
know which is
....
Dim oWrite as System.IO.StreamWriter
oWrite = oFile.CreateText("C:\sample.txt")
OpenText
.....

[Bas Cost Budde]

te**@i-vibe.com wrote:

Ok, I managed to get all the queries all at once. My problem now is,
it's telling me that "the text is too long to be edited" in the textbox
in a form.
For all queries? I can imagine that :-)
I just want to copy and paste it in excel.
Umm, I recommend the use of an intermediate file. Much like
Sub DumpQueries()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field
dim nFile as long

Set db = CurrentDb
nFile = FreeFile
Open "c:\sample.txt" For Output As nFile
For Each qd In db.QueryDefs
Print #nFile, qd.Name;'semicolon lets us stay on the same line
For Each fld In qd.Fields
Print #nFile, vbTab & fld.Name;
Next
Print #nFile,""'we want to end the line for this query
Next
Close nFile
Set db = Nothing
End Sub

This will create a tab separated file with the query name at the start
of each line. Seems easy to me to import that into Excel. Have fun!
- is there a way to increase the textbox size (cant seem to finde it)

No; a textbox is limited to -what?- I think 32K
--
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]

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.field1
TABLE1.field2
and so on....

Thanks!

[temp]

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.field1
TABLE1.field2
and so on....

Thanks!

[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.field1
TABLE1.field2
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 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.field1
TABLE1.field2
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.Customer" from the Northwind database (sample
database on SQL Server) to my MDB, and rename the table as
"tblCustomer". I *cannot* rename the fields in the linked table.

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

SELECT tblCustomers.CustomerID, tblCustomers.CompanyName,
tblCustomers.Address, tblCustomers.City,
"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("qryCustomer")
Debug.Print qd.Name

For Each fld In qd.Fields
Set td = db.TableDefs(fld.SourceTable)
Debug.Print " Query Table.Field: " & fld.SourceTable & "." &
fld.Name
Debug.Print " Source Table.Field: " & td.SourceTableName & "." &
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.CustomerID
Source Table.Field: dbo.Customers.CustomerID

Query Table.Field: tblCustomers.CompanyName
Source Table.Field: dbo.Customers.CompanyName

Query Table.Field: tblCustomers.Address
Source Table.Field: dbo.Customers.Address

Query Table.Field: tblCustomers.City
Source Table.Field: dbo.Customers.City

Query Table.Field: tblCustomers.Comm
Source Table.Field: dbo.Customers.

Query Table.Field: tblCustomers.Comm2
Source Table.Field: dbo.Customers.Phone

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.Customer" from the Northwind database (sample
database on SQL Server) to my MDB, and rename the table as
"tblCustomer". I *cannot* rename the fields in the linked table.

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

SELECT tblCustomers.CustomerID, tblCustomers.CompanyName,
tblCustomers.Address, tblCustomers.City,
"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("qryCustomer")
Debug.Print qd.Name

For Each fld In qd.Fields
Set td = db.TableDefs(fld.SourceTable)
Debug.Print " Query Table.Field: " & fld.SourceTable & "." &
fld.Name
Debug.Print " Source Table.Field: " & td.SourceTableName & "." &
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.CustomerID
Source Table.Field: dbo.Customers.CustomerID

Query Table.Field: tblCustomers.CompanyName
Source Table.Field: dbo.Customers.CompanyName

Query Table.Field: tblCustomers.Address
Source Table.Field: dbo.Customers.Address

Query Table.Field: tblCustomers.City
Source Table.Field: dbo.Customers.City

Query Table.Field: tblCustomers.Comm
Source Table.Field: dbo.Customers.

Query Table.Field: tblCustomers.Comm2
Source Table.Field: dbo.Customers.Phone

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