I am using Access 2010 linked to a SharePoint 2010 list. This list has less than 1600 records.
I apologize in advance for posting such a long post. However, I do not know how to provide the information in a more concise way, so here goes:
I am developing a VBA module that is used to open a recordset of records from a linked table. This linked table is located in another Access database file located on a shared network drive. This recordset is developed by linking the two tables together with an equal join in an SQL statement so that only records in both tables will be in the recordset.
The VBA code is designed to iterate through a DAO recordset from the SQL statement updating a few of the fields in the SharePoint list. Both linked tables are linked correctly and are responsive. There is no issue with opening either of the tables.
The reason for iterating through the DAO recordset to update the SharePoint list is that there are criteria that must be evaluated for each record and values assigned for the update of each record.
The code is running without error, however, upon completion of running the code, and opening the SharePoint list, it was discovered that one field is being set to null. The field in question addressed and is not d\even found in any statement of any kind in the code.
I have tried many different things to determine what is causing the field in question to be set to null, but I have not been able to find any cause for this field to be set to null.
My explanation of what happens when the code is run will seem to be totally impossible, but please believe me when I say that I have been able to recreate this activity over and over again, without being able to come up with an explanation.
After many attempts to trace the value of the field and when it changes, I found that if I run the VBA code which has a nested IF statement for each month of the year where values in the DAO recordset are evaluated to assign value to update the field, when the first nested IF statement is completed for the first month, the value of this field is already set to null. However, if I define a string variable and just prior to the execution of the nested IF statement I assign the value of the field in question to that variable the field is not set to null. I do not have to re-assign the value to the field, it just retains its value. If I then execute the next nested IF statement, the value of the field is suddenly set to null. However, if I add the same assignment statement that assigns the value of the field to the variable just prior to each of nested IF statements, the code will run through all of the nested IF statements and the value of the field in question will not be set to null but will retain its value.
The curious thing about this whole thing is that if I define a string variable and at the start of the code within my For Loop and assign the value from the field in question to this string variable, my variable will hold the value from the field and then I can assign the value from my variable to the field just before the update of the DAO recordset. Using this method, I have been able to retain the appropriate value for each record and make my update process work as designed. However, this does not explain how or why the field that is not being mentioned in code is being changed to null.
If anyone has any thoughts or ideas or if you would like more information about this mysterious situation, I would be happy to share more details about my testing process and even my code.
I would just like to understand why this is happening with this linked SharePoint list. I have used linked table in Access for years and I have never seen any field get changed that was not specifically modified by the VBA code.
Thanks for taking the time to look at this and to read through this long explanation.
I apologize in advance for posting such a long post. However, I do not know how to provide the information in a more concise way, so here goes:
I am developing a VBA module that is used to open a recordset of records from a linked table. This linked table is located in another Access database file located on a shared network drive. This recordset is developed by linking the two tables together with an equal join in an SQL statement so that only records in both tables will be in the recordset.
The VBA code is designed to iterate through a DAO recordset from the SQL statement updating a few of the fields in the SharePoint list. Both linked tables are linked correctly and are responsive. There is no issue with opening either of the tables.
The reason for iterating through the DAO recordset to update the SharePoint list is that there are criteria that must be evaluated for each record and values assigned for the update of each record.
The code is running without error, however, upon completion of running the code, and opening the SharePoint list, it was discovered that one field is being set to null. The field in question addressed and is not d\even found in any statement of any kind in the code.
I have tried many different things to determine what is causing the field in question to be set to null, but I have not been able to find any cause for this field to be set to null.
My explanation of what happens when the code is run will seem to be totally impossible, but please believe me when I say that I have been able to recreate this activity over and over again, without being able to come up with an explanation.
After many attempts to trace the value of the field and when it changes, I found that if I run the VBA code which has a nested IF statement for each month of the year where values in the DAO recordset are evaluated to assign value to update the field, when the first nested IF statement is completed for the first month, the value of this field is already set to null. However, if I define a string variable and just prior to the execution of the nested IF statement I assign the value of the field in question to that variable the field is not set to null. I do not have to re-assign the value to the field, it just retains its value. If I then execute the next nested IF statement, the value of the field is suddenly set to null. However, if I add the same assignment statement that assigns the value of the field to the variable just prior to each of nested IF statements, the code will run through all of the nested IF statements and the value of the field in question will not be set to null but will retain its value.
The curious thing about this whole thing is that if I define a string variable and at the start of the code within my For Loop and assign the value from the field in question to this string variable, my variable will hold the value from the field and then I can assign the value from my variable to the field just before the update of the DAO recordset. Using this method, I have been able to retain the appropriate value for each record and make my update process work as designed. However, this does not explain how or why the field that is not being mentioned in code is being changed to null.
If anyone has any thoughts or ideas or if you would like more information about this mysterious situation, I would be happy to share more details about my testing process and even my code.
I would just like to understand why this is happening with this linked SharePoint list. I have used linked table in Access for years and I have never seen any field get changed that was not specifically modified by the VBA code.
Thanks for taking the time to look at this and to read through this long explanation.
