A wierd condition for a Make Table Query!

[mud2]

Consider the following:

Dim TRY_SQL As String
Dim Dummy As String

A: Dummy = "Field2"
B: Dummy = Inputbox("Enter field name")
C: Try_SQL = "SELECT " & Field2 " & " INTO Table2 From Table1;"
D: Try_SQL = "SELECT " & Dummy & " INTO Table2 From Table1;"
E: MsgBox TRY_SQL
F: DoCmd.RunSQL TRY_SQL

Letters A: - F: put in here for reference, NOT in the VBA.
Table1 is a valid table with 3 rows and fields named Field1 and Field2

Line C works
Lines A and D work.
Lines B and D work ONLY if Table 2 is deleted before execution, otherwise a message "The RunSQL action was cancelled."

Line E produces the same message in all cases!

If a "driver" sub is used to obtain and pass the Dummy, only works if Table2 is first deleted.

???

[XLGibbs]

The problem is that if the table already exists, it can't do a make table query without first deleting it.

In Access itself, this is covered within the programming by a message prompt that advises to that..."Table xxxx will be deleted if you continue.."

When accessing through OLEDB or other driver...you must first execute a statement to Delete the table

"Drop Table xxxxx"

The other option would be to APPEND the table, which I believe you are familiar with..

"Insert Into Table ( {Column Names })
Select {Values}"

Of course the Select statement can be a likewise crafted query which produces the necessary results.

Essentially "Select {values} INTO xxxx" is a make table query..so the warning would be expected...if the table already exists...it can't proceed with that operation.

Part of Access native program is that a specified Make Table query (although unseen) checks if the table already exists and prompts as such.

[mud2]

Thanks...
When lines A & D are used, the SQL works even if table2 exists. A message comes saying that 3 records have been found, and warning that a table will be deleted.
When lines B & D are used, The same message saying that 3 records have been found pops up and then the "error" msg appears.

The simple work-around is to check for the existence of table2 and to delete it if it does exist. Just a nuisance!
mud2

[XLGibbs]

Yes, it is, but it is necessary to prevent "accidental" deletion.

But a nuisance just the same.

FYI, the trick for turning off the access database warnings would be

DoCmd.SetWarnings(Off)

And the record set warnings, your table will be deleted etc warnings won't pop up...

Obviously, back on would be DoCmd.SetWarnigs(On)

[OBP]

XLGibbs, the set warnings on and off that you have given are not the standard ones and although DoCmd.SetWarnings (Off) is accepted by the VBA Editor the DoCmd.SetWarnings (On) causes a Compile Error.
The normal set warnings are
DoCmd.SetWarnings False for Off and
DoCmd.SetWarnings True for On

[XLGibbs]

XLGibbs, the set warnings on and off that you have given are not the standard ones and although DoCmd.SetWarnings (Off) is accepted by the VBA Editor the DoCmd.SetWarnings (On) causes a Compile Error.
The normal set warnings are
DoCmd.SetWarnings False for Off and
DoCmd.SetWarnings True for On


One of the reasons I hate Access to begin with. Thanks for the catch.

[omocaig]

I use DoCmd.RunSQL when I'm testing so I can see the warnings. Then when I go to production I use currentDb.execute which does not require you to shut off the warnings.

hth,
Giacomo