Good One

[ScottF]

I have 20 different Cities across the USA. They gather data in Excel Workbook and then they are gone to transfer the Excel file to the Home office sever via highspeed connection. In Access I have link built to the spreadsheet in the Workbooks. Every Sunday night at 12:00am a macro will open the links updating the Tables. What I.m doing is given each city a codes example H = Houston extra which is the name of the Excel Workbook for that city. The Workbook has about 4 different spreadsheet on it that are link to Access Tables. Data from the cities offices are gather is one weeks time then sent to the Home office. So to know what week the data came from a using date on the Excel spreadsheet of each enter in the rows. So what I'm saying is each worrkbook it file name is the city code and I do not have to update the link to access every week(Say example if the filename was cite code & date). I hope who every read this can understand me.
Now my Q is there a better way?

 

[neffs]

I'm an old guy. Been programming for a long time. I have taken data from almost any type of file and dumped into any other kind of file. My suggestion is to forget the excel and write the gathering data application in Access and then import the data from their data files to the data files on the server. It's been my experience that tells me that sharing data within the same apps = good...sharing data with different apps = pain.

Sam Neff

 

[ScottF]

That would be great and I agree with you except they are scanning data into the Excel spread sheet from a third party software.

 

[IMO]

Cant you then scan the data directly into access from the third party software? There shouldn't be any problems with that.

IMO