Inhibit MakeTable prompt

[Guest]

Hi All
How do I Inhibit the MakeTable prompt in AXP when I open a form? In
Tools=>Options=>Edit/Find I've switched off all the confirm options but it
persists in prompting " The existing table 'table name' will be deleted
before you run the query" Yes / No

I'm using the Forms on-open event run a make table SQL and I don't want the
user to see it. Any suggestions?

TIA
johnb

 

[Douglas J. Steele]

Are you sure you need a MakeTable? If it's possible to narrow the data down
through a query, can't you just use that query, rather than saving the data
into a table?

If you don't feel you must have a table, and you don't want the users to see
that prompt, determine whether the table already exists, and delete it if it
does.

One way to determine whether a table exists is to use a function like:

Function TableExists(TableName As String) As Boolean
On Error Resume Next

TableExists = (CurrentDb.TableDefs(TableName) = TableName)

End Function

You could then have:

Dim strTableName As String

strTableName = "SomeName"

If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If

and then run your MakeTable query.

Another approach would be not to use a MakeTable query, but rather to delete
all of the data in the table and then insert the new data into it.

 

[Guest]

Hi Douglas

Thank you for the prompt reply. The table I'm creating each time a form is
closed combines two columns into one column in the temp table. This temp
table is feeding an Excel pivottable which needs updating daily. I know that
Dr. Codd would turn in his grave if saw such practice, but when you inherit a
db you are stuck with what you got. I've got around this issue by using
DoCmd.SetWarnings False followed by True and seems work ok.

I've read a fair bit of your work over the years on Access and may I say I
find it easy reading, its good stuff! I am currently trying to get my brain
to understand Class Modules but it refuses and will not absorb it. If you
know of any dummys guide to Class Modules I'd like the address.

Thanks once more for the comments

johnb

 

[Douglas J. Steele]

What you've told me doesn't seem to indicate the need for a MakeTable query.
As I said before, if it's possible to write a query that returns the data
how you want it, you should be able to use the query instead of the table
you're creating. Tables and Queries are almost always interchangable in VBA
commands.

Sorry, I don't know of good electronic guides to Class Modules, just books
and magazine articles. Good luck!

 

[Guest]

Cheers Douglas and thanks for the assistance I shall follow up your suggestions

johnb