Please Help - Having problems with an MS Access Macro deleting steps/tables

So here's the story. Where I work we have crop information for planting & planning of the coming years that is entered into different Excel files.

Once the data is entered, then I am told to run an Access macro that then updates our database to reflect the changes made. After this has been completed, a Crystal report runs that shows those changes, and what our forecast is projected to be.

Now the problem: When I went to update the macro to add in the "DeleteObject" - 2015 table data, then added the "OpenQuery" - new 2015 query input, everything seems okay. But when I run the macro it gives me an error that, (the existing table "***2014" will be deleted. do you wish to continue?) . Now all I did was do a 'copy/paste' with the 2014 'deleteObject' and copy/paste of the 2014 'openQuery'. I did create an initial 2015 table, and modify the 2015 query to reflect the year change.

What am I doing wrong? Let me know if you need screen shots of anything. I'll see what I can do to help explain any other issues.

Note: I did not build this initially. I inherited this, and the previous guy took a couple of years to put this all together. I'm impressed with what he did.

Thanks,
Leo
LeoBriggsITAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
i think your macro are already messed up..

create a copy of the db.

delete all the commands in the macro, then build the macro commands in the proper order..

use the old macro as reference.
0
 
Rey Obrero (Capricorn1)Commented:
<But when I run the macro it gives me an error that, (the existing table "***2014" will be deleted.>

this mean only one thing..

the query criteria is set to delete the  2014 table..
0
 
LeoBriggsITAuthor Commented:
Okay, so if that's the problem, then how do I change it? I'm running Office 2010 Pro Plus.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Rey Obrero (Capricorn1)Commented:
open the query in design view and change the criteria and table name ***2014 then save

or change the codes that will delte the table

here is a sample code  of deleting a table  "****2014"

docmd.deleteobject actable, "****2014"
0
 
LeoBriggsITAuthor Commented:
okay, forgive me, but I guess I'm not getting it.

The steps in the macro I'm working with, (as I'm interpreting them), go from 2011 to 2012, to 2013, to 2014, to 2015....

! Delete Object
   Object type:  table
   Object name: ppbud2014

OpenQuery
   Query name: 0 ppbud2014
    View:  datasheet
    Data Mode:  Edit

! Delete Object
   Object type: table
   Object name: ppbud2015

OpenQuery
   Query name: 0 ppbud2015
   View:  datasheet
   Data Mode:  edit

And when I open the query "0 ppbud2015" in design view, the only thing it lists in the row that says "criteria" is a designation of '2015' for the field 'fyear'.
0
 
LeoBriggsITAuthor Commented:
sorry, I didn't finish my confusion....I'm wondering which query and where to change the criteria?? I would imagine it would be the '!Delete Object' steps, but that isn't a query. Maybe that's where I'm confused in the Access macro.

Thanks for bearing with me, and helping me out in this.
0
 
Rey Obrero (Capricorn1)Commented:
this part of the macro

! Delete Object
   Object type:  table
   Object name: ppbud2014  '<<< change the object name

will delete the table  ppbud2014
0
 
LeoBriggsITAuthor Commented:
capricorn1,

Thanks for all the help so far. I know the field can't be blank, it will give me an error that there needs to be an Object Name filled in. How do I handle this in Access 2010 to delete just the data only?? I was not having any luck last night researching that detail.

Leo
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you will need a delete query, here is the SQL of the query to delete the contents of ppbud2014



delete * from ppbud2014

save the query as  Q_DeleteData_ppbud2014

then just call the query in your macro


i don't have A2010 at the moment ....


btw, what do the query  " 0 ppbud2014"  does  ? in this part of the macro

OpenQuery
   Query name: 0 ppbud2014
    View:  datasheet
    Data Mode:  Edit

what are the options available in   Data Mode:  Edit ???
0
 
LeoBriggsITAuthor Commented:
for you question "What does 0 ppbud2014" do? I believe it pulls data from an Excel file based on criteria of it's fiscal year. Criteria for this query would be: field - fyear with criteria being 2014. The macro has one built for each year starting in 2011, and I am needing to add the year 2015 into this run. That part of why I'm trying to solve for this.

I'll admit I'm having the worst problems with this, probably because I'm a network guy, and the only database experience I've had was some Access classes, and dBase back in the early 90's. So the SQL part does get frustrating for all who deal with me. For this I apologize.

Your solution above says I will need a 'delete query' here, yet in Access I don't have that option. Should I be converting this macro to VBasic to make those edits work; (kind of force the process)?
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
i am trying to remember the commands in the a2010 macro here...

try

instead of  ! Delete Object , use

runSQL
sql statement   delete * from ppbud2014
0
 
LeoBriggsITAuthor Commented:
Okay, that seemed to work a bit better. But the weird part I'm seeing is that when I run the macro; it seems to delete the data from the 'ppbud2014' table, then add in the field data it's supposed to. But when it goes to the next step, (2015), it gave me the flag that asked me if it was okay to delete the data in 2014. Then it said it was appending to that file again. So I am thinking I need to start from a beginning point because it's not recoginizing the 'ppbud2015' open query, or there is a hidden area I'm not seeing that is designating a formula delete/append to the 2014 query. I just don't see it right now though.
0
 
LeoBriggsITAuthor Commented:
I believe you are correct.

That will be my project to start on this afternoon, (got my work cut out for me). Thanks for all your help, and showing me some tips to, hopefully, make this easier.

I'll let you know how it turns out.
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.

All Courses

From novice to tech pro — start learning today.