Best method to import access data into SQL automatically
I have a need to import/replace data automatically (hands off) several times a day into a live SQL server.
I have legacy data in both MS Access and Paradox DBF formats that has to be imported into SQL Server 3 times each day to keep the inventory "current". It needs to replace the data or at least update it where it is different.
I need it to be fast so it doesn't disrupt the websites that use the data.
The way I am doing it right now is bound to give me trouble.
So far the best method I could come up with is a vbscript VBS file that deletes the current SQL Table data and then repopulates the same SQL Table from the msdb or dbf.
I am fairly certain that is an inefficient way to go.
Ideally I need it to just update existing data and add new data, but I have no clue how to do that.
Scripting that in vbscript would probably take too many resources and cause many problems.