Multi-computer access to program's data
-
copec wrote:
Can I develop the first version in such a way that I allow myself the ability to later "plug in" multi-client ability with a minimum of rewriting, etc.?
Yes, hence the suggestion to start with a new project in Microsoft Access[^]. This is a local database that can easily be migrated[^] to it's bigger SQL-brother - moving the data to the server, and letting the reports fetch their data from there. And you're welcome, off course :)
I must not have mentioned it already: I have had this db runnning in Access for over three years and have now decided to write it into a stand alone program. I'm researching and reading and trying to figure out what components and tools to use. I've narrowed the language to VB.NET or C# with a WPF gui. I think the only main part left to narrow and decide is which SQL db to use. That's why I'm asking about the possibility/feasibility of using a local-only db for now but writing the program, etc, in preparation for the multi-client capable db later. Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later? Thanks again.
-
I must not have mentioned it already: I have had this db runnning in Access for over three years and have now decided to write it into a stand alone program. I'm researching and reading and trying to figure out what components and tools to use. I've narrowed the language to VB.NET or C# with a WPF gui. I think the only main part left to narrow and decide is which SQL db to use. That's why I'm asking about the possibility/feasibility of using a local-only db for now but writing the program, etc, in preparation for the multi-client capable db later. Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later? Thanks again.
copec wrote:
Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later?
Not quite; it's not as simple as throwing a switch. It is, however, quite easy to upsize. That wizard makes it a few-clicks operation to migrate your data to SQL. The way you'd talk to them is also very similar. When you want data from Microsoft Access, you'd typical run a query over a
connectionto fetch that data. With SQL Express/Server, you'd also run a SQL-query over aConnectionto fetch data. In both cases you'd be using aConnection-object to connect to the database, running a query and reading the results. The main difference is that Access is specialized in fetching/updating the data in a local database, or at least with not all-too-much clients. SQL Express is the larger brother, living on a server, always present while the computer runs, ready to update it's datastore :)Just introduced to 'Southern Comfort'
modified on Saturday, July 25, 2009 8:58 PM (not GMT?)
-
copec wrote:
Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later?
Not quite; it's not as simple as throwing a switch. It is, however, quite easy to upsize. That wizard makes it a few-clicks operation to migrate your data to SQL. The way you'd talk to them is also very similar. When you want data from Microsoft Access, you'd typical run a query over a
connectionto fetch that data. With SQL Express/Server, you'd also run a SQL-query over aConnectionto fetch data. In both cases you'd be using aConnection-object to connect to the database, running a query and reading the results. The main difference is that Access is specialized in fetching/updating the data in a local database, or at least with not all-too-much clients. SQL Express is the larger brother, living on a server, always present while the computer runs, ready to update it's datastore :)Just introduced to 'Southern Comfort'
modified on Saturday, July 25, 2009 8:58 PM (not GMT?)
-
Thanks. Given the similarity, does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I wouldn't state it like that, but that's what a lot of people did. Many of them started out with the combination Access/VB6 and have moved to SQL Server/C# in the meantime. Reading from the datastore will be roughly the same, but you might run into a concurrency-problem once a lot of people start meddling around with the data. You'd have to decide what happens when "user one" is changing the countryname of the Northpole to "New Southpole" and "user two" is (at the same time) changing this name to "Old Northpole". That's rather a logical problem that you'll encounter with any database. Most of them provide locking-mechanisms, even Access (to some degree). Some of the older applications have been converted to webapplications, utilizing ASP.NET/SQL (or PHP/MySQL). Those applications can often be used from every country, introducing differences in measurement (metric vs imperial), differences in the formatting of numbers and dates (and some locations on daylight saving time) and such.
copec wrote:
does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I'm not saying that it's not a concern, but that a lot of developers have faced these challenges before, and the problems that you're likely to encounter will probably be well-documented.
-
I wouldn't state it like that, but that's what a lot of people did. Many of them started out with the combination Access/VB6 and have moved to SQL Server/C# in the meantime. Reading from the datastore will be roughly the same, but you might run into a concurrency-problem once a lot of people start meddling around with the data. You'd have to decide what happens when "user one" is changing the countryname of the Northpole to "New Southpole" and "user two" is (at the same time) changing this name to "Old Northpole". That's rather a logical problem that you'll encounter with any database. Most of them provide locking-mechanisms, even Access (to some degree). Some of the older applications have been converted to webapplications, utilizing ASP.NET/SQL (or PHP/MySQL). Those applications can often be used from every country, introducing differences in measurement (metric vs imperial), differences in the formatting of numbers and dates (and some locations on daylight saving time) and such.
copec wrote:
does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I'm not saying that it's not a concern, but that a lot of developers have faced these challenges before, and the problems that you're likely to encounter will probably be well-documented.
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
-
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
copec wrote:
If so, the program won't open and gives the person a message
That might be a bit frustrating for the user. Especially if they don't know for how long they have to wait.
copec wrote:
it totally does away with the concurrent editing problem.
That's true :)
-
copec wrote:
If so, the program won't open and gives the person a message
That might be a bit frustrating for the user. Especially if they don't know for how long they have to wait.
copec wrote:
it totally does away with the concurrent editing problem.
That's true :)
Eddy Vluggen wrote:
Especially if they don't know for how long they have to wait.
The message would tell them that they have to wait until the other instance on the other computer is closed. It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
-
Eddy Vluggen wrote:
Especially if they don't know for how long they have to wait.
The message would tell them that they have to wait until the other instance on the other computer is closed. It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
copec wrote:
It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
The wife? Perhaps it's a good idea to put a password on it? :laugh: Well, it wouldn't be as much a problem as it would be in a full office. It would be frustrating if there's "some" co-worker using it, blocking you. But that's less of a problem at home.
-
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
copec wrote:
I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else
How are you going to do that? You would have to be able to check for what machines are attached to the network and for each machine check which processes it is running to see if it is running your program. What would happen if two users on different machines simultaneously start your program? This may not be a problem on a small home network, but if you go commercial it could be.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
-
copec wrote:
I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else
How are you going to do that? You would have to be able to check for what machines are attached to the network and for each machine check which processes it is running to see if it is running your program. What would happen if two users on different machines simultaneously start your program? This may not be a problem on a small home network, but if you go commercial it could be.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
riced wrote:
How are you going to do that?
Of course I don't know (yet, I hope), but I thought there would be some way the program could find out as it opens if the db was already opened by another instance of the program. Since it is a home-use app, it wouldn't be necessary to know which computer had it open, just that another one did. Am I wrong about the feasibility/possiblity of this?
modified on Monday, July 27, 2009 2:29 PM
-
riced wrote:
How are you going to do that?
Of course I don't know (yet, I hope), but I thought there would be some way the program could find out as it opens if the db was already opened by another instance of the program. Since it is a home-use app, it wouldn't be necessary to know which computer had it open, just that another one did. Am I wrong about the feasibility/possiblity of this?
modified on Monday, July 27, 2009 2:29 PM
copec wrote:
find out as it opens if the db was already opened by another instance of the program
For Access you could check for the existence of a .ldb file in the same directory as the .mdb. This would tell you that the db is in use (the .ldb is used by Access to control locking and removed when the last user closes the db). For SQL Server I think you could query the master..syslogins table but I'm not absolutely certain. There may be easier ways to do this.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
-
copec wrote:
find out as it opens if the db was already opened by another instance of the program
For Access you could check for the existence of a .ldb file in the same directory as the .mdb. This would tell you that the db is in use (the .ldb is used by Access to control locking and removed when the last user closes the db). For SQL Server I think you could query the master..syslogins table but I'm not absolutely certain. There may be easier ways to do this.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis