Input a variable to a query

[rfcomm2k]

I have code that copies an existing query to a new query. When I created the base query 'ciscoquerystructure' I used an existing table '0901', but I need to have the "new" queries I create from this code select from a DIFFERENT table. (i.e. 0902, 0903, 0807, etc)
Look:

Dim TableName As String, stSQL As String, CiscoTableName As String, CiscoFileName As String, CiscoQueryName As String

Err.clear

TableName = InputBox("Enter a Table Name for Updating", "Input")
CiscoTableName = TableName & "C"
CiscoQueryName = TableName & "CQ"

' Create append query
DoCmd.SetWarnings False
DoCmd.CopyObject , CiscoQueryName, acQuery, "ciscoquerystructure"

Right here, how can I tell the new query to use the new table as the source for data? i.e. new query named '0903CQ' should select records from table named '0903C'.


DoCmd.SetWarnings True
' Append cisco data to monthly table
DoCmd.SetWarnings False
DoCmd.OpenQuery CiscoQueryName, acViewNormal, acAdd
DoCmd.SetWarnings True

 

[LPurvis]

You can - and it's pretty simple, given certain caveats.
Basically you'll be referring to the table name within the query.
If the potential exists for that string which describes it's name to appear as part of another string in the query definition then that will also get replaced unless you start coding very specifically where to replace the text (which is possible - just more than likely unnecessary).

You could perform the action in a single line of code were you so inclined...

CurrentProject.Connection.Execute "CREATE VIEW [" & CiscoQueryName & "] As " & Replace(Currentdb.QueryDefs("ciscoquerystructure").SQL,"0901", CiscoTableName)

Or perform a more robust, dedicated creation of the new query definition.
Bear in mind that you rarely must create a new query - and that SQL on the fly isn't a bad solution in many instances. (Or just changing the definition of an existing query that exists purely to be changed based upon the core query's definition).

Considering the SQL syntax parsing that Access performs and potential reformatting - I'd be tempted to merely store the text of the query SQL in a table and refer to that as the core source.

Cheers.

 

[rfcomm2k]

Thanks for the tip about using SQL. Here is how I solved it:

stSQL = "INSERT INTO [" & TableName & "]( callingparty, dialed, startyear, startmonth, startday, starthour, startminute, startsecond, endyear, endmonth, endday, endhour, endminute, endsecond, hours, minutes, seconds, fname, lname ) SELECT [" & CiscoTableName & "].callingparty, [" & CiscoTableName & "].dialed, [" & CiscoTableName & "].startyear, [" & CiscoTableName & "].startmonth, [" & CiscoTableName & "].startday, [" & CiscoTableName & "].starthour, [" & CiscoTableName & "].startminute, [" & CiscoTableName & "].startsecond, [" & CiscoTableName & "].endyear, [" & CiscoTableName & "].endmonth, [" & CiscoTableName & "].endday, [" & CiscoTableName & "].endhour, [" & CiscoTableName & "].endminute, [" & CiscoTableName & "].endsecond, [" & CiscoTableName & "].hours, [" & CiscoTableName & "].minutes, [" & CiscoTableName & "].seconds, [" & CiscoTableName & "].fname, [" & CiscoTableName & "].lname FROM [" & CiscoTableName & "];"
DoCmd RunSQL stSQL

 

[LPurvis]

No need for all those table prefixes.

stSQL = "INSERT INTO [" & TableName & "] ( callingparty, dialed, startyear, startmonth, startday, starthour, startminute, startsecond, endyear, endmonth, endday, endhour, endminute, endsecond, hours, minutes, seconds, fname, lname ) SELECT callingparty, dialed, startyear, startmonth, startday, starthour, startminute, startsecond, endyear, endmonth, endday, endhour, endminute, endsecond, hours, minutes, seconds, fname, lname FROM [" & CiscoTableName & "];"
DoCmd RunSQL stSQL