CODEWITHSUNDEEP
databasesharepoint
MDeck
MDeck

Reputation: 61

Access Database Linked to Sharepoint Tables

I have been working on a fairly complex access database for a group at work. The database was designed entirely in Access 2010 and working beautifully. I then pushed all the tables to SharePoint 2013 and linked them back to the database. I am now getting an error when running a section of code with an update query. It is saying it is unable to update all of the records in the update query due to a lock violation. I have checked and versioning is turned off in SharePoint. The database is built so that a user can search a table for a record match to what they need to enter, and "copy" an old record into the new record they are creating. With this design, both the form and the subform are pulling from the same table and I think that may be the issue, but it worked fine in Access prior to pushing the table to SharePoint. Does anyone have any ideas on how I can fix this? Thank you!

Here is the code that I think is causing the issue. That particular error doesn't give me the option to debug so I don't know for sure. Private Sub Command165_Click()

Dim SQL As String

Me.Dirty = False

SQL = "UPDATE [Cross_Border_Grid_Table]" _
          & "SET [Export Country]='" & Me.[Export Country] & "', [Export State]='" & Me.[Export State] & "', [Export City and/or Providence]='" & Me.[Export City and/or Providence] & "', [Import Country]='" & Me.[Import Country] & "', [Import State]='" & Me.[Import State] & "', [Import City and/or Providence]='" & Me.[Import City and/or Providence] & "', " _
          & "[Shipment Type]='" & Me.[Shipment Type] & "', [Material Category]='" & Me.[Material Category] & "', [Sub Category]='" & Me.[Sub Category] & "', [Specific Material]='" & Me.[Specific Material] & "', [Transgenic/ Conventional]='" & Me.[Transgenic/ Conventional] & "', [Intended Use]='" & Me.[Intended Use] & "', " _
          & "[Specific Use]='" & Me.[Specific Use] & "', [Material Common Name]='" & Me.[Material Common Name] & "', [Scientific Name]='" & Me.[Scientific Name] & "', [Event]='" & Me.[Event] & "', [Material Condition]='" & Me.[Material Condition] & "', [Permit Required]='" & Me.[Permit Required] & "', " _
          & "[Phytosanitary Certificate Required]='" & Me.[Phytosanitary Certificate Required] & "', [Agency Labels]='" & Me.[Agency Labels] & "',  [Other Requirements]='" & Me.[Other Requirements] & "', [Data Modified]='" &  Me.[Data Modified] & "', [RegCode]='" & Me.[RegCode] & "' WHERE [ID] = " &  [Forms]![New_Shipment_Home_frm]![Text105]

DoCmd.RunSQL SQL

Upvotes: 0

Views: 125

Answers (1)

MDeck
MDeck

Reputation: 61

In case anyone else has a similar issue, I was able to rewrite the code to create a recordset, and using a recordset was able to set the recordset property to .LockEdits = False

I hope this helps someone else at some point since there seems to be many that have had an issue and no resolution!

Upvotes: 0

Related Questions