List field names in a query

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi guys

I have a query that I am using to create a list of customers details that I
would like to display on a form without using CBF. I would like to write a
select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
 
Tony

I am not even sure what you want to do is possible. For instance how could
Access tell what field names would be returned from a crosstab query? Here
is a can't fail way of doing what you want using a recordset and iterating
through its fields collection.

Public Sub GetFieldNames(strQueryName)
' Get names of all Columns a query will return
Dim rs As ADODB.Recordset, fld As ADODB.Field

Set rs = New ADODB.Recordset
rs.Open strQueryName, CurrentProject.Connection
For Each fld In rs.Fields
Debug.Print fld.Name
Next
rs.Close
Set rs = Nothing
End Sub


Ron W
 
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));
 
Thanks John

JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
Just checking.
:-)

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
ahh...Code Behind Forms.

The Goulds said:
what is CBF?

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
ummm...my version of these files did not contain all the tables w/in my
database
JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Unfortunately John, that query may return inconsistent values, because
sometimes MSysQueries.Expression contains a criteria expression.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Please show me how - that's what I'm looking for
The Goulds said:
Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers
details that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Tony,

Just change "QueryDef" and "QueryDefs", to "TableDef" and "TableDefs",
respectively.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Please show me how - that's what I'm looking for
The Goulds said:
Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Wow - so lessee, the query forms a list based on an existing query?

Picky, picky, picky! :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers
details that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top