Access linked to Excel recording empty rows

[Ajaxs87]

What my goal is to send data from my Excel file to my Access file. Then from my Access file, I wish to query it to my Sharepoint List. How I am trying to do so is

1. Use a Excel worksheet to Access Transfer, that only has data appear, if it isn't already on the Sharepoint List. This is done by comparing a query of Sharepoint to Excel worksheet ?Entry Log?.
2. Excel worksheet to Access Transfer is linked inside of Access.
3. Sharepoint List is linked inside of Access.
4. Query to Append the linked tables inside of Access to update Sharepoint List.
5. This in turns updates the Excel worksheet to blank.

The problem is when I link the Excel file to Access, it creates several blank records in Step 2. So, when I run the query to combine the two linked tables in Step 4. I end up with 20,000+ records, when it should be 20 records.
I am very unfamiliar with Access, so I am not sure where to start to not pull empty records into the linked Access.

In the attached, I have two access databases and one excel file.
The Excel I the file linked to the Access Transfer.
The Destination access is to simulate the Sharepoint List and is linked to the Access Transfer.
And the Access Transfer is the file linking everything together.

If I need to clarify anything else, just let me know. Thank you for your time and help.

[sleek12]

It sounds like the issue is with the linking process of the Excel worksheet to Access. When linking tables in Access, it will create a new table in Access that mirrors the structure of the source table. If the source table has empty records, Access will create blank records in the linked table in Access.

To avoid this issue, you can modify your Excel worksheet to remove any blank rows before linking it to Access. You can also apply a filter to the Excel worksheet to exclude blank records when linking it to Access.

Here are the steps to apply a filter to exclude blank records:

Open your Excel worksheet and select the range of cells that contains data.
Click on the "Data" tab in the ribbon.
Click on the "Filter" button to apply a filter to the selected range.
Click on the drop-down arrow in the first column header and uncheck the "Blanks" option.
Click on the "OK" button to apply the filter.
Save and close the Excel worksheet.
Open your Access database and go to the "External Data" tab.
Click on the "Excel" button to import the filtered Excel worksheet into Access.
Follow the import wizard and make sure to select the option to link the table instead of importing it.
Once the table is linked, you can use it in your query to update the Sharepoint List.
By applying a filter to exclude blank records in the Excel worksheet, you should be able to avoid creating blank records in the linked table in Access and have a more accurate count of records when combining the two linked tables.

[Gregor y]

if ctrl+end takes you to row 20K something then you have blank rows in your excel worksheet

1. select the entire row(shift+space) and delete them(ctrl+-)
2. do the similar for blank columns(ctrl+space)
3. save the workbook
4. ctrl+end should now take you to the end of the data
5. now the spreadsheet is ready for import without blank records

if you don't want to have to worry about doing this by hand each time you run the access process, then add a delete query that removes the blank rows.