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()

Just curious, are you deleting from DupeTbl, and then re-inserting those same records(only updated) again as a means to edit a record? If so, then you should just use a single loop with an UPDATE command. — Gavin Perkins, Sep 22 '15 at 03:58
Your DB is probably part of the project and set to copy a blank DB everytime you debug. — user3697824, Sep 22 '15 at 04:58
Every time when you do action on DB and it is success, and you look into DB, and you see no changes - check if you updating same DB as the one you're checking — T.S., Sep 22 '15 at 05:08
The issue in such cases is almost always that you have the `Copy to Output Directory` property of your data file set to `Copy Always` and then your working database gets overwritten each time you build. To conform whether or not data is saved, simply test the result of `ExecuteNonQuery` and `Update` calls to see if they are non-zero. The solution is generally to change that property to `Copy if Newer`. Read this to learn how data files are managed: http://msdn2.microsoft.com/en-us/library/ms246989(VS.80).aspx — jmcilhinney, Sep 22 '15 at 07:06
@GavinPerkins - Yes, I should just use and update statement. And I think I will after I get this figured out. — Rockin Raul, Sep 22 '15 at 09:38
@jmcilhinney Thanks for that article. I think I understand what's happening now. What I still don't understand is this: After I deploy the application, does the same behaviour still apply each time the application is initialized? — Rockin Raul, Sep 22 '15 at 09:45
It's got nothing to do with initialising the application. It has everything to do with building the project. If you run the project twice without changing any code in between you'll find that there will be no issue because the project will not be rebuilt. As soon as you make a code change, the project needs to be rebuilt so the source data file will be copied to the output folder, overwriting the working database. — jmcilhinney, Sep 22 '15 at 12:17
@jmcilhinney Thanks, again!. I believe I understand now. If you want to post your comments as answer I'll check it as resolved. Then you get your street cred. — Rockin Raul, Sep 22 '15 at 15:18

score 0 · Answer 1 · answered Sep 22 '15 at 13:27

   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 UR In UpdateRecords
        Comm = New System.Data.OleDb.OleDbCommand($"UPDATE DupeTbl  SET CUST_NO = <NewValue>, PREDIR = <NewValue, POSTDIR = <NewValue> etc. where DupeTbl.CUST_NO={DR.ToString}", Conn) '
        Dim aff As Integer = Comm.ExecuteNonQuery
        'MsgBox(aff)
        Comm = Nothing
    Next

    Conn.Close()

score 0 · Accepted Answer · answered Sep 22 '15 at 22:58

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.

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

2 Answers2