Save Dynamic Query (1 Viewer)

[Tallica]

I have some code that builds an SQL string from data on a form. I now want to save this data as a query and open another form based on it.

What is the code to save the string sSQL which is a select statement as a query? I have searched ans searched and can not find it.

Thanks
Rob

Here is my code that builds the query

Dim rcd as String
rcd = Me.Target_Projects_t_num

Dim sSQL As String 'query string
sSQL = "SELECT [Target_Projects Query].[Target_Projects_Project #], [Target_Projects Query].Location, [Target_Projects Query].DPM, [Target_Projects Query].Target_Projects.t_num, [Contacts Extended].[Contact Name], [Contacts Extended].Company, [Contacts Extended].Address, [Contacts Extended].City, [Contacts Extended].[State/Province], [Contacts Extended].[ZIP/Postal Code]"

sSQL = sSQL + " FROM [Target_Projects Query] LEFT JOIN [Contacts Extended] ON [Target_Projects Query].DPM = [Contacts Extended].[Contact Name] WHERE ((([Target_Projects Query].Target_Projects.t_num)='" & rcd & "'));"

 

[boblarson]

You can use QueryDefs for this:

[COLOR=blue][B]Dim qdf As DAO.QueryDef[/B][/COLOR]
[COLOR=#ff0000]Dim rcd as String[/COLOR]
 
[COLOR=#ff0000]rcd = Me.Target_Projects_t_num[/COLOR]
 
[COLOR=red]Dim sSQL As String 'query string[/COLOR]
[COLOR=red]sSQL = "SELECT [Target_Projects Query].[Target_Projects_Project #], [Target_Projects Query].Location, [Target_Projects Query].DPM, [Target_Projects Query].Target_Projects.t_num, [Contacts Extended].[Contact Name], [Contacts Extended].Company, [Contacts Extended].Address, [Contacts Extended].City, [Contacts Extended].[State/Province], [Contacts Extended].[ZIP/Postal Code]"[/COLOR]
 
[COLOR=red]sSQL = sSQL + " FROM [Target_Projects Query] LEFT JOIN [Contacts Extended] ON [Target_Projects Query].DPM = [Contacts Extended].[Contact Name] WHERE ((([Target_Projects Query].Target_Projects.t_num)='" & rcd & "'));"[/COLOR]
 
[COLOR=blue][B]Set qdf = CurrentDb.CreateQueryDef("NameOfQueryToUse", strSQL)[/B][/COLOR]

[COLOR=#0000ff][B]qdf.Close[/B][/COLOR]
[COLOR=#0000ff][B]Set qdf = Nothing[/B][/COLOR]

 

[boblarson]

Forgot to mention that if you already have a query that you want to modify which the form is bound to you can just use:

Dim qdf As DAO.QueryDef
Dim strSQL As String
 
strSQL = "Select * From Employees" ' just an example
 
Set qdf = CurrentDb.QueryDefs("NameOfExistingQueryHere")
 
qdf.SQL = strSQL
 
qdf.Close
 
Set qdf = Nothing

 

[ChrisO]

Rob.

Minor point, depending on how we create databases, but the following line: -

CurrentDb.QueryDefs("NameOfExistingQueryHere").SQL = strSQL

does not require a reference to DAO.

Chris.

 

[Tallica]

Thanks to all. That will help me out alot. The last programming I have done is in VB6.0, but I am able recreate almost everthing so far in access.

Cheers,

 

[namliam]

I would suggest to keep your SQL (more) readable...

sSQL = ""
sSQL = sSQL & " SELECT ... "
sSQL = sSQL & " ,      other column(s) "
sSQL = sSQL & " ,      more column(s) "
sSQL = sSQL & " FROM       [this table] "
sSQL = sSQL & " INNER JOIN [that table] "
sSQL = sSQL & " WHERE .... "

**Insert rant about using naming convention and NOT using spaces and special characters**