Click here to Skip to main content
15,662,668 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I need to create access database and fill it up in code. I do use following code to create database and table inside. First few columns need to store information, lastone is for Primary Key.

Dim MainBase As New ADOX.Catalog
Dim MainBaseIndex As ADOX.Index
Dim ShopsTable As New ADOX.Table

MainBase.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"

ShopsTable.Name = "Shops"
ShopsTable.ParentCatalog = MainBase
ShopsTable.Columns.Append("CatalogueIndex", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopIndex", DataTypeEnum.adInteger)
ShopsTable.Columns.Append("ShopName", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopWWW", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopDate", DataTypeEnum.adDate)

'Will be as Primary Key
ShopsTable.Columns.Append("LineId", DataTypeEnum.adInteger)
ShopsTable.Columns("LineId").Properties("Autoincrement").Value = True

MainBaseIndex = New ADOX.Index
MainBaseIndex.Name = "PrimaryKey"
MainBaseIndex.PrimaryKey = True

ShopsTable = Nothing

Magicly it works.

Then I'm reading it to fill up with data.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"
Dim cmd As New OleDb.OleDbCommand With {.Connection = cn}
Dim DBShops As New DataSet

cmd.CommandText = "SELECT * FROM Shops"
DA = New OleDbDataAdapter(cmd)


It's works and I'm almost belive in God. I fill up my data and now trying to save it.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"
Dim cmd As New OleDb.OleDbCommand With {.Connection = cn}
Dim MyBuilder As OleDbCommandBuilder

cmd.CommandText = "SELECT * FROM Shops"
DA = New OleDbDataAdapter(cmd)
MyBuilder = New OleDbCommandBuilder(DA)

And it's works again! Yes, God is exist and magic is real. I'm successful programmer and will earn billions dollars!

Meanwhile. I did update once, did it twice but at the third time I've been crushed by error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. How? Why?!..


Extra from the web:

This exception still seems to catch people out when updating, for example, DataSets.

This is simply because DataSet (or ADO.Net rather) uses Optimistic Concurrency by default.

This means that when updating, the whole row (rather than the changed column only) is used for comparison with the data in the database.

This then means that if you are trying to update a row that no longer exists in the database, the update from the DataAdapter the update will fail with the exception above.

Typical scenarios when this may happen is that you get your data into the disconnected DataSet, you do some work on it and then try the update.

However, between you selecting the data into the client and sending the update, another user is deleting this row from his application.

Or it can be that you are deleting the data from somewhere else in your application.


Wrong! As I know my application, only it is reading this database from the single computer. Also, I pretty sure that is NO single line has been deleted. Only added and updated. So... dead-end.

1 solution

I see that you open connections, but do you close all them? The .NET framework may not be cleaning up your connections as fast as you are creating them. Try wrapping your connections in using statements so that they are automatically destroyed when you are done with them and if there was an exception, they will also be destroyed.
Share this answer
Member 9969313 14-Jun-13 2:21am    
Yes, all time I do:


Except creation time, because there nothing to close.
Member 9969313 14-Jun-13 2:28am    
Buy the way, I have an extra idea. Why it can be done well first and second time but fire exception on third? Because first time and, suddenly, second all data unique and there adding only new records. At the third time some of existing records could be changed and need to be updated. Maybe here something happens? As I understand it's only one difference between 1-st and 3-rd time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900