Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-10
15
Medium Priority
?
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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