I don't appear to have too much luck eliciting a response, but I'll keep trying. Here are two questions that I am wrestling with right now:
1. I am having to redefine data types for a few fields across several tables; unfortunately, some of them are key fields. I have first removed all relationships from the relationship window, but still keep getting the window message below:
"You can't change the data type or field size of this field; it is part of one or more relationships.
If you want to change the data type of this field, first delete its relationships in the Relationship window".
Well, I have, and this message still keeps popping up.
2. I intend to populate one of the tables with outside data using an Excel spreadsheet. Is an Upload preferable to a Link? I am concerned with future application size, since the upload data will contain monthly sales information for several products. If the maintenance of the Excel spreadsheet can be assured, would a Link be a better option?
1. Try creating copies of the tables and then dropping the original tables and renaming your copies. Sometimes with Access don't bother to understand why and just use brute force to fix the problem.
2. That depends if your Excel Spreadsheet is located over a network or local to your Access database. And will be there be a lot of traffic retreiving and updating data or not. Also the size of the data is a factor. If the data is not to big and not over a network you can link otherwise upload. You can try both variations and see which one performs better.
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!
Yes, the Excel spreadsheet will be over the network, in a shared drive. The Excel data will be a monthly download from SAP - the monthly download itself is not expected to be very big, but incrementally it can or will be (say, in a year's time).
As for trying out both, I did: the Link didn't work, for some reason. Surprisingly though, the upload did. Is the "100 MB" file size I keep hearing about as an Access critical limit true?
As for the other problem about renaming key fields, it seems the message itself is possibly wrong. Relationships are only one factor and persisting error messages pointing to this could actually be referring to any expressions that involve a deleted field that is not a key field. I found this out after probing every one of my queries to see where the problem lay. At least, I think this was the problem - after I took care of it, the problem disappeared.