I wrote some VBA to import an excel spreadsheet easily into a table in my database. This way, the user just clicks a button on a form, and answers some questions about the file to be imported. The problem is that after the import is completed, I try to modify anything else in the database and I'm presented with the message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." I then have to exit the database and reopen it to make any changes. I've done this before without problems on other versions of Access. So I don't know if I have a typo someplace, or something has changed from Access 2013 to 2016 that's causing the problem. Currently, the Database is located on my local machine. Ultimately, this will be moved to a SharePoint site and split into 2 access files. I don't want to do that until I have it mostly working because I don't have anyplace I can upload to SharePoint without a lot of people having access to it.
Private Sub ImportNewTB_Click() On Error GoTo ImportNewTB_Click_Err 'If an error occurs anywhere along the way, make sure you still clean up the memory before quiting Dim OwssvrFile As DAO.Database 'This is the open connection to the file Dim OwssvrInfo As DAO.Recordset 'This is the recordset for the teachers information Dim fileName As String 'This is the name of the file being opened Dim dbs As DAO.Database Dim CurrentTBDB As DAO.Recordset 'This is to make a connection to our current table 'Dim ExcelHdrs(0 To 20) As Variant 'This is an Array with the headers from the Excel file. Dim numRecords As Integer Dim WksName As String Dim TimeStamp As Date fileName = getOpenFile() 'Use the function I built in the Module If fileName = "" Then GoTo ImportNewTB_Click_Exit 'If they didn't select anything, then just give up on life and exit WksName = InputBox("Enter the name of the worksheet: ", "Worksheet Name", "owssvr") numRecords = InputBox("Enter the number of records in the Worksheet: ", "Num Records", 2412) WksName = WksName & "$A1:BE" & numRecords + 1 'Once we have a real file, open it already Set OwssvrFile = OpenDatabase(fileName, False, True, "Excel 12.0; HDR=YES;") 'Create Recordset from the excel file. Set OwssvrInfo = OwssvrFile.OpenRecordset(WksName) OwssvrInfo.MoveFirst 'Goto the first line of the recordset Set dbs = CurrentDb Set CurrentTBDB = dbs.OpenRecordset("SELECT * FROM CurrentTB") TimeStamp = Now() Do With CurrentTBDB .AddNew .Fields!EntryDate = TimeStamp .Fields!ProjectName = OwssvrInfo.Fields(0) .Update End With OwssvrInfo.MoveNext 'All of that for entry 1, only 2000 more lines to go Loop Until OwssvrInfo.EOF ' Tidy up, This closes everything out and releases the memory ImportNewTB_Click_Exit: On Error Resume Next 'Basically this says, if there's an error, I don't care, do this anyway MsgBox "Input Complete!" OwssvrInfo.Close OwssvrFile.Close CurrentTBDB.Close Set CurrentTBDB = Nothing Set dbs = Nothing Set OwssvrInfo = Nothing Set OwssvrFile = Nothing Exit Sub ImportNewTB_Click_Err: 'This produces an error message if one exists MsgBox Err.Number & " " & Err.Description, vbCritical, "Error!" Resume ImportNewTB_Click_Exit 'Make sure we still clean up before leaving End Sub