Change a MSAccess conection string using commandline arguments

[Tucalipe]

What I'm trying to achieve is to change the connection string to search for the database on different locations depending on an argument passed to the executable. So, if I run my app with the /offline argument, it would use a connection string with "Data Source=|DataDirectory|DataBase\database1.accdb", while, without arguments, it would use "Data Source=\\Network_Folder\Folder\database1.accdb".

Is it possible to achieve this on vb.net+access?

Important info: I've used VS2015 Wizard to create the current connection string.

[kebo]

Is it possible to achieve this on vb.net+access?

Yes

First, you will need to store all of your connection strings somewhere that is accessible to your application. Then in the startup routine (either sub main, or in the startup form) you will read the command line arguments and select the correct connection string based on the argument you have passed using a case statement.

[Tucalipe]

Ah, I can't use the wizard then? It'll have to do, thanks!

[Tucalipe]

Ah, I think I've reached a dead end... According to what I've seen in VS2015 I'd need to rewrite all of my queries and most of my programming for the software to use a different connection string. Is that right? Or is there an easier way to declare a new connection string allowing me to have it dinamycally set?

[jmcilhinney]

Ah, I think I've reached a dead end... According to what I've seen in VS2015 I'd need to rewrite all of my queries and most of my programming for the software to use a different connection string. Is that right? Or is there an easier way to declare a new connection string allowing me to have it dinamycally set?

No that is not right. It's as simple as setting the connection string each time you create a table adapter, e.g.

Using adapter As New SomeTableAdapter
    adapter.Connection.ConnectionString = someConnectionString
 
    '...
End Using

Just make sure that you've set the connection to be public in the DataSet designer.

[Tucalipe]

Can I use ConfigurationManager.AppSettings to change that setting if I were using Windows forms? Something along the lines of:

Private Sub setdblocation (e As Object, sender as EventArgs) Handles Form1.Load()
        If CheckOffline() = True Then
            value = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Local Folder\database.accdb"
        ElseIf CheckOffline() = False Then
            value = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Network Place\DataBase\database.accdb"
configFile.AppSettings.Settings(0).value = value
End Sub

I only have one setting so far, so I'm sure Settings(0) is the Connection String.

[PlausiblyDamp]

It might be easier to store both connection strings in the config file and use the correct one based on the app running online or offline.

You can update the config file through code but not in the way you have there, if you really feel that is the approach you want then https://social.msdn.microsoft.com/Fo...orum=vbgeneral might be of use.

However the main issue you might have with updating the config file is folder permissions; if your application is installed into Program Files (or equivalent) you aren't going to have permissions to make changes to the file by default- you would need to either modify the default permissions (probably a bad idea) or make sure the app is installed elsewhere.

[Tucalipe]

No that is not right. It's as simple as setting the connection string each time you create a table adapter, e.g.

Using adapter As New SomeTableAdapter
    adapter.Connection.ConnectionString = someConnectionString
 
    '...
End Using

Just make sure that you've set the connection to be public in the DataSet designer.

So I'd need to have a copy of each query from the original online table adapter on the offline one?

[PlausiblyDamp]

There isn't a need to duplicate all the TableAdapter / query code, just change the connection string for them based on the user being online or not.

The code jmcilhinney posted is showing you how to change the connection string for a TableAdapter, there is no need to duplicate things.

[jmcilhinney]

So I'd need to have a copy of each query from the original online table adapter on the offline one?

Just read what I said. There's copying. There's no online or offline. You simply do what I said. I said that you set the connection string each time you create a table adapter so that is what you do. When I say "create a table adapter" I mean an object, not a class. If you want to retrieve data to display in Form1 then you create a table adapter and call its Fill method. Between creating the object and calling the method, you set the connection string. That's it, that's all.