Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


DAO Execute Make Table Query Error if Table Exists

DAO Execute Make Table Query Error if Table Exists

DAO Execute Make Table Query Error if Table Exists

Been working on this problem for a full day. Using Access 2016 with file formats 2003. Database file is on shared department network.
I have a form with vba behind that upon clicking Start button, about 30 queries run in sequence saving us about 4-6 hours every time we do a What If (previously performed this work manually).
1) I had been using DoCmd.OpenQuery "query name" for all the queries which include mostly MakeTable queries. I used DAO to Append fields and to Update new fields as needed. No vba errors ever but where I ran into a problem was in final results - one or two tables did not get updated properly if I ran through the queries too fast (even with SetWarnings on). So, researched internet and found suggestion to use DAO Execute in place of DoCmd.OpenQuery for action queries. My code changed to 2)
2) Set myDb = CurrentDb
myDb.Execute "query name", dbFailOnError
Set myDb = Nothing
This new code replaced all the DoCmd.OpenQuery code for action queries. I had a few Select queries and left the code as DoCmd (per web research).
Problem: The new code causes Error and exit from Sub not for syntax error but if for example a table already exists when I run Execute against a Make-Table query. The DoCmd.OpenQuery does not exist sub but allows the current table (same name as the MakeTable name) to just be deleted I guess; no error and vba sequence continues.
Since we run this routine over and over, I don't want to manually delete all tables that are named in my MakeTable queries.
Does anyone have a solution or suggestion for using DAO Execute query method that does not stall out if table is already in object list?
Thanks for any suggestions.

RE: DAO Execute Make Table Query Error if Table Exists

Just check you name before running the update queries.

CODE -->

Public Function TableExists(TableName As String) As Boolean
  Dim TDF As TableDef
  For Each TDF In CurrentDb.TableDefs
    If TDF.Name = TableName Then
      TableExists = True
      Exit Function
    End If
  Next TDF
End Function 

if not TableExists("YourTableName") then CurrentDB.Execute "query name", dbFailOnError

RE: DAO Execute Make Table Query Error if Table Exists

Thanks MagP for the input. I am currently not able to work the project I talked about, but will try your If table open logic as soon as I can, and will respond. JJL

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close