Add-ins to autogenerate a select query with all fields?

[Ivan]

Hello,

I find myself often in position to make an append query from one table to
another. The procedure is simple. Usually I make first an select query then
I check up the suitability of the resulted rowset and after that I change
the select query to the append one.

Often but I have to modify the fields of the origin table before I append
them to the second one and in such cases I can't use at the begining the
simple select query with a wild character * for using all fields. What
disturbs me very much in the query design view is that I must click for each
field of the table to put it in the select query. If the table has hundred
or more fields this takes time and it gives the oportunity for mistakes. I
think that using ADOX I would know how to make an add-ins which autogenerate
the select query which has all table's fields written down in its SQL
statement, but in the lack of time I hope that somebody did that job
already.

So if anybody has such code or an working add-ins for Access 2007 I would be
grateful if he can present it.

Ivan

 

[Dale Fye]

Ivan,

Using DAO, you should be able to use this. I usually just call it in the
immediate window. It will generate the SQL and open the resulting query in
design view.

Public Sub GenSQL(TableName As String, QueryName As String)

Dim strSQL As String, varFL As Variant
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef

'This will get you an empty recordset
strSQL = "SELECT * FROM [" & TableName & "] WHERE False"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'Build the SQL string
varFL = Null
For Each fld In rs.Fields
varFL = (varFL + ", ") & ("[" & fld.Name & "]")
Next fld

rs.Close
Set rs = Nothing

strSQL = "SELECT " & varFL & " FROM [" & TableName & "]"
Set qdf = CurrentDb.CreateQueryDef(QueryName, strSQL)
qdf.Close

DoCmd.OpenQuery QueryName, acViewDesign

End Sub

HTH
Dale

 

[Ivan]

Thank you Dale. The code is exactly the tool I needed it.

Ivan

Ivan,

Using DAO, you should be able to use this. I usually just call it in the
immediate window. It will generate the SQL and open the resulting query
in
design view.

Public Sub GenSQL(TableName As String, QueryName As String)

Dim strSQL As String, varFL As Variant
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef

'This will get you an empty recordset
strSQL = "SELECT * FROM [" & TableName & "] WHERE False"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'Build the SQL string
varFL = Null
For Each fld In rs.Fields
varFL = (varFL + ", ") & ("[" & fld.Name & "]")
Next fld

rs.Close
Set rs = Nothing

strSQL = "SELECT " & varFL & " FROM [" & TableName & "]"
Set qdf = CurrentDb.CreateQueryDef(QueryName, strSQL)
qdf.Close

DoCmd.OpenQuery QueryName, acViewDesign

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Hello,

I find myself often in position to make an append query from one table to
another. The procedure is simple. Usually I make first an select query
then
I check up the suitability of the resulted rowset and after that I
change
the select query to the append one.

Often but I have to modify the fields of the origin table before I append
them to the second one and in such cases I can't use at the begining the
simple select query with a wild character * for using all fields. What
disturbs me very much in the query design view is that I must click for
each
field of the table to put it in the select query. If the table has
hundred
or more fields this takes time and it gives the oportunity for mistakes.
I
think that using ADOX I would know how to make an add-ins which
autogenerate
the select query which has all table's fields written down in its SQL
statement, but in the lack of time I hope that somebody did that job
already.

So if anybody has such code or an working add-ins for Access 2007 I would
be
grateful if he can present it.

Ivan