Database and Networking ???

[doctrin13th]

Hello guys!

I'm just took my 1st step to networking, and database handling and I want to know the how's, do's and don'ts, briefly.

Can somebody upload a very simple and basic example project from which I can/may learn? Please.

This example will do:

1 database (MS Access) - just 1 table with 2 fields(idnumber, membername)
1 form
1 combobox - cboID for listing available IDnumber
1 textbox - txtName for membername
2 buttons - cmdUpdate for update, cmdExit for exit
The database contain 1 or 2 records already

The scenario:
On form load textboxes contain "".
When the user select an IDnumber from the combobox, txtName will be filled with the name of the owner of the idnumber
When the user click update button, the entry will be updated of course
When the user click exit, guess what?( )

Now that, I can do. But how can I implement that on two PCs on a network. Just 1 PC has the database. Where 1 PC serves the database and only can update it. While PC 2 can only view.

Well, I guess someone make an example project out of that in less than 5 minutes. If you have time, I would greatly appreciate it.

Thanks guys!

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

Pretty much everything you need is in our Database Development FAQs/Tutorials (at the top of this forum), particularly the "Classic VB: ADO" section.

But how can I implement that on two PCs on a network. Just 1 PC has the database. Where 1 PC serves the database and only can update it. While PC 2 can only view.

Just change the database path in the connection string, and disable the editing features of your program.

[RunsWithScissors]

DO you ALREADY have the two PC's set up on a network, or is that part of what you need to know how to do? If so, are you talking about a local workgroup such as I have here at my HOME (no Real "Server", two pc's connected via a netwrk router), or are you talking about a dedicated Server like we have at my OFFICE, where the IT department has a dedicated server, to which we all connect?

In a way, it should not matter for what you are trying to do. But if you are in the latter situatiuon, you may or may not need to involve your netwrok administrator. In the former situation, you probably ARE the network administrator.

More info?

[doctrin13th]

Just change the database path in the connection string, and disable the editing features of your program.

What to do you mean disabling the editing features? How can I do that?

DO you ALREADY have the two PC's set up on a network, or is that part of what you need to know how to do? If so, are you talking about a local workgroup such as I have here at my HOME (no Real "Server", two pc's connected via a netwrk router), or are you talking about a dedicated Server like we have at my OFFICE, where the IT department has a dedicated server, to which we all connect?

I have already a home network. a local workgroup setup.

[si_the_geek]

What to do you mean disabling the editing features? How can I do that?

It depends on how you enable them in the first place.

If there is a "Save" button, you could simply disable it (or hide it).

If you are doing this as two separate programs (one for the updater, one for the viewer), just don't put any editing code into the viewer program.

[doctrin13th]

what if two PCs will be able to access and modify records in one database file? Are there any issues? maybe regarding to read-only(because it is in use) matters or the like?

[si_the_geek]

There could be issues if you have inappropriate code. If you are worried about it, post your code.

[RunsWithScissors]

I think he is worrying about record-locking and such.

Access, and other RDBMS are designed to be accessed by multiple users.

Depending on how you are setting this up, you probably don't need to worry much about concurrent access to the data.

Create one .mdb on the computer you elect to use as the "data-server", create your table set in that, and nothing else. allow THIS .mdb file to serve as your data-container.

Then, create an .mdb "Front-end" that will be used to access the data. This .mdb file will conatian all the forms, code, queries, report definitions your users need, but none of the tables.

Then you can either:

A. Use access "Linked tables" to create your connection, which will give you access to your table data in a familiar manner; you will have table proxies within the front end, and can manipulate them as if the tables were local.

B. Use a programmatic connection via ADODB in your code to connect at runtime on an "as-needed" basis.

C. Note that you can use code and ADODB even on the linked table set through the "CurrentProject.Connection" Property in your code.

In either case, Access is designed to manage the concurrent user access issue for you. If you do this programmatically, you will have slightly more control (and more responsibility for managing your connections and record locking).

I recommend learning and using the programmatic approach. WHile the "Linked Tables" capability is easier to implement, it also incurrs more overhead, and puts the tables (and raw data) that much closer to the user's direct access (they should really only access the data through whatever front-side strategy you have implemented, giving you control over what they can do, how and WHEN they can do it).

Using linked tables requires that you manage the linkage. If the back-end gets moved or re-named, you have to make sure to use the linked table manager to update the connection mappings.

You have to maintain these things in your code as well (if the back-end gets moved or renamed, you have to modify your connection string), but you can set the connection string up as a constand in a code module, so that you only need to change it in one place.

ALl that said, if you are using VBA code to build your forms and logic, do yourself a couple favors:

Use ADODB, not DAO as your data-access model.

Use OLEDB, NOT ODBC, as your connection model.

Hope all that helped . . .

[si_the_geek]

As it is within VB6, I don't think the "Front end" database is worthwhile.


As far as concurrent users go, unfortunately Access isn't particularly good (due to it being file-based rather than server-based), but it is generally OK if there are less than 5 to 10 users working with it at the same time.

[RunsWithScissors]

As it is within VB6, I don't think the "Front end" database is worthwhile.

Ooops. Missed that little fact . . . Ignore my bit about the "Front-end" database then, although I didn't see anymention of vb6 in the op's post, so I assumed he was dealing with Access itself as front and backside . . .

As far as concurrent users go, unfortunately Access isn't particularly good (due to it being file-based rather than server-based), but it is generally OK if there are less than 5 to 10 users working with it at the same time.

Couldn't agree more.

[doctrin13th]

Let me set an example:

PC1 and PC2 are simultaneously working. By the way, the database is in PC1. What if both PCs change the record in that database, and at the same time, simultaneously updates it. Or maybe both PCs at the same time perform a query on that database, but different conditions(i.e. PC1 asked for lastnames='Cruz' while PC2 asked for lastname='Pineda').. What issue/s will/might arise?

[RunsWithScissors]

THis is what I was talking about. Access (or any other RDBMS) is set up to handle this scenario (although at a certain level, many are of the opinion that Access' record-locking mechanism is somewhat lacking).

Again, some of this will depend on how you are using your front-end. However, generally speaking, in order to UPDATE a record, the record is locked until the update is complete. The odds of Two PC's simultaneously attempting to UPDATE the same record (and by simultaneously, we are talking milliseconds or less) are slim. Usually, one of the pc' sis going to get there FIRST, and then the record wil be locked until the update is complete.

Which is NOT to say that there aren't issues within Access around this. WHile most db's will handle the majority of concurrent access issues (in access' case, at worst, (usually) by putting up the message
"The record has been changes by another user. DO you want to overwrite with your changes, or save your changes to the clipboard until you review the changes made by the other user?". This is irritating when it happens.

Concurrent access and record-locking issues are a fact of Multi-user Database life. If you want to learn more about this, look into the following items:
1. Database record Locks
2. Dirty Reads
3.Non-Repeatable reads
4. Phantoms
5. Lost Updates
6. Transaction Isolation levels

Some of the items on that list are explained very nicely in a book by Robert Vieira titles "Professional SQL Server Programming" While this is a book about SQL server, many of the general principles are universal. I highly recommend it.

THe short version is, deploying your Access db on a netwerk as you describe will result in no more or less concurrency issues than anyone else with a networked Access Db. Try it out, and see what happens.

In your code, of course, setting appropriate locks at the right time is one measure you can take. If you are using VB6 and ADO, then try Connecting with adOpenKeySet/adLockOptimistic during inserts and Updates. Use the default cursor when performing simple SELECTs.

If you do a little research within Access help (open from within the VBA CODE window, not from within Acces' Application window) and search for ADO, Optimistic locking, record locks, Etc. you wil find a lot of info . . .

Hope that helped. Record locking is a complicated issue. It is cool that you recognized the issue; however, be aware that RDBMS' are designed to mitigate at least SOME of the pain . . .

[si_the_geek]

RunsWithScissors gave good info, but here's a simplified version:

What if both PCs change the record in that database, and at the same time, simultaneously updates it.

If you are using a recordset to do the editing, one of the users will get an error on the recordset.Update statement, which you can deal with as you see fit.

If you are using SQL statements, there will be no issue - whichever change is done last (they don't actually run at exactly the same time) will be the one that is saved in the database.

Or maybe both PCs at the same time perform a query on that database, but different conditions(i.e. PC1 asked for lastnames='Cruz' while PC2 asked for lastname='Pineda')..

They are completely unrelated, there will be no issue at all.

There also wont be an issue if one user is editing data, and another user reads it (they will see the data as it was when their request reached the database).

[RunsWithScissors]

Si_the_geek:

There also wont be an issue if one user is editing data, and another user reads it (they will see the data as it was when their request reached the database).

THIS is one of the things which (for me) is the most insideous of problems, though, for the very fact that, as Si says, you won't have a TECHNICAL issue, but the issue is there nonetheless. At least ONE of the users will be looking at old/outdated information.

If User A opens John Smith's health account record in order to add a note that John Smith is allergic to a certain medication, and slightly after that, (as in, half a second) User B opens John Smith's health record from another site (say, the intensive care unit, where John Smith has just been admitted, unconsious due to an accident) to veiw potential medication allergies, user B will not see the changes user A is about to make, as they have not yet been committed (they may not even have been MADE yet-user A MAy have had a phone call come in just as they opened the edit screen).

The way around this scenario is to employ a lock from the moment User A opens the record. However, imagine what would happen then. What if User A went to lunch witht he edit screen open, thinking the change would wait until they returned, not aware that no one else could access the patient's record until they were done.

In the real world, a mission-critical system like that would probably have a more advanced (or strict ) record management system that described above.

As I said, technically there will be no issue; be every now and the you WILL run into the situation where one user begins an update, another user begins a read. The first user updates, adn then, either:

A. User b may be making a decision based upon outdated information, OR:

B. User be may have been proceed to update another portion of the record (a different field). The way MANY Front-ends are set up, the Save operation will result in updating the ENTIRE record with the changes as shown in User B's edit window. This will include the field(s) changed by user B, as well as the values loaded during the initial read in the fields which have been updated and committed by User A. User A's changes will now be overwritten with the original values.

I have probably muffed some of this info. This stuff is HARD to explain in a clear context. My understanding is that any locking strategy involves a series of trade-offs, and that there is no one-size fits all solution to the issues I enumerated previously.

In the case of a two-station access system, you should have no problems of much significance. However, understanding how all this works is a real bonus as you go forward. That you recognized the potential issue on your own shows a good forward-thinking approach!

Note that I am still learning this stuff. Part of why I attempt to explain is to both enhance my own understanding, and to be corrected where I have it wrong by people like Si . . . He's right. I did make it more complicated . . .

[si_the_geek]

I have probably muffed some of this info. This stuff is HARD to explain in a clear context.

It all looks right to me.

My understanding is that any locking strategy involves a series of trade-offs, and that there is no one-size fits all solution to the issues I enumerated previously.

Absolutely.