MS Access Dynamics Connection

[jovillanuev]

Hi Guys,

I am using the following codes to connect to MS Access database using vb.net code. I am providing the file path name and directory. This codes work fine in my computer. How can I make this codes as dynamics connection that can run to another computer that no need to change the path. I'm using MS Access 2013 and Visual Studio 2008 (VB.NET). Below is the connection string code i used to connect to database in VB.NET form. So, how and what is the connection string I will be going to use in my form. This is bothering me. I am testing my application and the database is stored and access in bin/Debug folder, why it is the below path was not access instead. thanks.

--This is a hard coded connection string 
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\JV\Desktop\MyVBProject\FeedBackSystem"


--Then I change to this one.
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source= |DataDirectory|\KPI.accdb"

I'm getting an error. could not find the file. C:\Users\JV\Desktop\MyVBProject\FeedBackSystem\bin\x86\debug\KPI.accdb.

[djjeavons]

Hi

The |DataDirectory| simply tells ADO.NET where to look for the database file. In the case of a Windows Forms application, this will be the same location where the executable file resides (in this case the bin\debug folder). If this was an ASP.NET application then it would be the App_Data folder.

If you plan on keeping the database file in the same location as the executable file when you deploy your application then the above connection string will be fine. You will need to move (or copy) your database to the debug folder while working on your application.

A better approach however would be to store the connection string in a settings file. This way, you can point the connection string to your current location when working on your code, and when you deploy your application you can change this setting to point to the new location on the target machine. The settings file is just an XML file so it is very easy to modify.

To use a settings file, right click on your project and choose properties. Then on the left hand side you should see a section called Settings. Click this and add a new setting of type string. Call it ConnectionString and provide the actual connection string as the value.

Then, in your code you can simply state:

conn.connectionString = My.Settings.ConnectionString

HTH

[Reverend Jim]

You may want to consider (on app startup) doing a test to see if the file can be accessed, and popping up a folder or file dialog to allow the user to specify the file location if it cannot be accessed. This new location could be saved in the app Settings for subsequent executions.

[jovillanueva]

Hi djjeavons,

I tried your suggestion to use the setting to store the location. see attached screen shot, then I add a datasource and copied to my project. hope this is correct.

By the way, Just want to ask this question. I will deploy this applcation to 5 work station or do i need to copy this application to a separate computer as server and the 5 work station will connect to that computer. any suggestion. thanks.

[djjeavons]

Hi

There is no screen shot unfortunately.

Regarding deployment, normally you would deploy the application to individual workstations and if the database is supposed to be shared then the database would go onto a server which each application would point to (as part of the connection string).

Word of warning, if this is an Access database then bear in mind that Access does not always play nicely in a multi user environment. That is, it is not designed with concurrent usage in mind, although it can be done. Maybe consider something like SQL Express if multi user is required.

[jovillanueva]

Thank you djjeavons. Tried again to attached image.
Regarding deployment. let say my database is stored into this path,
C:\Users\JV\Desktop\FeedBackSystem\FBSystems\bin\x86\Debug\KPI.accdb. all 5 individual workstation will have a copy of my application whichj it contains also my DB. and i have also another workstation that will serve as my server and will be also installed with application.

You mean the DB will be installed only in my server? how can i shared the DB. thanks.

Below is where my connection string setup in Setting. Leftside is the solution explores that cointains also my DB and dataset.

[djjeavons]

Hi

Change the type to String.

If the database is going to be stored locally then that is fine. I would recommend that you possibly store the database in the same location as the executable so that you can make use of the |DataDirectory| directive which will make deployment easier for you. As it is going to be local, you should also consider the advice that Reverend Jim gave you as if the user does move the database then your program needs a mechanism for alerting the user to the fact that the database could not be found and of also updating the settings file.

You mean the DB will be installed only in my server? how can i shared the DB. thanks.

If your database is in a central localtion on a server and the workstations can all see the server, then the connection string would simply point to a path on that server as part of the Data Source parameter.

[jovillanueva]

Got an error after i change it to string when i login to my application. It say "format of the initialization string does not conform the specification starting at index 0.

Meaning those 5 work station will have their own DB file which was stored under this path C:\Users\JV\Desktop\FeedBackSystem\FBSystems\bin\x86\Debug\KPI.accdb same location with exe file.

as for centralize DB i will stored in separate folder outside of application and will change the string connection of my form based.

By the way. i will use clickonce to compile. This is my first time deploying a multi-user database application.I'm be very grateful for your suggestions/advice. thanks.