Solved

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

Posted on 2013-01-10
15
183 Views
Last Modified: 2015-01-10
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
0
Comment
Question by:LeoBriggsIT
  • 7
  • 6
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38764722
<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
 

Author Comment

by:LeoBriggsIT
ID: 38764828
Okay, so if that's the problem, then how do I change it? I'm running Office 2010 Pro Plus.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38764847
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
 

Author Comment

by:LeoBriggsIT
ID: 38764892
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
 

Author Comment

by:LeoBriggsIT
ID: 38765119
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38766041
this part of the macro

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

will delete the table  ppbud2014
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:LeoBriggsIT
ID: 38767803
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 38767842
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
 

Author Comment

by:LeoBriggsIT
ID: 38768046
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 38768175
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
 

Author Comment

by:LeoBriggsIT
ID: 38768292
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38768321
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
 

Author Comment

by:LeoBriggsIT
ID: 38768325
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now