[RESOLVED] Editing and adding fields to Access database over LAN

Hi!

I have made a program that is able to filter, edit, add and delete rows to a database. On my local computer all works fine. But when I share the folder and open the program in another computer on my network it is not able to edit or add new rows to database. Filtering is working fine.

How can I overcome this?
This program is meant to work on multiple computers and use the same database.

Do I have to change the connectionsting to full path or....?

If the connection string wasn't correct then you wouldn't be able to filter because you wouldn't be able to retrieve because you wouldn't be able to connect. Maybe if you were to explain what actually happens we might be able to diagnose the issue. First of all, how are you retrieving the data and how are you trying to save it? If you're using a data adapter and calling Update then there are only three possibilities:

1. It succeeds and returns zero.
2. It succeeds and returns a non-zero value.
3. It fails and throws an exception.

Which is it?

Sounds like a permission issue to me (by the way you phrased your question).

So if I have you correct...
You made an application that could read, write and modify successfully. After you moved the database your application could only read?

To me it sounds like you don't have the proper permissions set up to this shared directory for these other computers accessing it. The connection string also must point to the exact database file you want to access. So if you have the drive/directory mapped as X on one computer, and Y on another. Then that will have to change. I would suggest making a form where the path of the database can be dynamically changed for each installation.

But I agree with jmcilhinney, you should capture some exceptions as they will let us know if this is a permission issue or something entirely different.

One unrelated thing I would warn you about: Access can be flaky when used over a LAN. The issue is not constant, and you may go for years without encountering any problem, but if the network fails while you have the database open over a LAN, you may permanently destroy the database. Therefore, be very careful about backing up the data often if you are relying on Access across a network. This issue may no longer exist, too, as Access has changed considerably over the years, and I haven't kept up with the newer versions.

Ok, I try to clarify things a little bit. I have one computer where my program is installed. The database that is used by my program is in the same directory as my program.

Then I shared this folder where my program and my database are located to local area network. Then I accessed this folder from another computer on my LAN network and made a shortcut of my program to that computers desktop.

Launched the program, data is nicely inserted to datagridview. I can filter data by sql statements. Now I open another form in my program which is used for editing: edit the data, hit "Save" and the messagebox pops up saying: "Can't connect to database!" (I set this exception when I was coding my program). Same thing is with adding new record form. I use datasets, adapters and bindingsources that wizard has set up.

Code for saving after editing:

Code:

---

Try
       
            'Me.Validate()
            Me.TableBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TableDataSet)
            MsgBox("Saved successfully!", MsgBoxStyle.Information, "Catalogue")
       
        Catch ex As Exception
        MsgBox("Can't connect to database!", MsgBoxStyle.Information, "Catalogue")
        End Try

---

Code for adding new row:

Code:

---

Try
       

            MainForm.TableAdapter.Insert(Me.ComboProfiil.Text, Me.PikkusTextBox.Text)

            MainForm.TableAdapter.Fill(MainForm.TableDataSet.Table)
            MsgBox("New item saved successfully!", MsgBoxStyle.Information, "Catalogue")
Catch ex As Exception
        MsgBox("Can't connect to database!", MsgBoxStyle.Exclamation, "Catalogue")

        End Try

---

Yeah, but you show the same message for ALL exceptions. That code could fail for any number of other reasons, and right now you don't know quite why. At the very least, change your messagebox to show ex.Message in addition to whatever else you want, because that will tell you a whole lot more about what the problem really is. In fact, you might post ex.Message here.

Quote:

---

Originally Posted by Shaggy Hiker

Yeah, but you show the same message for ALL exceptions. That code could fail for any number of other reasons, and right now you don't know quite why. At the very least, change your messagebox to show ex.Message in addition to whatever else you want, because that will tell you a whole lot more about what the problem really is. In fact, you might post ex.Message here.

---

Good point!

I'll do just that.

Hi!

Reporting back the "ex.Message" : Operation must use an updateable query.

Weird that it doesn't give that message when I run the program directly from my local computer.

Did some research on this error and came across with this little article. Thread solved on my behalf. :)


The unbelievably cryptic Operation Must Use An Updateable Query error is the bane of developers who are just starting out with Access and ASP.NET. You've done your code, plopped your database file in the App_Data folder (or at least, you should have done), and try to run a page that INSERTs or UPDATEs records, and it all stops dead. This brief article explains the cause of the error, and the steps required to stop it recurring.



When a Jet 4.0 database (the actual type of database represented by your "Access" mdb file) is deployed in a multi-user environment, an .ldb file is created whenever the database is opened. The .ldb file contains details which include who has opened the file, and primarily serves to prevent opened records being written to by another user.

In the context of an ASP.NET application, who the "user" is will depend on the platform: for XP Pro machines, the user is the ASPNET account. On Windows Server 2003, 2008 and Vista, it is the NETWORK SERVICE account. However, if you have ASP.NET Impersonation enabled, the default user account will be IUSR_machinename, or whichever account you have applied. If you are unsure which account your ASP.NET application is running under, Environment.UserName will return it. To be able to create, write to and delete the required .ldb file, the relevant user needs MODIFY permissions on the folder that the .mdb file is in.

To set this permission, right click on the App_Data folder (or whichever other folder you have put the mdb file in) and select Properties. Look for the Security tab. If you can't see it, you need to go to My Computer, then click Tools and choose Folder Options.... then click the View tab. Scroll to the bottom and uncheck "Use simple file sharing (recommended)". Back to the Security tab, you need to add the relevant account to the Group or User Names box. Click Add.... then click Advanced, then Find Now. The appropriate account should be listed. Double click it to add it to the Group or User Names box, then check the Modify option in the permissions. That's it. You are done.

Note: this fix will also solve "The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data" errors.