Replacing contents of table (1 Viewer)

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
Hi,

How do I replace the contents of a table? I was planning to delete the existing records and then import new data but I am not sure that is the proper way to do it. The table doesn't have any relationships to other tables if that matters.

Gale
 

ebs17

Well-known member
Local time
Today, 03:08
Joined
Feb 7, 2020
Messages
1,882
The first consideration would be to replace or perhaps update and complete data.

Eberhard
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
Hi Gale. When you simply import the new data into an existing table, I think it will overwrite the old data. You could try it out on a test version of your db.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
Thank you theDBguy, I didn't think it would overwrite existing data but that would make it easy. I will give it a try and let you know how it goes :)
Gale
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
The first consideration would be to replace or perhaps update and complete data.

Eberhard
Thank you Eberhard, I am going to try theDBguy's suggestion... maybe it isn't as complicated as I thought it might be :)
Gale
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
Thank you theDBguy, I didn't think it would overwrite existing data but that would make it easy. I will give it a try and let you know how it goes :)
Gale
Hi Gale. If that doesn't work, then we can give you some code to clear the table first. Let us know what you find out. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:08
Joined
Feb 28, 2001
Messages
27,001
Depends on what is going on. If the table has indexed fields and you are replacing values based on those indexed fields, it will overwrite if you do an UPDATE query. However, an INSERT INTO (Append) query won't erase what is already there if it happens to not conflict with existing records.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
@The_Doc_Man & theDBguy
This is my first Access DB and I am creating it to replace a Lotus Notes application that has been in use for over ten years. Now I am preparing the Access DB for release and I want to load the Documents table with current records. I don't want to update the records I want to replace them because they have been used for all kinds of experiments and they are not worth trying to correct.

The import utility allows creation of a new table or appending data to an existing table. Append seems like the way to go ... can't I just delete all but one of the existing records in the table and then append the new records? I will make sure the remaining record is not duplicated in the new data. When the append has completed I will delete the record I left in the table to allow the append. What are your thoughts?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
@The_Doc_Man & theDBguy
This is my first Access DB and I am creating it to replace a Lotus Notes application that has been in use for over ten years. Now I am preparing the Access DB for release and I want to load the Documents table with current records. I don't want to update the records I want to replace them because they have been used for all kinds of experiments and they are not worth trying to correct.

The import utility allows creation of a new table or appending data to an existing table. Append seems like the way to go ... can't I just delete all but one of the existing records in the table and then append the new records? I will make sure the remaining record is not duplicated in the new data. When the append has completed I will delete the record I left in the table to allow the append. What are your thoughts?
Hi Gale. Are you importing the data from Lotus Notes? Or something else?
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
I exported the data from Lotus Notes into a text file. I then imported it into Excel and spent two days cleaning it up :) So now I have the data in very good shape in an excel workbook.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
I exported the data from Lotus Notes into a text file. I then imported it into Excel and spent two days cleaning it up :) So now I have the data in very good shape in an excel workbook.
Okay, perfect. Now, please try the following steps:

  1. Create a new blank database
  2. From the Ribbon, go to External Data > Import & Link > Excel
  3. Follow the Wizard to import your Excel file, remember the name of the table you used
  4. Open the table and double check how many records were imported
  5. Repeat the above process from Step 2, using the same table name you used in Step 3
  6. After it's done, open the table and see how many records are in there now
  7. If the import "appended" the data, then you should have twice the number of records in the table now
  8. If the import process "replaced" the data, then you should have the same number of records as you originally had in the first import process
Please let us know what you find out with this little experiment. Thank you!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:08
Joined
Feb 28, 2001
Messages
27,001
With Access, you can delete the entire table contents (all the records). You don't have to leave one behind to "prime the pump." That is because the table structure exists separately from its data through something called a TableDef structure.

Note, however, that frequent repetition of this DELETE * FROM table; / IMPORT INTO table () FROM ... sequence would lead to a database in frequent need of being compacted and repaired.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
Thank you both very much. I will avoid deleting records and use the method theDBguy described. It works perfectly although without someone telling me about it I would never have figured it out. The option title to "Import into a NEW table" is a little confusing in this case. But no problem... I have you guys and everyone in Access World to help so I am in good shape and I truly appreciate your help. :)

I do have a follow-up question. Something very strange happened and it's mind boggling to me. In following theDBguy's instructions I decided to use a reduced data set so I copied my excel data file and renamed it. I then deleted rows below 101 and all columns after col-f to get a data set of 100 rows with column headers. I deleted the rows and columns I did not clear the contents. When I imported the data the table it contained all of the original 2000+ rows of data in columns A-F. Instead of 100 records I had over 2000 but only the first 6 columns. I checked the data file and nothing exists below row 101 or after column F. How did Access get data I had deleted? I got around the problem by opening a new sheet in the test data file and copying only the data I wanted into it. This is very strange to me... but I know how to avoid the problem.

Thanks again for the help, now I'm ready to update the real table... :)

Gale
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
Hi Gale. How exactly did you "delete" the extra data? If you simply highlighted the rows and pressed the Del button, then maybe Excel left some sign for Access to read those rows as still part of the Sheet with empty data. Instead, try selecting the rows and then right-clicking on it and select delete. See if that makes any difference.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
By habit I use the right click drop down menu... but I almost live in excel so a lot of what I do is muscle memory and I really don't think when I do it... not do I totally remember :) I'll experiment and let you know what I find. I learned another thing about Access in this exercize... when you create a new database it's automatically saved. Not a big thing but now I have to look for a database I created... somewhere... so I can delete it LOL
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
Okay. Let us know how it goes. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:08
Joined
Feb 28, 2001
Messages
27,001
Using the "highlight"/DEL key empties the row. If you use the right-click>>Delete... method you get an option box that would empty the rows or empty and remove formatting or actually REMOVE the rows, renumbering the grid to replace the removed rows.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
That's exactly the problem. I created three tables from portions of my original Excel data that I "groomed" using different delete key and right -click>>Delete combinations on the data to remove columns and rows. The Right-click>>delete method worked perfectly every time, but the Del key deleted data was always picked up by Access as records with null entries. That isn't exactly what happened to me the first time but I am sure I understand what's happening now. This is very interesting and potentially dangerous. I think the safest way around the problem is to always copy and paste groomed data to a clean worksheet before importing. Do you agree?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
21,358
That's exactly the problem. I created three tables from portions of my original Excel data that I "groomed" using different delete key and right -click>>Delete combinations on the data to remove columns and rows. The Right-click>>delete method worked perfectly every time, but the Del key deleted data was always picked up by Access as records with null entries. That isn't exactly what happened to me the first time but I am sure I understand what's happening now. This is very interesting and potentially dangerous. I think the safest way around the problem is to always copy and paste groomed data to a clean worksheet before importing. Do you agree?
Hi. I think the safest approach is to clean up the data in Excel first before importing them into Access. However, there are also ways to clean up the data in Access, if the improper data is predictable. For example, if we only have to worry about empty rows, then we can simply execute a DELETE query. But, if we don't know if or how many empty columns we might get, it's still possible to clean it up in Access, but it would just involve more code.
 

GaleT

Registered User.
Local time
Yesterday, 19:08
Joined
Oct 18, 2019
Messages
72
Thank you theDBguy, and I totally agree. In this experiment all of my data grooming is done in Excel prior to importing into Access. I have done some direct table data changes but I will avoid that in the future since all of the data is correct and up-to-date now. Future edits and additions will be through the form. This was a very production exercize, thanks agin for all of the help :)

Gale
 

Users who are viewing this thread

Top Bottom