Help me please about sharing database in a networking computers with vb.net

Question

Hi,

First of all, thank you so much for helping me out for so many things but this time i really need your suggestions more than ever.

Well, my problem is that i have developed vb.net program and i did publish it. its running in every computers in my home office network. there are 10 networked computers and the program is running each. the database is ms access 2007. the problem is here:

I want the database to be shared by every computer so that, every computer1 enters information in the program, then the computer 2 has to see it. computer 3 has to see it. its like computers are supposed to share the database. something like that.

for example, if a user in a computer 1 adds new information to the program, then the user 2 using another computer must see what the user in a computer 1 has entered.

With all my heart, i am begging you to help me out of this.

I will appreciate your suggestions.

Regards,

Hosseinel

Answer 1

Quick and dirty - designate one machine as a server and put the Access database onto a file share on that machine.  Make sure that all of your users have update access to both the file share and the underlying file system). 

Change the application to connect to the database on this machine (might be a simple file path change; might need to tinker with ODBC settings; depends how you connect).

[Far] Better solution - designate one machine as a server, install SQLServer [Express] on it and upsize the access database into it.

Change the application to use SQLServer.

Oh; either way; don't turn the "server" machine off if anyone wants to use the database!   :-)

---

Regards, Phill W.

Answer 2

Hi,

there are multiple possibilities. The general solution is to have a central database. So you could put the Access Database File on a network share and access it from there (a slow and bad solution).

My recommendation is to upgrade to SQL Server. SQL Server Express is free and it should be easy to port the code to use SQL Server instead of Access. And there is an Upsizing wizard which reads Access databases and creates SQL Server databases.

So you would run the SQL Server on one system and all other computer will connect to this system (So the system with teh database must be running).

Other solutions can be much more complex. So it is possible to have a central database and local databases (SQL Server CE maybe?). Then the local databases will replicate with the central database (but your solution must provide that - there are multiple things to take care of so you do not get any racing conditions). That way the central server can be offline and the software might still work.

Or another idea is, that the systems could still have local databases but you build a Peer-to-Peer protocol which is used to sync the databases. But this is the most complex szenario (it would be something like Active Directory in the end).

So my suggestion would be: Build up a central system with SQL Server Express and put the database there.

With kind regards,

Konrad

Answer 3

@ Konrad,

I really thank you for that brilliant hint but lets assume that i install the sql server as you said that it can helps you to import the database from the access database into sql server database, so i got some pictures in the access database records stored as OLE object, does this mean i can get it as images database in sql server?

Sounds that am going to follow your suggestion but please how can i work this thing in vb.net. suppose i have installed sql server in my machine and i want to make my system as a central database system which enables every other computer connected to my machine to connect to it, so in vb.net how can i do that.

in my usual way of deploying the database into the program is to first connect the database to vb.net then take the databse to every computer but how can i do this in sql server. I need tutorials or tips to do this.

All what i know is that, in order to connect the program to database, you go straight forward to add new data source, you choose the new connection, from there, select which database type you want. theres sql server, access database and others. then once you connect it to database, you go to the project properties.

Project properties/application files, take the additional files names in your project.

Project properties/prerequisites, you need to select a .net framework. 3.5 and windows istaller 3.1 as download with same location as my application....the problem is here, what will you do when the sql server is being installed?

How other networked computers are able to access this database. because one you publish the program and you install it to another computer, it needs a database with the same location as the one you were working with it. dunno how this scennario works but  i really need more steps to follow.

Answer 4

Hi phill,

Thank you for that information but have to say in one side that  i have got a server and its running all the time. so if i put the database in the server for example, c:\application\database.accdb. then how about the configuring the database in vb.net

for me, i have followed the usual way to connect the vb.net to database. and here's the thing:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\WINDOWS\hello\CustomerRegister.accdb;Persist Security Info=True;Encrypt Password=False

Please i need step by step as am awfully beginner in vb.net. never reached your level of that. please step by step and i would really be indebted to you.

On the other side, since you said the best option is to install the mysql server( i have sql server 2005), how can i import the database from access to sql server..it souds a long process may be to start from scratch but if i install it and import the files from access, do i need to switch on the sql server in the machine installed the database. easy steps needed either ways.

How can i determine if other computers are capable of accessing the sql server database installed in my machine as a server? you publish the project using sql server database and you install it to another computer. no database is carried as you're using sql server. so that computer is expecting to have a database for this program, how does this think work? sorry for being naive at this but seems that i have not understood yet.

Thankz once again

Answer 5

Hi,

you no longer deploy a database with your software. Your software only holds a connection string which tells the systems where to find the database. So it is containing information about the Name or IP Address of your "database server", the database instance and the database name together with the Authentication used and additional options.

What do you use in your code to access the Access Database? Do you use the OleDb* Classes to query it? In such a case you just have to change the OleDb* Classes with Sql* classes (e.g. you use SqlConnection instead of OleDbConnection). So it can be straight forward.

Regarding your Images inside the Access database: I am not 100% sure how these will be handled by the Upsizing wizard. I would expect that you get them a BLOBs inside the SQL Server database. Maybe the code to read and display them is different from what you have right now but there are examples on the net how to read a BLOB and display the image read. But I cannot promise that the conversion is easy in that point. But it should be something that is not to difficult.

With kind regards,

Konrad

Answer 6

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\WINDOWS\hello\CustomerRegister.accdb;Persist Security Info=True;Encrypt Password=False

Answer 7

Hi Konrad,

Pleasure for me to have you on here and i really thank you for demonstrating me how the sql server works.

what i don't understand is that, for example, i install the sql server express in my machine. i got a vb.net in my programs. when i import the database from access database to sql server database hopefully it will be ok for me, i debug the program again and run it. once it runs, i then go to the project properties to publish the program. what should i suppose to do in this case? do i have to specify the location where i will install the database as the network?...in this case, what is the best way to do this? i mean, just give me some examples of how to do this....

Kind Regards,

Hosseinel

Answer 8

Hi Hosseinel,

you no longer have to distribute the database. Inside your application you have to store the connection string but that is all that the client needs. So there is no deployment for the database required in your szenario.

You gave the connection string when I asked how you access the database. So I asume now that you just used the Visual Studio IDE to access and use the database? I am not sure how easy you can translate that to SQL Server, because the IDE created some code for you which must be changed now. (I am not using these wizards inside the UI to access the data.)

But maybe it is quite easy to switch the data source and change some data bindings inside the IDE - the fact that I cannot tell you exactly what must be done does not mean that it must be complex or impossible! And I am quite sure that other will be able to help you on that, too. (And because it is a Visual Basic or Visual basic Express problem, you could even conside to ask questions inside the Visual Basic (Express) Forum when you encounter any problems! I am sure that there are a lot of very active VB experts who would like to help you, too.)

With kind regards,

Konrad

Answer 9

Thank you very much for eveyone who has given me a useful hints..i really appreciate it...

Hatts off to you, mr Konrad.

Best Regards,

Hosseinel