Queries appear to work, but don't actually affect Access database

Question

I've added an access db to my project as a datasource. So I get the automatically generated tableadapters class and therefore, access to the table adapter instance which includes the connection string. I'm using this to open a connection to my db so I can first, delete some records, and then replace them with new records. The queries seem to work because the .executenonquery does return the rows affected. I even tried a delete * command to be sure. But, when I open the database everything is the same. I had some ideas as to why. I thought the connection string returned by the tableadapter might be goofy because it contains a generic pointer to the project's data directory.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DupeMDB.mdb;Persist Security Info=True

I also thought maybe I had a problem with the Build Action or the Copy to Output Directory. I really don't understand the mechanics behind these two things. I think maybe the copy to output directory thing might be the culprit.

Here's me goal. I want to deploy this project to my secretary so she can use the program to deal with a duplicate record list etc. The data has to go with the program. I want to package this .mdf file with the deployment and get it back from her when she's done with it. I am so close to the end here (writing back to the table). Does anyone know why the table won't update?

    Dim Connector As DupeTblTableAdapter = New DupeTblTableAdapter
    Dim Conn As New System.Data.OleDb.OleDbConnection
    Conn = Connector.Connection
    Conn.ConnectionString = Connector.Connection.ConnectionString
    MsgBox(Conn.ConnectionString)
    Dim Comm As System.Data.OleDb.OleDbCommand

    Conn.Open()
    For Each DR In DeleteRecords
        Comm = New System.Data.OleDb.OleDbCommand($"DELETE from DupeTbl where DupeTbl.CUST_NO={DR.ToString}", Conn) '
        Dim aff As Integer = Comm.ExecuteNonQuery
        'MsgBox(aff)
        Comm = Nothing
    Next
    For Each RR In ReplaceRecords
        Comm = New System.Data.OleDb.OleDbCommand($"INSERT INTO DupeTbl ( CUST_NO, PREDIR, POSTDIR, SUFFIX, CUSTSIZE, AddFlag, IgnoreRecord ) VALUES ({RR.Cust_No}, '{RR.PreDir}', '{RR.PostDir}', '{RR.Suffix}', {RR.Size}, {RR.AddFlag}, {RR.Ignore});", Conn)
        Comm.ExecuteNonQuery()
        Comm = Nothing
    Next

    Conn.Close()

Answer 1

The issue in such cases is usually the working database being overwritten on each build. When adding a local data file to your project, it is added as a source file in the project folder. By default, the Copy to Output Directory property is set to Copy Always. That means that every time you build your project, which will happen each time you make a code change and run the project by default, the source file will be copied over the top of the working database in the output folder, thus wiping out any changes you made while debugging. To prevent this, change that property to Copy if Newer, which means that the working database will only be overwritten if you make a change to the source database, e.g. modify the schema.