Hi all,
So once again I have bumped into one of the slightly odd and initially very confusing nuances of MS Access/Jet 3.0/VBA.
Currently I have a table which holds data about changes that need to be made to our various PC images. The main fields in this table are the Software code, the Image code, the Date the record was added and whether the update has been completed.
This is great for record keeping, but for checking which images I have made a change to is tricky due to the columnular nature of the table.
I would like to ba able to view the data so that a single row shows the sofware code and a check box for each image code showing whether the change has been made for that specific image.
To start with I created some code which created a new table, added the correct fields and inserted the data. Then I found that constantly adding and deleting tables can cause your database to bloat unless you compact and repair regularly....something I don't want to have to do.
So instead I created a single tabe and created and deleted the fields I required. This worked until eventually I hit a 3190 error "Too Many Fields Defined". This is due to the internal field counter for the table not being reduced by 1 for each field deleted. I have not been able to find a way to reset this counter via code and a Compact does not remove this problem. The only method appears to be to copy the data to a new table, but this runs straight back into the creating and deleteing tables problem mentioned earlier.
Something that should be mentioned is that the number of image codes is dynamic as new hardware generally means a new image. Therefore I cannot create a static table with fields for all the image codes as the number of codes is subject to fairly regular change.
So I am thinking...
1. I need to find an alternative method...
2. Am I completely missing the point and could I do this easily using a query? (If so, please show me how! I can't think quite how to make a query work)
3. There is a way round it and I haven't found it yet. (I'd really like to avoid having to compact the database as much as possible)
4 There isn't a way in access, what I'm trying to do is beyond it's scope (I doubt this!)
So any insight would be much appreciated
Cheers,
Matt
So once again I have bumped into one of the slightly odd and initially very confusing nuances of MS Access/Jet 3.0/VBA.
Currently I have a table which holds data about changes that need to be made to our various PC images. The main fields in this table are the Software code, the Image code, the Date the record was added and whether the update has been completed.
This is great for record keeping, but for checking which images I have made a change to is tricky due to the columnular nature of the table.
I would like to ba able to view the data so that a single row shows the sofware code and a check box for each image code showing whether the change has been made for that specific image.
To start with I created some code which created a new table, added the correct fields and inserted the data. Then I found that constantly adding and deleting tables can cause your database to bloat unless you compact and repair regularly....something I don't want to have to do.
So instead I created a single tabe and created and deleted the fields I required. This worked until eventually I hit a 3190 error "Too Many Fields Defined". This is due to the internal field counter for the table not being reduced by 1 for each field deleted. I have not been able to find a way to reset this counter via code and a Compact does not remove this problem. The only method appears to be to copy the data to a new table, but this runs straight back into the creating and deleteing tables problem mentioned earlier.
Something that should be mentioned is that the number of image codes is dynamic as new hardware generally means a new image. Therefore I cannot create a static table with fields for all the image codes as the number of codes is subject to fairly regular change.
So I am thinking...
1. I need to find an alternative method...
2. Am I completely missing the point and could I do this easily using a query? (If so, please show me how! I can't think quite how to make a query work)
3. There is a way round it and I haven't found it yet. (I'd really like to avoid having to compact the database as much as possible)
4 There isn't a way in access, what I'm trying to do is beyond it's scope (I doubt this!)
So any insight would be much appreciated
Cheers,
Matt
