0

I am very experienced with MS Access, but I am trying to transition to to VB.net and SQL Server. I am trying to build a multi-user database. The mdf file would be on some computer on the LAN, and each connected computer would have the application. I need a way to let the end users select the path to the mdf file, so only one mdf file is used. I've got a working method, but I want to make sure I'm on the right track. When a form loads, I have this:

My.Settings("mslConnectionString") = "Server=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=E:\Documents\Visual Studio 2013\Projects\WindowsApplication3\WindowsApplication3\msl.mdf;"

Is that an appropriate way to link to the backend mdf file? What I envision doing is making a form where the end user can navigate to the correct network path, and then I guess I'd store that path in a text document. When the form is opened, it would first look at the text document and change the path at runtime. Thanks in advance.

devhl
  • 111
  • 1
  • 9
  • 2
    You need to attach the MDF to an instance of SQL Server (Express might work for you) on the "computer on the LAN" to make a multi-user application. – Cᴏʀʏ Jan 09 '17 at 21:10
  • So it's not like running an Access backend? Ok, if the machine hosting the data has SQL Server Express running, I would somehow 'host' the file on the server. If that is all right, how would my connection string change? Would I just use the full path to the mdf file, or something else? – devhl Jan 09 '17 at 21:14
  • 1
    You don't point to the file. You point to the instance of SQL Server. Something like this: "Server=my_server;Database=name_of_db;User Id=user_name;Password=my_password" – Bugs Jan 09 '17 at 21:16
  • If my_server is an instance of SQL Server, how does the client know where that is, or what computer it's on? – devhl Jan 09 '17 at 21:20
  • 1
    Someone will need to install SQL Server on a machine. Then you can leave the user a configuration form to write the Computer Name, DB Catalog, User and Password. – Esselans Jan 09 '17 at 21:21

1 Answers1

0

What you have there is a "LocalDB" connection string. LocalDB is a lightweight implementation of SQL server that can attach directly to an mdf file. It is commonly used to have one "client" attached to it at a time. You can't share that same .mdf file directly from multiple applications like you can with Access.

If you want to do something like that with SQL, you need to install SQL Server. SQL Server will attach to the .mdf file and allow multiple connected clients to use it at the same time.

The full version of SQL Server is /very/ expensive, but there is a "Lite" version that is free, with a few restrictions, called SqlExpress.

It is also possible to set up LocalDB in such a way as it hosts the database file as if it were a full sql server, but this involves a little bit more work and complexity. If you want more information about this, check out MSDN, specifically the section that talks about "Named Instances".

Bradley Uffner
  • 16,641
  • 3
  • 39
  • 76
  • Okay, I'm getting it. Is there an easy way streamline the installation of SQL Server Express and host the mdf file? Or does it require a competent admin to do it? I'm just considering how to get this working when most people aren't all that tech savvy. – devhl Jan 09 '17 at 21:50
  • You install that on one computer that acts like the server. Then the other computers use a different connection string to connect to *that* computer. I can give you more information once I get home. – Bradley Uffner Jan 09 '17 at 22:08
  • 1
    The connection string of each client machine will look something like `Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;`. With `Server` being the ip address of host name of the computer acting as the server. `Database` is the name of the database as it is registered in SQL Server. `Username` and `Password` are user accounts that you set up within SQL Server. – Bradley Uffner Jan 09 '17 at 22:59