CODEWITHSUNDEEP
sqlexcelms-access
Joe R.
Joe R.

Reputation: 2052

How can I delete rows in Excel that exist in Access?

I have a table called "ActiveCustomers" in an Access DB which contains customer names and telephone numbers. I also have an Excel sheet which contains the same data in my "ActiveCustomers" table in Access, plus Inactive Customers. I would like to delete from the Excel sheet all the Active Customers that exist in the Access DB, leaving only the inactive customers in the sheet. What do I need to do in order to accomplish this?

Upvotes: 0

Views: 192

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123779

Based on comments from another answer, for a table with two fields:

[CustomerName] - Text(30), Not Indexed  
[PhoneNumber]  - Text(10), Indexed (Duplicates OK)

15 million rows that are completely full (40 characters each), e.g.

CustomerName                    PhoneNumber
------------------------------  -----------
Inactive Customer No: 00000001  9900000001
Inactive Customer No: 00000002  9900000002
Inactive Customer No: 00000003  9900000003
...
Inactive Customer No: 15000000  9915000000

results in an 982 MB .accdb file, which is 48% of the 2 GB maximum .accdb file size.

So, with some care, it should be possible to import the inactive records from all of the Excel sheets into an Access database file and process then en masse.

Depending on what is already in the "main" Access database file it might be prudent to import the inactive records into a separate .accdb file and then create a Linked Table into the main .accdb file to access the active records.

Upvotes: 1

Tony
Tony

Reputation: 10337

You cannot modify Excel sheets linked to an Access database, only read from them.

Therefore I'd recommend importing the data you have in the sheet, use a query to generate the information you need and then export that to a new spread sheet.

Although I don't see why you are maintaining the inactive customers in an external sheet when you have a database? Simply record the information in the database and stop using the Excel sheet.

Upvotes: 1

Kabkee
Kabkee

Reputation: 198

There's several ways of making an access table;

import, link, and so on..

if you selected "IMPORT" when making the table of the data from excel, just delete the table and re-make the table.

if you selected "Link from Excel", close the access table ( NOT Access program itself, just table is enough). and just delete the data from Excel file.

  • HOW TO FIGURE IT OUT, IMPORT OR LINK? * If the table imported from an Excel file is open and you try to open the linked Excel file, Excel alerts you, saying that something wrong.

    If so, the table is directly linked from Excel. go to the excel file and revise it.

Upvotes: 0

Related Questions