My changes to a table won't save.

[RoohBear]

Hello, I'm trying to figure out why my changes to an Access Database won't
save.

I can open a database, get the numbers of tables, get table values, clear
tables, etc but they never seem to get saved. I can save changes in an Excel
file ok, but not an Access database.

I'm using VS2008, C#, and have made a dataset somehow using VS2008. The
table I want to access is called optioncat and it has just a few simple
columns.

I found a method for a table called AcceptChanges() that commits changes,
but doesn't help. I close the database using CloseCurrentDatabase() but that
doesn't help either.

Here are my steps and my code so far:
- connect to Microsoft Access using Interop.Access
- open my database file
- make an adapter (I don't know what an adapter is or what it does, but it
seems necessary for my next step to get at the tables)
- make a table using a dataset so I can access the table in my database
(this part seems clear)
- get the number of records in the table (for debugging, I know that I have
2 records at startup)
- get the name of the table (for debugging)
- clear the table
- get the number of records in the table again (should be 0 now)
call tableOptionCats.AddoptioncatRow (for debugging, to add 1 record to the
table).
- get the number of records in the table again (should be 1 now)
- call table.AcceptChanges()
- close the current database


// connect to MS Access
appAccess = new Microsoft.Office.Interop.Access.ApplicationClass();

// open the file I want
appAccess.OpenCurrentDatabase("c:\\users\\greg\\desktop\\YourData.mdb",
false, "");

// make an adapter
yourdataDataSetTableAdapters.optioncatTableAdapter adapterOptionCats = new
yourdataDataSetTableAdapters.optioncatTableAdapter();

// make table from the adapter
yourdataDataSet.optioncatDataTable tableOptionCats =
adapterOptionCats.GetData();

// get some info about the optioncat table in the database
int nNumOptionscatRecords = tableOptionCats.Count; // should be 2

String strOptionCatTableName = tableOptionCats.itemidColumn.ToString(); //
should be "itemid"

// clear the table
tableOptionCats.Clear();

// get the # of items again
nNumOptionscatRecords = tableOptionCats.Count; // was 2, should now be 0

// add a row for debugging purposes only
tableOptionCats.AddoptioncatRow(123, "testname", "testheading", 34.45, 67.89);

// get # of items again
nNumOptionscatRecords = tableOptionCats.Count; // was 0, should now be 1

// these should save the file, but do not
tableOptionCats.AcceptChanges();
appAccess.CloseCurrentDatabase();


Thank you Microsoft. I'm rather impressed with how this all works, and how
far I got after only a few hours of work using mostly the Intellisense
feature in VS2008. Keep up the good work! Now if only my changes would
save...

 

[RoohBear]

After coming back to this a day later, I noticed that this method seems to
make a difference after inserting a row:
adapterOptionCats.Update(tableOptionCats);

I figured out how to delete a record too. This command will delete the 3rd
record:
tableOptionCats[2].Delete();

I found these commands, and call them when I close the database but I'm not
sure if there's a better way:
tableOptionCats.AcceptChanges();
appAccess.CloseCurrentDatabase();
appAccess.Quit(AcQuitOption.acQuitSaveAll);