Executing make-table queries with VBA

[Guest]

I am doing a fairly simple project for a client in which I am "translating"
an existing macro into VBA in order to enable me to provide the client with a
better user interface. It is a data migration system which will be run on a
regular basis. It includes multiple make-table queries which are used as part
of the process of transforming the data.

When I try to execute one of the make-table queries through code (using
QueryDef.Execute), I get back an error code of 3010 and a message telling me
that the table already exists. This is correct; the table does exist, but I
expect to be able to indicate to Access that the existing table should be
overwritten, just as I can when executing the query through the Access UI or
through a macro. I've tried setting warnings to "off" (DoCmd.SetWarnings
False), but that hasn't helped.

Does anyone have a solution?

 

[Douglas J. Steele]

Sorry, Access's default behaviour is to either raise an error, or create a
"similarly named" table (eg. Customers1 instead of Customers)

Add a step in your code that checks whether the table already exists, and
deletes if it is does:

If TableExists("NameOfTable") Then
CurrentDb.TableDefs.Delete "NameOfTable"
End If

Your "TableExists" function can be something like:

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

Dim strName As String

strName = CurrentDb.TableDefs(TableName).Name
TableExists = (Err.Number = 0)

End Function

 

[Marshall Barton]

I am doing a fairly simple project for a client in which I am "translating"
an existing macro into VBA in order to enable me to provide the client with a
better user interface. It is a data migration system which will be run on a
regular basis. It includes multiple make-table queries which are used as part
of the process of transforming the data.

When I try to execute one of the make-table queries through code (using
QueryDef.Execute), I get back an error code of 3010 and a message telling me
that the table already exists. This is correct; the table does exist, but I
expect to be able to indicate to Access that the existing table should be
overwritten, just as I can when executing the query through the Access UI or
through a macro. I've tried setting warnings to "off" (DoCmd.SetWarnings
False), but that hasn't helped.

If you want to discard all the data in a table, you should
use a Delete query. Then add the new data using an Append
query.

 

[Guest]

Thanks for this information; it's disappointing that Microsoft hasn't
implemented in VBA functionality that is available through macros.

Since I'll need to delete these tables in code, is there any way that I can
get the table name from the query (through the QueryDef object, for example?)

 

[Guest]

Marsh has a good answer, but if you really want to do the make-table and have
it replace the existing table, in VBA, with no errors or messages, try
something like this:

Public Function Make_Table_1()
Dim SQL As String
SQL = "SELECT Table2.NewItem INTO Table1 FROM Table2"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End Function

You can get the SQL by creating what you need in a Query, then converting it
to SQL (a view option) and inserting it into the code. Granted it is an SQL
approach, but executed inside VBA. I just tried this and it works fine.

HTH
Joe

 

[Douglas J. Steele]

You're running the query that creates the table, therefore you should know
the name of the table you're going to be creating!

Sorry, but you're mistaken if you think this is related to the fact that
you're using VBA: you'll run into the same problem using a macro. If you're
not seeing that, perhaps they're running an earlier macro that automatically
deletes the tables in question.

 

[Guest]

Interesting. If I do this:

DoCmd.SetWarnings False
aQuery.Execute
DoCmd.SetWarnings True

I get the error. If I do this:

DoCmd.SetWarnings False
DoCmd.RunSQL (aQuery.SQL)
DoCmd.SetWarnings True

the query runs. Unfortunately, I need to get the number of rows affected by
the query, and CurrentDb.RecordsAffected doesn't do the job. So I'll have to
figure out the solution to that or go to plan B, which is to include the name
of the table to be deleted in my action table, which provides the system with
the names of the queries to be run.

Thanks for the info.

 

[Marshall Barton]

Interesting. If I do this:

DoCmd.SetWarnings False
aQuery.Execute
DoCmd.SetWarnings True

I get the error. If I do this:

DoCmd.SetWarnings False
DoCmd.RunSQL (aQuery.SQL)
DoCmd.SetWarnings True

the query runs. Unfortunately, I need to get the number of rows affected by
the query, and CurrentDb.RecordsAffected doesn't do the job. So I'll have to
figure out the solution to that or go to plan B, which is to include the name
of the table to be deleted in my action table, which provides the system with
the names of the queries to be run.

It's fairly well know that you need to resolve the items in
the query's Parameters collection before using Execute.

If the Query is syntactically correct and its parameters are
properly resoled, it will not generate an error or
confirmation message. If you want to know about "error"
when the query executes, use the dbFailOnError argument.
Another thing to note is that Execute runs synchronously.

If all of the query parameters' value are in text boxes on
an open form, then you can use code like:

Dim aQuery As QueryDef
Dim prm As Parameter
Set aQuery = . . .
For Each prm In aQuery.Parameters
prm.Value = Eval(prm.Name)
Next prm
aQuery.Execute
MsgBox aQuery.RecordsAffected

While RunSQL will resolve parameters, it does generate error
and confirmation messages. It also runs asynchronously, so
you never know when it has completed.