I'm working with two different Data Bases. One is just an Access Table and the other is a list on SharePoint.
I'm trying to take the SharePoint list, run down one field and take each part number and compare it to the Access table to get the description and then place it on the SharePoint list.
I can use code to read and write to an Access table all day using
The problem I'm having is doing the same for the table or list on SharePoint.
I tried 10 different variation of the path but just cant seem to get it to work. I'm told every time that the file path does not exist.
Now I can link to the SharePoint table with no problem. I use the same path to try and write to it but no luck.
So I ditched that effort and tried to just write to the linked file using SQL statements. And that works just fine if I am just trying to update one record at a time.
But when I put that same statement in a loop for the just over 1,000 parts, I get a Run-time error '3734'; "The database has been placed in a state by user 'Admin' on machine xxxxxx that prevents it from being opened or locked."
And this error is not constant. Sometimes I can close out the program and try again and it works.
So I guess I'm having two issues. I don't care which way works as long as one of them do. However I would prefer to be able to link to the table verses using SQL statements.
Worst case, I will just bring the table down from SharePoint, get the descriptions in there and then put it back but it looks as though there is going to be a lot more SharePoint work in my future so I would like to get this figured out.
I'm trying to take the SharePoint list, run down one field and take each part number and compare it to the Access table to get the description and then place it on the SharePoint list.
I can use code to read and write to an Access table all day using
Code:
Public Const BackEndDBPath = "[URL="file://\\rstaz-123\CSASDataBase.accdb"]\\rstaz-123\CSASDataBase.accdb[/URL]"
Set db1 = OpenDatabase(BackEndDBPath)
Set rst1 = db1.OpenRecordset("PartMaster", dbOpenDynaset)The problem I'm having is doing the same for the table or list on SharePoint.
I tried 10 different variation of the path but just cant seem to get it to work. I'm told every time that the file path does not exist.
Now I can link to the SharePoint table with no problem. I use the same path to try and write to it but no luck.
So I ditched that effort and tried to just write to the linked file using SQL statements. And that works just fine if I am just trying to update one record at a time.
Code:
'Create the SQL statment to place the description on CM DB
strSQL = "Update Docs " & _
"Set Title = """ & PartDescription & """ " & _
"WHERE [Name] = """ & PartNumber & """"
'run the statment
CurrentDb.Execute strSQL, dbFailOnErrorBut when I put that same statement in a loop for the just over 1,000 parts, I get a Run-time error '3734'; "The database has been placed in a state by user 'Admin' on machine xxxxxx that prevents it from being opened or locked."
And this error is not constant. Sometimes I can close out the program and try again and it works.
So I guess I'm having two issues. I don't care which way works as long as one of them do. However I would prefer to be able to link to the table verses using SQL statements.
Worst case, I will just bring the table down from SharePoint, get the descriptions in there and then put it back but it looks as though there is going to be a lot more SharePoint work in my future so I would like to get this figured out.
