0

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
  • Just FYI, I removed a bunch of code from whats shown that checks for a duplicate before I AddNew, and I'm importing about 15 out of 56 fields from this excel spreadsheet, the whole thing is way too much to paste in here. Also, feel free to tell me I'm approaching this completely wrong. The only formal Access training I've had was on Access 2003. So I'm sure I'm using predicated methods still. I've only updated my skills as I find things that no longer work. – MGaddict Apr 21 '17 at 18:40
  • I'm not really an 'Access' person, but I was searching the Internet for solutions to your problem and found this (which may or may not be helpful) - stackoverflow.com/a/156199 – Scott Hodgin Apr 24 '17 at 11:16
  • Thank Scott. That message was very old and the links didn't work however it did get me to double check that I was closing everything correctly. I wasn't closing the actual database, which I'm not sure I've ever done. But doing that didn't seem to help. Something is still keeping the database locked. – MGaddict Apr 24 '17 at 18:27
0

To make a long story short... Run Compact and Repair. I thought I had already attempted that. Perhaps I had before I read Scott's post. But after fixing that and then Running Compact and Repair, everything is working exactly the way it should be. I don't typically have to do that for the first time on a database until I've done a LOT more work, I'm guessing because this Database has close to twenty thousand entries, and most of the ones I work with have less then 1000, it makes a huge difference. Lesson learned. Always learning, always growing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.