Deleting Forms,queries,macros In Another Database

Feb 16, 2005

I have a list of objects (forms,queries,macros,reports the number varies but could be up to 100) that need to be deleted from several other databases (about 20).

I could do this by hand (go into each database and select and delete each object) but this is slow and error prone.

I could create a macro and import it into each database and then execute it (I know how to do this)

or (!!!!!!)

there should be some way to be in one database (HOME) and programatically (VBA) delete in the other database (OTHER).

I know how to

Dim db As Database
Set db = DBEngine.Workspaces(0).OpenDatabase("c:other.mdb")

What do I do next. I could use the db.execute command for tables (which I don't want to delete) but what about forms,queries,macros, and reports ???? I tried to modify the MsysObjects table but ran into permission problems.

Any Ideas

View Replies


ADVERTISEMENT

General :: Way To Show All Database Elements Tables / Forms / Macros / Modules

Jun 7, 2013

I have a .mdb file (access 2003) which has ballooned in size to 1.2GB and I'm not sure why. I tried deleting some older tables (which were copies and had about 38,000 rows each) but it hasn't made a dent. Is there a way to show all the database elements tables, forms, macros, modules etc and list their sizes so I can see what has caused the size to increase?

There are 3 tables linked via ODBC which have tens of thousands of rows but as these are linked I wouldn't have thought they would have increased the size at all?

View 10 Replies View Related

Open Database With Macros Disabled.

Jul 2, 2007

Hello,

I have a 'legacy' Access DB which I need to get into.

Unfortunately there is a problem, whenever I try to open it (using MS Access 2003) there is an error (Disc/Network error 3043) and the database closes immediately.

How can you open an MS Access db with all macros/vba etc disabled, so that the code causing the error does not execute and I can find out what is going on ?

I have not been able to find any such options in the MS Access 2003 open dialog or menus.

Thanks,

Andrew

View 5 Replies View Related

Queries, Macros Of VBA

Jun 28, 2005

When you need to run a series of Queries.

And they must be sequential.

Is it better to use a Macro or VBA to call the queries?

Thanks Everyone.

Joe

View 2 Replies View Related

Tables And Forms And Macros Oh My!

Jul 17, 2007

Hello everyone! I’m new at building databases; actually, I’m learning as I go as I’ve never had any formal training and this is my first significant attempt. So with that said, I’m hoping someone out there may be able to help me and I apologize in advance for how long this is. A little back round—I’m writing a database to create a report showing the loan history on an account—basically it shows how many loan payments came in with the dates they were processed and amounts of each payment. I created a table that is supposed to populate after entering the policy number, original loan amount and original loan date into a form. I have a macro that when a button is pushed on the form, should run a query (this query is made up of the table I created as well as tables from our data warehouse and when ran, gets the loan payments, dates processed, amounts, etc), opens a report in print preview (showing this loan info), and then deletes the record from the table that I created via a delete query (I do not want a history of each policy number that is entered into the form). Now to the issues—when I enter the info into the form, it does not automatically populate the table that I created, therefore the report does not show any loan data and obviously the delete query would not work. If I move to the next blank record, the table does populate with the info I just entered in the previous record, the report runs, but the delete query still does not work. I’m not understanding why my table won’t populate right away—the record source on the form is my table. I assume the delete query not working could have something to do with my macro? Here is how I have my macro set up:

Action: Echo Action Arguments: (Echo On) No (Status Bar Text) blank

Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryFormInfo (In Database Window) Yes

Action: OpenReport Action Arguments: (Report Name) rptFormInfo (View) Print Preview (Filter Name) blank (Where Condition) blank (Window Mode) Normal

Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryDeleteRecord (In Database Window) Yes

Action: Echo Action Arguments: (Echo On) Yes (Status Bar Text) blank

So if any of you made it this far, can anyone see anything obvious that I have wrong? Just a warning—I’m not good with VB code. I appreciate any input/suggestions you may have.

Thanks for your help!

View 1 Replies View Related

Forms :: New Buttons Are All Suddenly Embedded Macros?

Feb 3, 2015

I've been developing a new db and have been adding buttons to forms all along without any issue.

I would create a button and under the event tab in the properties sheet all the buttons used to indicate "On Click" would produce an [Event Procedure]. Suddenly the "On Click" now indicates an [Embedded Macro] is going to run, which is not what I really want to have happen......

View 1 Replies View Related

Big Ol' Access Problem (Forms, Macros, Importing, Query, Etc

Mar 31, 2008

Okay, so I just got a big project dumped into my lap to rebuild a database that was lost. This DB would import data from a couple excel spreadsheets and a text file, compare them to find any records that are missing from one or the other then spit that information back out in two reports. To make things harder, the person who uses this DB isn't the most computer literate and needs to be able to do everything with the push of a button. For this project I'm using Access 2003 with a possible need for 2000 backwards compatibility

First Issue: Importing. Is there ANY way to save an import so a macro could run it without the user doing anything? The other option is linked tables, but is there any way to update the data in those links because some of it needs to be changed. Also, can linking combine two excel spreadsheets into one linked table?

Second Issue: Information updating. As I mentioned before there is some information that needs to be updated before we can do comparisons. There is a field in both record sets that could be used for comparisons except for the fact it is formated all wrong.

In the excel spreadsheet it is set up as just a string of numbers like "3125" or "55879", but in the text file they added some preceding digits to this number code. The text file adds "HM" and a number of 0s to each number so that the field is a uniform 10 digits long. Like "3125" would become "HM00003125" and "55879" would be "HM00055879". So each record needs to have this data either added to it or trimmed off. The problem here is the variable number of 0s. I created a macro that would use the Len function to test how many zeros need to be added and update the field, but it can only run on one record at a time.

What I need to do is have either a trimming or adding updater for that field that runs in the background (either through the macro button or when loading a query) to make those fields compatible. It also would be preferable to work through linked tables.

Thankfully, there isn't any sort of rush on this paticular project. If you have any advice on any part of this project, please post

Thanks in advance

View 14 Replies View Related

Deleting All Data From B_E Database Tables. (AC2003)

Nov 26, 2007

Hi all,

Im trying to write a query so that all data in all but two tables is deleted, not the tables themselves only the data.

I keep getting these errors when im trying to build a suitable query..

COULD NOT DELETE FROM SPECIFIED TABLES
or
TOO MANY FIELDS DEFINED

With any luck i'd like to able to have this query run automatically each day is there any way to do that or do i have to repost in vba section?

Thanks for reading!!

View 3 Replies View Related

Deleting Excel Sheet Linked To Database

Aug 7, 2015

I have a database that links to an excel sheet and take data from it. For a fail safe i put deletes in several places in case of user error to make sure the sheet is deleted. If it is already deleted the other deletes don't act well.

I need to write a conditional iff statement that if it doesn't exist it ignores the delete call. Not sure how to structure it.

Private Sub Command3_Click()
DoCmd.DeleteObject acTable, "Sheet1"
DoCmd.Close acForm, "District Select Form"
End Sub

View 4 Replies View Related

Error After Deleting Split Database File

Nov 16, 2011

We have a MS Access Application. It involves authentication as well. I split the database and created this db_be file. I deleted this. Now I'm not able to login into this Application.

View 1 Replies View Related

Deleting Multiple Tables Or Queries

Mar 6, 2007

Hi.

I have a cluttered database with too many 'no longer necessary' queries and tables.

Is there a way (other than deleting them one at a time) to delete multiple ones at once, as I can in My Documents, et cetera?

Russ

View 2 Replies View Related

Queries :: Deleting Fields Within A Table

Sep 16, 2014

Is it possible to create a query that will delete the fields within a table?

View 1 Replies View Related

Queries :: Deleting Duplicates Without Primary Key?

Nov 4, 2013

I bounced into a big problem with Access 2010 where I cannot seem to be able to remove duplicates from a table containing millions of entries midway through a series of queries.

The table is formed like this:

Date - Serial Number - Reading 1 - Reading 2 - Reading 3
30.7.2013 - 1122334455 - 12345 - 987654 - 654321
30.7.2013 - 1122334466 - 12345 - 987654 - 654321
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334466 - 12344 - 986013 - 453213

As you can see, there are some rows which contain exact duplicates and some are a bit different. I wish to remove those fields with exact duplicates and only the duplicated fields. Running "Find duplicates" in Access gives me about 250,000 rows with such data.

I've tried a few options already:
- I cannot use the date as primary key as there are several serial numbers. I cannot use serial numbers as primary key, because there are several dates. Using reading value as primary isn't an option either.
- Microsoft says I should mark all duplicate values with an x and then make a delete query to get rid of all the x-marked rows. For 250,000 duplicates, that's a bit too much manual 'x-ing'.
- If I do a delete query using the Find Duplicates query as a base, it removes all 250,000 entries from that table, instead of just the 125,000 which had a duplicate elsewhere in the table.
- If I make a query which identifies duplicate data and gives me just one row for each duplicate, the delete query still deletes both entries from the original table.
- I could make a new query which would have only unique values using Totals as criteria (for instance, using First for the Date-column). However, this still leaves the duplicate values in the original table. Note that this database is already 800 MB large and new data is imported once a week, for the next decade or so. I cannot have a table get duplicates every week and leave it there.
- If I make a macro which would create the unique values table first and then deletes the old table, what happens next week when I try to import new readings? I would need to make a new macro each time I try to import new data as the table names change. Or is there a way to first run the unique numbers out, then replace the original table with the new one? With a 800MB database, this would put me dangerously close to the 2GB size limit. I wouldn't be able to use this as a part of a macro, as the database would have to do Compact & Repair each time it deletes the original table, midway through a longer series of queries.
- Having duplicates removed from the original import isn't an option either, as it comes in as a overriding excel sheet for the past 3 months, once a week.

As you can see, it's quite a pickle getting the duplicates out of the original table. This is just a small part of a very long macro, which takes about 15 minutes to complete, but due to duplicates the database is getting way too large.

View 9 Replies View Related

Queries :: Deleting Data From Selected Records

Sep 23, 2014

My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).

At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.

View 6 Replies View Related

Queries :: Deleting Duplicates In Non-unique Records

Feb 27, 2014

I have imported a large number of emails into a table tbl_requests.

I had intended to have unique file tbl_requests.date_opened unique, but have ended up with a lot of duplicate records (i.e. tbl_requests.date_opened is not unique !). How to delete any duplicates? I have 15,000 records...

View 3 Replies View Related

Queries :: Deleting Records Based On Selections From Another Table

Apr 1, 2013

I am trying to create a Delete query.

I am trying to delete a specific part from multiple BOMs in my database.

I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.

I am able to select the records with:

Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

Now how do I delete these same records.

I am getting error saying I have to select a table to delete from....

View 11 Replies View Related

Form Based On Queries - Deleting Field From Main Table

Mar 28, 2014

In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).

View 1 Replies View Related

Forms :: Requery Subform After Deleting A Record

Nov 8, 2014

I'm trying to execute some lines of code right after deleting a record in a subform, with a right click on the row I want to delete (the idea is that if a record is deleted the other ones should be updated by module1.tblUpdateLatestDocuments).

I've tried putting this code into the events On Delete and After Del Confirm of the subform, none of that worked.

Code:
Option Compare Database
Private Sub Form_AfterDelConfirm(Status As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Private Sub Form_Delete(Cancel As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Aren't there any events that I could use right AFTER the record has been deleted?

View 10 Replies View Related

Forms :: Delete Button Deleting Too Many Records

Aug 8, 2013

I have a columnar form with a sub-form based on an updatable query. I've created a delete button on the master form with the intention that if the user hits the delete button, it will delete the current record (like its supposed to, right?).

But what is happening is when the delete button is hit, it is asking twice for deletion confirmation and deleting the current record AND the next record. Even if I hit "Cancel" to stop the deletion, it still deletes the current and next records.

Each record has a primary unique ID that is included on the form (but not tab stopped - if that makes any difference), but it doesn't seem to be using that as the basis for deleting.

I've tried two different codes for this:

Private Sub Command110_Click()
On Error GoTo Err_Command110_Click
DoCmd.RunCommand acCmdDeleteRecord
Exit_Command110_Click:

[Code] .....

View 3 Replies View Related

Forms :: Deleting Records On The Table Through Form

Oct 12, 2014

I have a form linked to a table and in my form there is a listbox reflecting the records in my table. Evereytime I tick a record in the list box the information on its corresponding columns appears on the text field.

So far I am able to add, modify and save record to my table using this form but I am having a problem on deleting a record which I selected on the listbox. I created a delete command button but it is not working. Please see attached database.

View 2 Replies View Related

How To Share Access Database Including Forms / Queries

Jun 9, 2015

I have an Acess database (2013). This will be used by several people within one company, they don't have their own server. I would use SharePoint Online, but it seems to me, that you only can "export" the tables. The database I have is quite a complicated system, and it is neccessary to use all the Access tools - forms, reports, queries. Not tables only.

Another idea is to split the database and "store" the backend part (the tables) on Sharepoint (Office 365 online version). And all the users would have their own frontend (forms, queries...) on their own PC, linked to this backend. Would this work? Can everyone add some entries into the database this way? How does it "synchronize" then?

View 1 Replies View Related

Forms :: Deleting ComboBox Lookup Options But Keep Old Records

May 14, 2014

I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).

In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.

Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?

I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.

View 1 Replies View Related

Forms :: Deleting Record When Closing After Autonumber Has Been Incremented

Oct 15, 2014

I have a form that I want to open on a new record (with an Autonumber-based ID string that is a calculated field) and this uses a hidden field in the form when it opens to spawn a new record, ready to be related to some additional items in a join table. The user selects those from a list box.I want to add a "return to main menu without saving" button, but not matter what code I use, it obstinately continues to save the record.

Code:
DoCmd.Close acForm, "JobPlanfromCAFs", acSaveNo
on the command button - no effect.

I also tried this code on the form's BeforeUpdate method. No effect. The new record continues to exist.

Code:
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If

I suppose I can 'query out' incomplete records at other places in the database, but it's annoying to not be able to return to the same JobID again if you don't save it.

View 1 Replies View Related

Access VBA - Crosstab Tables Into Forms / Deleting Blanks

Aug 1, 2013

I have a crosstab table that is displaying in a form within a form because the form has data coming from multiple tables.

Crosstab Query looks like
Name Sum Cust1 Cust2 Cust3.....
Jon 50 250
Bill 100 50 100
Kevin 200 50 100
Ryan 10 500

The issue are the blanks in the rows for each Name. When I bring it up on the form, I want the columns to be the Customers but only customers with amounts. The original form is there to show the name and position and the form within the form should have 1 row for name of the person that is being selected and columns of customers WITH A BALANCE.

View 1 Replies View Related

General :: Securing A Database / Can't Edit Forms / Reports / Queries

Jan 21, 2014

I'm trying to secure my database so users can't edit tables, forms, reports, queries, etc.I'm splitting the database, making an ACCDE for users:

1. I inserted code to disable the bypass key.
2. I inserted code to hide the Quick Access Toolbar (QAT) in the On_Load sub of the form that opens with the DB.
3. Deselect Navigation Pane, Allow Full Menus and Allow Default Shortcut Menus are deselected
4. Then, I use the immediate window to show the QAT, I then create an ACCDE.

How do I link this ACCDE with the original ACCDB? Am I supposed to delete tables from the front end and link the forms/reports to the back end DB?

View 7 Replies View Related

Forms :: How To Make Form Refresh After Deleting A Record In Subform

Sep 3, 2014

I have been trying to figure out how to make a form refresh after deleting a record in a subform. The code in the combo box that populates the subform works fine. If you select the "Action" of "sign" from the unbound combo box above the subform, it places "Sign" in the the subform and then makes a checkbox on the main form [WillTake] = True.

But when I delete the record by right clicking on the entry, the focus is sent back to the main form so I can refresh the form and thus update the [WillTake] checkbox. I have tried just about everything. I can get the focus to a field on the form, but I cannot figure out how to refresh or requery the form.

The code to transfer focus is on the OnDelete of the subform.

I have included a watered down version of the DB.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved