Hi. I have an application running on local machines at different geographic locations, that need to update data (and pick some info back) to a central server at the end of the day (receiving data from all locations).
Data volume can be 500 records / day, nearly 2 MB.
I am not sure if this can be called distributed database.
I plan to use SQL Server on the central server and am undecided on whether to use MS Access / SQL server. I will be using VB for the application.
What application technology and database technology (and data transfer) would be ideal?
I would think that SQL Server would be the most ideal. You could set up separate ACCESS DSN (for your various clients to upload their MS Access database updates to), then use DTS within SQL Server to pull data from these various datasources.
What I would recommend is the following (in terms of the DTS structure, easiest, not the most jazzy or seamless approach, but it may work, of course, the other option is writing a stored procedure to either UPDATE or INSERT data into your centralized database):
1) Delete all data from central database.
2) Append data from client#1
3) Append data from client#2
4) Append data from client#3