Best way to refresh table with imported data

[Techno]

In an Access database, we have a table which has been filled with imported data from an excel sheet. We had to change data type of a few columns during the import and this table has relationship with other tables.



Next month we will need to refresh this table with latest excel data. The question is how best to do it?

1. The current solution that we have currently is that we have a linked table (a separate table) to the excel file, next month when the data in excel changes (excel file name will remain same), we will run a data delete query on the actual table and then an append query to append the linked table data to the actual table.

2. Linked tables can't have relationship so the above was the only solution.

Is there any other or better way to achieve this? Is there a way to simply refresh the import as available in platforms like Power BI?
Any help greatly appreciated.

[June7]

If you want data in table then it must be inserted into table Since you have a process to accomplish that, not really understanding what you are asking for.

[Techno]

Thank you for the reply...Actually I am looking for a way to refresh data in an imported table directly (and not through delete rows and append through a linked table method) as the connection file address remains fixed, is it possible in Access? This is the way data refresh is done in Power BI, once connected later only refresh is required to bring in new data, Linked table in Access seems to have this but they don't support relationship.

I am new with MS Access any help or something about the best practice would be a big help for me, Thank you.

[ArviLaanemets]

The solution I have used without any problems.

The Excel file to be linked is always same, in same location in LAN. Excel users work with it either directly, or using individual Excel files stored in LAN. The Excel file to be linked may have a hidden sheet(s) with tables where the info to be imported is read from tables, updated by users, or read from individual Excel files (using either Excel formulas, or ODBC queries);
You link the table(s) with data to be imported in this Excel file into your Access app. The Access app has Access table(s) to store info read from linked Excel table(s), and a procedure, which reads data from linked Excel table(s), and updates proper Access tables with it (adds new records, deletes records, or updates records). Linked Excel table(s) in Access app are used only by this procedure - and never in any other way. The info imported from linked Excel tables is used in your app (except by update procedure) only through updated Access tables - never directly (and you a free to set any relations for them);
The updating procedure may be run manually (from button), or invoked by OnOpen event of Access app after current date (and latest update date in a table created specifically to keep it) is checked, or to be scheduled (p.e. using windows scheduling).

[Techno]

Thank you...

[CJ_London]

You do not have to specify relationships to have a join in a query.

so would help if you clarify what you mean. - for example if there is there a unique value (for all time, not just this occasion ) in the excel file you can left join on the field in the destination table and only import where the destination field is null. Or perhaps a unique key can be created by combining 2 or more fields. If it is the whole record you can use a hash key.

all depends on the actual requirement

[Techno]

Thank you....Yes...importing only different rows is a very good idea which I'll keep in mind....in this particular case, there is already an imported table and wished to update it with altogether new data for the next month excel....really thankful for the helpful suggestions here

[CJ_London]

Your requirement is a bit confusing. What is the benefit of deleting all the data in the destination table and then appending the data from the excel? Why not just use the linked table as is? No need for the destination table at all...

[June7]

once connected later only refresh is required to bring in new data

That's what link to Excel is - connected data source that automatically refreshes. If you then want data in an Access table, it must be inserted. Is this Excel workbook cumulative data or only new data?

Yes, could just work with the Excel link same as a table, except for editing. However, Excel sheets with a lot of data will become slow and cumbersome.

[Techno]

Thank you ....Linked table don't support relationship....I needed to use this data as the master data (primary key) for another table where I am manually entering updates so due to relationship I could use referential integrity for checking the manual entries. For reverential integrity to work there must be relationship between two tables. Thankyou again.

[Techno]

That's what link to Excel is - connected data source that automatically refreshes. If you then want data in an Access table, it must be inserted. Is this Excel workbook cumulative data or only new data?

Thank you, Excel has fresh data for every month, file name remains same. Yes, the linked table is the closest possible to the required but the problem faced was that the linked table don't support relationship and can't be used for referential integrity data validation for other tables.

[CJ_London]

I don’t see how you method supports referential integrity since you delete all your data. If your destination table is the primary table you either won’t be able to delete the record if a child record exists somewhere or it will delete the child record as well - depends on the settings for the relationship.

it only makes sense to me if the dataset is large - several 100,000 records - for indexing and performance reasons, nothing else. But then you will need to constantly compact/repair more frequently to recover the space left by the deleted records

But it’s your project so up to you

[Techno]

I don’t see how you method supports referential integrity since you delete all your data. If your destination table is the primary table you either won’t be able to delete the record if a child record exists somewhere or it will delete the child record as well - depends on the settings for the relationship.

it only makes sense to me if the dataset is large - several 100,000 records - for indexing and performance reasons, nothing else. But then you will need to constantly compact/repair more frequently to recover the space left by the deleted records

But it’s your project so up to you

Thank you, Primary key is in the actual table (which is fed by the linked table) and manual entry table is the child table (makes sure no typing mistakes or wrong data). Only simple referential integrity is forced (not delete option). Of course the design has plenty of scope of improvement but it is limited by my current Access skill
I am really thankful for the answerers here. This is a very lively place.

[CJ_London]

Primary key is in the actual table (which is fed by the linked table) and manual entry table is the child table (makes sure no typing mistakes or wrong data). Only simple referential integrity is forced (not delete option)

So when you come to delete all the data in the actual table you are left with orphan records in the child table (no related parent since you have deleted them). So if you are successful in deleting the records in the actual table, you cannot be enforcing referential integrity (which means a child record cannot exist without a parent).

I think you are confusing referential integrity with a simple join like this (referential integrity not ticked)


Whereas with referential integrity ticked - you get a join like this - with a 1 and infinity symbols

[Techno]

Yes, you are correct....I now see the problem. It won't work as it is. I think I can make below two as the rule of thumb

1. Don't destroy a table if it has a relationship
2. Don't empty the table if it has referential integrity as Parent