Mrten Mrten - 20 days ago 5
C# Question

OLE DB overwriting old database

I'm trying to use a MS Access database in C# using OLE DB, but every time the database loads it does not keep the old data.

This is my code:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Databases\\Database.accdb;Persist Security Info=False;";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO test (userID, name) VALUES(?, ?)";
// Setting parameters to random integer and string
command.Parameters.Add(new OleDbParameter("@userID", randomID));
command.Parameters.Add(new OleDbParameter("@name", randomName));
command.ExecuteNonQuery();


For testing purposes I use a randomly generated ID and name. If I check the database in MS Access the data is overwritten every time. Am I missing something?

I use this code for creating the table:

OleDbCommand command = connection.CreateCommand();
command.CommandText = "CREATE TABLE test (" +
"userID INTEGER NOT NULL," +
"name TEXT(20) NOT NULL," +
"PRIMARY KEY(userID)" +
")";
command.ExecuteNonQuery();

Answer

This typically is associated with the properties of the Database.accdb file as it exists in Visual Studio.

By default, the "Copy to output directory" field for the file is set to "Copy always". So, this means that every time to launch the application in Visual Studio, it will completely replace the working copy of Database.accdb with the prototype version.

So, the solution is kinda simple... change the field to "Copy if newer" (or "Do not copy")