Publish project including database where database update everytime project is openned in visual basic

Question

I am working on a project in visual basic which include a database. When I publish the project I got an error message which basically tells me that my database can't be accessed because initially it was located in the Debug folder before I publish the app.

I did found multiple solutions for my problem however none of them get to work for different reasons.

1) I tried to add my database to My Resources and then use it in my code as My.Resources.dbName The problem here was that when I had to specify my Data source in the connection string I could not use My.Resources.dbName

In this scenario, I need an alternative solution to what connection string I can use?

Current connection string: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=db.accdb;Persist Security Info=True"

2) I tried to put the database in the installation folder after I install the .exe version of my app and still did not work

3) I tried to add it as a database source but did not found a way to link it to my project

If you are going to give me a solution in this way, please do so in a lot of steps and explain clearly

Most solutions that I read were about similar solutions to the one above so there is no need of me mentioning them.

Also, I need the database to keep its records, because in the first solution the database was empty every time I opened the .exe file.

Database: Microsoft Access VB:Visual Basic 2017/2015

Thank you for taking the time and effort to help me!

Answer 1

Using a local database is very easy.

The first step is to add it to your project in the Solution Explorer. For an Access database, that usually entails creating it in Access and saving it to your Documents folder or the like, then right-clicking your project or using the Project menu and adding it to the project as an existing item. When prompted to copy the file to your project folder, do so. Your clean database is now in your project folder, with the rest of your source files. Any time you want to make any changes to the schema or the default data, you do it to that source file.

You should generally then change the Copy to Output Diorectory property of that file from Copy Always to Copy if Newer. What happens is that that source file is copied to your output folder along with your EXE when you build and it is that copy that your application connects to when it runs. By default, a copy is made every time you build, which means that any time you make code changes and run your project in the debugger, any changes you made to the data in previous debug runs will be lost. By setting Copy if Newer, the copy in the Debug output folder will only be overwritten if you make changes to the source data file itself.

When you publish your project, a Release build is created. A copy of the clean source data file is made in the Release output folder and that is what you deploy along with your Release EXE. The Debug database is completely irrelevant. Because the Release output is created from the source file, it is unaffected by any mess you may have made in the Debug copy while testing. Just add the database to your project properly and there's no problem to solve.