Maximum fields in table problem
Access has a problem when you change fields (name, datatype, number) in a large table. It cannot have more than 255 fields in a transaction. So if you have 200 fields then change 100 fields you have 300 fields in the transaction which exceeds the 255 limit.
If you have 200 fields already you cannot change more than 55 of them at a time. So change 50, (close design) save, (open design) and change another 50 fields .. and repeat.
Just for backup of Excel .. better to copy sheets elsewhere or wnole files to an archive.
Saving transactional data for summary .. start transaction in database, create Excel file with intial data, save link in Access database, action (eg. button) in Access or Excel file to update data in Access with final figures.
You could create separate tables for different portions of the Excel worksheet with a unique identifier for reference (you can't put 200+200 fields in a query but could process them sequentially).
|