hi,
i'm building a MS access DataBase for my customer. in between i'm always testing if it works fine (VB code, DB connections, etc.). But at the end, i would like to have DB empty of those test entries.
what is the best and savest way to delete these entries (so non of the traces are left behind)?
thank you.
When i enter entries into Access, and then they are deleted, for some reason the size of the Access Database doesn't reduce in size. I've only just noticed this as i accidently entered over 65000 entries in and then had to delete them now the database is twice it's usual size.
Can someone please tell me how to reduce the size of the database and explain why when you select delete record it doesn't actually delete the record.
Regards,
Donni.
:cool:
I'm using Access 2010. I have a form that has a combo box. The combo box lists all employee names and one has to be selected. I've been adding employees over time as new employees come. This DB is 3 years old so there are many employees showing in the combo box selection that are no longer here. I'd like to only have the current employees visible but I do not want to just delete them from the Table because then the historic data wouldn't be complete. How can I only show the current employees in the combo box and maintain the data in the table?
View 1 Replies View RelatedFirst I'd like to say I've learned a great deal from all of your posts. I registered today because I'm having the strangest problem.
Let me tell you that I am in no way a programmer, I don't know any code except some basic html. I was asked to take a 2 day class to learn Access (which was a joke) and then build a database for my employer. I think I've accomplished what they need by using every available book I could get my hands on, forums like this, and the help menus.
Our database is very simple. We only have 3 tables, all of which are related by a field we call our "referrer code".
My problem is this -- we've noticed that some (6)of our records are gone. I have no idea why, or when. We run a roadside assistance company. The database keeps track of members. I can't afford for Access to just drop people. The tables are very small, we only have 1,841 members. There isn't any specialized coding, because like I said, I don't know any. I've mostly used wizards to design.
Does anyone have any idea what, if anything, I should look for. I can send or answer any questions that might help you help me.
I would appreciate anything you have to offer -- even if its just a place to read something that might help.
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
View 1 Replies View RelatedI'm exporting some data from an Access Database into an Excel Spreadsheet to create a report in Excel, and I'm running into some problems with that.
The idea is I have an Excel Template with a tab to take raw data from an Access Query, and then a couple tabs that manipulate that data and a final tab to format it for reporting. Basically, the formatted tab uses lookups to find data that's been exported from the query and display them in appropriate columns and rows (producing a result sort of like a cross-tab query but where I've got better control over the presentation of the results). My end-goal is to have this data formatted, to replace all the lookups with hard-coded values data, and then to remove the raw data tab and other tabs with lookup data to have a clean report ready for printing.
It's that last step - deleting all the extraneous tabs that are no longer feeding any equations because I've copy/pasted values over the lookup equations - that's proving vexing. Everything through this point is in the same On_Click event code:
Code:
Private Sub Command0_Click()
'Note: I'm using early binding right now to take advantage of Intellisense;
'My plan is to go back and replace this with late binding once I have code that works.
Dim objXLApp as Excel.Application
Dim objXLbook as Excel.Workbook
Dim objXLWS as Excel.Worksheet
Dim strPath as String
[code]....
I don't get any error messages. I actually have a messagebox thrown up in the code to inform me that I'm getting to the right worksheets selected. (The loop is selecting the worksheets as expected. But when I open the file in Excel... all of the worksheets are all still there, including the ones that should've been deleted.Is there something I should be doing to make sure the worksheet deletion goes through?
I've just joined a company based in Scotland, they are running a multiple user access 2000 database: backend and front-end. Recently they have had an issue where a user inserting a new row in a table, as they do, the record above drops off the table (disappears/unrecoverable). My first impression has been that this is a DB corruption issue and as such have ran compact/repair.We are running this database on network server:
-Backend resides in central location
-Frontend - Each user has their own front-end, storded on their personal network drive.
Would I be right in saying that the best solution would be to convert to newer version of access ?
When I highlight multiple records and choose delete, it only deletes 1 record.
Is it possible to delete more than one record at a time?
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.
I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID
Name
1
Adam
2
Ben
This week I have three new entries New entries
ID
Name
3
Charles
4
Richard
So when I run the same query next week I will get something like this.
Old Entries
ID
Name
1
Adam
2
Ben
3
Charles
4
Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
I'm running the following servers:
1. A Windows Server 2008 with MSEXCHANGE
2. A Windows Server 2008 with Terminal Server - my MSAccess database FE & BE is installed on this server.
3. Both servers are on the same LAN network.
4. The APPOINTMENT BOOK is in the MSAccess database and i have add the the idividual MAILBOXES in my tblEmployees.
5. I want the employees to make their appointments in the APPOINTMENT BOOK (4 above) and then the appointment must also be inserted/displayed in the employees MSOutlook calendar.
6. This works fine when the employees MSOutlook is open on the Terminal Server (2 above).
this will in effect mean that we need to open all users (emplyees) MSOutlook on the Terminal Server to insert the appointments from the MSAccess database into the MSOutlook calendar.I should think that there must be a way to insert the appointments from the MSAccess database into MSOutlook without opening all the the users MSOutlook on Terminal Server?
I've built a form on my Access database that invites a user to enter a start date and an end date into two textboxes. When i press a command button, a parameter query runs which uses the two textboxes as the parameter's criteria. However, to ensure that both textboxes have a data entry, i have tried entering in some VBA that prompts the user to enter in a date if its left null...then set focus to that textbox: Here it is...
If IsNull(Me.txtWeight1) Then MsgBox "You must enter a minimum weight!"
Me.txtWeight1.SetFocus
Else
If IsNull(Me.txtWeight2) Then MsgBox "You must enter a maximum weight!"
Me.txtWeight2.SetFocus
Else
If IsNull(Me.txtWeight1) And IsNull(Me.txtWeight2) Then MsgBox "You must enter a min and max weight!"
End If
This will not compile for me! I've tried with and End If and without an End If and other stuff. Why won't it work?
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
There is an issue that I cannot resolve in MS Access:
I have a large files with multiple columns that has 2 columns that randomly contain duplicated data.
I would like to filter out the duplicated entries in these 2 columns without spoiling the data integrity - and so the duplicates appear on the same row.
I think what I want it pretty much like conditional formatting in excel, but unfortunately the file size is really large to be filtered in excel.
I have a problem about deleting record from a form, it will be grateful if anyone can help me with this.
Here are my tables:
Par_tor: some personal details such as ID, name, phone NO., etc
Par_tion: the record of who has participated which activities.
Activity: name of the activity.
My problem is: the main form of the my form contains the stuffs in table Par_tor, and the subform contains the stuffs in Par_tion, so the user can view these participator's details and the activities they have participated.
In the main form, I also wanted to created a delete button to delete an entire record (including a participator's detail + his/her participation record) at once, but it didn't work since the participation was in the Par_tion table not in the Par_tor.
So is there any way that I can delete these records at once?
Hi
I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.
The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
A strange request but I hope someone can help with this one
I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number
e.g On a form text box I enter the number or records to be deleted (e.g.6000).
The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.
Thanks :D
I have a query (Query1) which returns results In one field recordID.
How do I delete records in a table where the RecordID is in the list Query1.recordID?
Thanks
:confused:
I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
I have create tables using SQL
SQL = "CREATE TABLE tblInvoiceLines" & _
"(InvoiceNo LONG, PartNo LONG," & _
"Quantity LONG)"
DoCmd.RunSQL (SQL)
Deleting this with the below code works,
SQL = ""
'This will delete the Invoice Line Table
SQL = "DROP TABLE tblInvoiceLines"
DoCmd.RunSQL (SQL)
But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?
SQL = "ALTER TABLE tblInvoiceLines " & _
"ADD CONSTRAINT fkInvoiceNo " & _
"FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
"tblInvoices (InvoiceNo) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE "
conDatabase.Execute SQL
Thanks
Coops
Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub
Hi All,
I have a very simple name and address DB. What I would like to know is "What is the easiest way of finding a record using the switchboard and then deleting that record from the DB?"
I am an Access novice so nice easy replies would be appreciated.
Many Thanks
Keith:o
Hi All
I have 2 tables "Customers" and "Work in Progress (WIP)"
The "WIP" table is result of the form "WIP" where I input all my data for each job completed. I use a lookup control "JobNo" to input the customer details from the table "Customers".
The objective is to call up the customer details into "WIP" add the job details, then on save, delete the record in the "Customers" table.
If this is possible, could you give me some ideas how.
Thanks in Advance.
Geoffk
Let's say we have a table:
Sipping branchReceiving BranchProduct #Qty to ship
a c PR1 43
a d PR1 25
a k PR1 1
....
Now I want to delete all lines that have same product code and shipping branch as a privous line.
So the only max amounts for each product and each shipping branch will remain.
How to delete those lines?
Thank you.
Hi.
Is there a way to delete multiple queries or tables when in Design View, rather than 1 at a time?
Russ