The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Question

Hi, I have created a macro file that will store/upload the data to the database. Users are sitting across the country. I have given a separate folder for each user and kept the macro file. I will explain with a below example.

A is Admin(me). B , C D are the users. 

Separate folders in the server location has been created for B, C & D users by Admin. 

Folder created for Access database in the server location and B,C & D are have the full access to their respective folders and Access database folder.

User B is updating some entries in the Excel. After updating B needs to click a button to run the macro. Now my macro will push those information to MS Access database.

Here is the problem.

After clicking the button, some times info uploaded to the database.

First Problem : Some time it shows below error and the user input not uploading the data to the database. Instead of I can see come blank rows added in the access database.

Second Problem :

I am using below VBA code to insert the data to my access database.

Sub insert_data(tablename As String, wkb As Workbook, rng As Range)

    Application.ScreenUpdating = False

    Dim cnn As Object

    Dim workbookname As String

    Dim sqlstring As String

    Dim rngtoinsert As String

    Dim dbpath As String

    Dim columnnames As String

    Dim columncounter As Integer

    Set cnn = CreateObject("ADODB.Connection")

    dbpath = "\IPAddress\Database\Data_Repository.accdb"

    workbookname = wkb.FullName

    rngtoinsert = "[" & rng.Parent.Name & "$" & rng.Address(0, 0) & "]"

    For columncounter = 1 To rng.Columns.Count

        columnnames = columnnames & "[" & rng.Cells(1, columncounter).Value & "],"

    Next

    columnnames = Left(columnnames, Len(columnnames) - 1)

    cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath

    cnn.Open cnn

    sqlstring = "INSERT INTO " & tablename & "(" & columnnames & ") "

    sqlstring = sqlstring & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & workbookname & "]." & rngtoinsert

    cnn.Execute sqlstring

    cnn.Close

End Sub

But when cnn.Execute sqlstringis running the copy of the same Macro workbook is opening as read-only. 

I am not sure both the problem are coming for one reason.

I am using office 365 including MS Access package.

Can anyone help me out?

Regards,

Lokesh

Answer 1

How are your "users across the country" running the Access application? It looks like you're trying to do this through a VPN, or something along those lines, since you're pointing to "\IPAddress\Database\Data_Repository.accdb".

You cannot reliably run your Access database in this type of environment. The correct way to support remote users is through a Citrix or RDS (Terminal Server) environment. Your errors, along with the blank records, is likely occurring due to this type of architecture.

So the first thing to do is provide a more suitable environment for your Access application.

Answer 2

Hi, my users are not directly accessing the Access Database. Through VBA code which I mentioned above they are indirectly accessing to upload their data. 2 out of 10 times it's not uploading to the database. In that two times blank rows are added. If any environmental issues i might be getting the error 10 out of 10 times right. Thats what the confusion. I have no clue for this.

Answer 3

Are you doing this through an Access database? If so, and if you're calling that code, then you're "directly accessing the Access Database" using VBA Code when you make an ADO connection to it.

Your error messages seems to refer to an .xls file. If you're trying to do this, then remember only one person can work with an Excel file. Are you sure you are the only person working with that Excel file? From my experience, you really cannot tell unless the file is on your machine, and you (and you alone) have access to that file.

Answer 4

Here Front end is Excel... Backend is MS Access. Macro written in the Excel.

User can't even see the MS access database.

They can can update only in excel.. Everything will be captured in Access through Excel VBA code.

And My users are having access only to their folder and they can access the file which is available in their folder.

Ex: For A - there is a folder available called A. In that have kept the macro file. Same for B. A can't access B's folder or files. Below is the quick workflow.

Answer 5

Again - your error is referring to some Excel file named Tracker_V1.3.xls. That file is apparently locked by some other user or process.

If that Excel file the "frontend" for your Access database? 

The concept of not using Access across a WAN and/or VPN still stands, regardless of the FE you're using to connect to it. Access is not a good choice for this type of environment, and you'll find it gives more troubles than it's worth. If all you're doing is storing data in the Access database, then consider switching over to a server-type database (like SQL Server Express, which is free) and use that instead of your .accdb format.