-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Format of a linked excel file
- Thread starter Peterso
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 64,700
- Office Version
-
- 365
- Platform
-
- Windows
In Access, each record (row) is independent of each other, and the order of the records in and Access table (or any other relational database table) really has no meaning (someone once described it as think of a table in a relational database as a bag of marbles). As such, if you are missing data from certain records, it can really be a pain to work with.
The preference is to clean up the data before it gets into Access. If you can clean up the data in Excel, that would be preferable. I often time will create Excel macros for data cleanup purposes like this.
If the data is imported (and not linked) into an Access table, you could also clean it up using VBA and looping through recordsets. That is a bit more involved, especially if you are not proficient in Access VBA or working with recordsets. If the data is linked, you do not have the ability to easily update the data in Access (Access will not update Excel files without the use of VBA affecting the Excel file directly).
If there is some logic as to what each missing field should have as its value, you may be able to use a calculated field in Access to get the value you need. However note that will NOT actually update the blank field. It is simply a calculated field returning a value. See here: Microsoft Access tips: Subquery basics
The preference is to clean up the data before it gets into Access. If you can clean up the data in Excel, that would be preferable. I often time will create Excel macros for data cleanup purposes like this.
If the data is imported (and not linked) into an Access table, you could also clean it up using VBA and looping through recordsets. That is a bit more involved, especially if you are not proficient in Access VBA or working with recordsets. If the data is linked, you do not have the ability to easily update the data in Access (Access will not update Excel files without the use of VBA affecting the Excel file directly).
If there is some logic as to what each missing field should have as its value, you may be able to use a calculated field in Access to get the value you need. However note that will NOT actually update the blank field. It is simply a calculated field returning a value. See here: Microsoft Access tips: Subquery basics
