Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

Make Table Query in Access 2003 VBA

I am executing a make table paramater query in vba

using the following code

  Dim db As Database
        Dim qry As QueryDef
       
   
   
        'use current database
        Set db = CurrentDb()
   
        'execute query
        Set qry = db.QueryDefs("Create_DailyStoreSalesFromROW")
         
        'specify value of parameters
        qry.Parameters("StartDate") = strStartDate
        qry.Parameters("EndDate") = strEndDate
         
        qry.Execute


When an attempt is made to try and execute qry.execute

it says the table already exists

I there a way to force the make table query delete the table like it does when you double click on it in the query window?



0
johnnyg123
Asked:
johnnyg123
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
the quick and dirty

add  >> on error resume next


Dim db As Database
        Dim qry As QueryDef
       
on error resume next  
   
        'use current database
        Set db = CurrentDb()
   
        'execute query
        Set qry = db.QueryDefs("Create_DailyStoreSalesFromROW")
         
        'specify value of parameters
        qry.Parameters("StartDate") = strStartDate
        qry.Parameters("EndDate") = strEndDate
         
        qry.Execute
0
 
Rey Obrero (Capricorn1)Commented:
or this

Dim db As Database
        Dim qry As QueryDef
       
   
        'use current database
        Set db = CurrentDb()
   

if dcount("*","msysobjects","[name]='nameOfTable'")>0 then
db.execute "drop table nameoftable"
end if

  'execute query
        Set qry = db.QueryDefs("Create_DailyStoreSalesFromROW")
         
        'specify value of parameters
        qry.Parameters("StartDate") = strStartDate
        qry.Parameters("EndDate") = strEndDate
         
        qry.Execute
0
 
Don ThomsonCommented:
Try unchecking the Action queries box under Tools - Options Edit/Find
Other than that you could just delete the table first - then create it - With an on_error that basically ignores the error
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now