I have a SharePoint list which is updated with data by both onshore and offshore teams within our organisation.
The number of entries is beyond the normal working limit so I need to remove some data but keep a full data base.

I cant have everyone adding the data to an Access database as our offshore team don't have access to the onshore drives.

So I have an Excel file that is linked to the SharePoint list which is run everyday and when refreshed saves as a new filename.
This allows me to do a backup.

However I want to then transfer this to an access database to be used for analysing the data.

I cant link this to the SharePoint list because if I remove data from the SharePoint list it will remove it from Access as well.

So I looked at using docmd.transferspreadsheet. I put some code together and this works fine and will only update any entries that have not already been added based on the "ID" column.
However using this looks at the first 8 rows of data and uses that format for the columns.

My data set has some columns that don't always have a value.
These are because it records if a call has been booked. If there is then there will be a date. If not then its left blank.

So my question is what is the best way to handle this? if there is no data in the first eight rows then it leaves all the rows in that column blank and doesn't add the data in.
The columns where there is a single line of text missing I can add a value for. But what about the column where there is not always a date? What can I put in there to keep the format and the values?

And is there a better way of importing the data? I did think of transferring it to a text delimited file, then transfer that to Access and set the formats for the columns.