Generate Table Name In Make Table Query

  • Thread starter Thread starter CVegas
  • Start date Start date
C

CVegas

Can I pull the value from a field, on my form, and replace the ReName that
follows the INTO syntax in the query below.

This would keep me from having to go into the fax.mdb and change the name of
the table I just created called "Rename"

frm_SendMultipleReports!Combo81 (This is my form and the field that has the
value I want to name the new table in the Fax.mdb)

SELECT dbo_Contacts.ShowName, dbo_Contacts.Company, dbo_Contacts.FirstName,
dbo_Contacts.LastName, dbo_Contacts.Area, dbo_Contacts.CanDo,
dbo_Contacts.Fax, 1 & [fax] AS NewFax INTO ReName IN 'z:\Fax.mdb'
FROM dbo_Contacts
WHERE (((dbo_Contacts.ShowName)=[forms]![frm_SendMultipleReports]![combo81])
AND ((dbo_Contacts.Company)<>"isnull") AND
((dbo_Contacts.FirstName)<>"isnull") AND ((dbo_Contacts.LastName)<>"isnull")
AND ((dbo_Contacts.Area)<="C") AND ((dbo_Contacts.CanDo)=1) AND
((dbo_Contacts.Fax)<>"isnull"));

Thanks in advance.
 
You could programmatically build a string for the SQL statement, and then
execute that instead of the saved query:

Dim strTableName As String
Dim strSQL as String
strTableName = "SomeTable"
strSQL = "SELECT ... INTO " & strTableName & " IN ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

Alternatively, you could alter the SQL property of the QueryDef:
Dim qdf As QueryDef
Dim strSQL As String
strSQL = "SELECT ... INTO " & strTableName & " IN ...
dbEngine(0)(0).QueryDefs("NameOfYourQueryHere")
qdf.SQL = strSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CVegas said:
Can I pull the value from a field, on my form, and replace the ReName that
follows the INTO syntax in the query below.

This would keep me from having to go into the fax.mdb and change the name of
the table I just created called "Rename"

frm_SendMultipleReports!Combo81 (This is my form and the field that has the
value I want to name the new table in the Fax.mdb)

SELECT dbo_Contacts.ShowName, dbo_Contacts.Company, dbo_Contacts.FirstName,
dbo_Contacts.LastName, dbo_Contacts.Area, dbo_Contacts.CanDo,
dbo_Contacts.Fax, 1 & [fax] AS NewFax INTO ReName IN 'z:\Fax.mdb'
FROM dbo_Contacts
WHERE (((dbo_Contacts.ShowName)=[forms]![frm_SendMultipleReports]![combo81])
AND ((dbo_Contacts.Company)<>"isnull") AND
((dbo_Contacts.FirstName)<>"isnull") AND
Click to expand...
 
Back
Top