Can no longer Insert into MS-Access database from VisualBasic 2010 RRS feed

  • Question

  • My first posting here.

    I created a database made up of two tables in MS-Access 2010. Table A has 20 fields while Table B has 42 fields. Table A has a PrimaryKey (1) with autoincrement which serves as a ForeignKey (Many) in Table B. For each record in Table A, there are 230 records in Table B. 

    Lo and behold, over the course of the last weeks, I was able to add over 40 records in Table A (and the corresponding number of records in Table B).

    At one point, I experimented with Visual Database Tools. I was impressed with the ease with which I was able to create a simple interface to navigate Table A record. Until I tried to delete records. Sure enough, the records would disappear from the dataset but they were never commited to the data source -- I'm learning ADO the hard way, believe me, with the little resources I can find on the internet. But I didn't mind since I was able to actually manage the data by opening the database in MS-Access. Still. I also had no luck with Updating -- that simply does not work with the simplistic -- dataAdapter.Update(dataSet, "Table A") -- statement. I was reading it could have to do with the fact that my Table A uses a PrimaryKey and that the simplistic Update call are not meant to deal with records having PK?

    Anyhow, I spent all the time and every I could on trying to teach myself ADO in VisualBasic. I love what it does -- on paper. I just wish it could be as simple as the litterature says it is.

    All in all, I had decided to remain with MS-Access to update records and be happy with my applications inserting new records in the database. But now that this functionality has ceased to work, I don't know where to turn no -- I'm discouraged. Tens of hours of studying this stuff and still can't get it to work -- feel like crying :(

    For those interested, here is the steps I take to Insert records in my database:

    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\TechkonWIP.mdb;Persist Security Info=False"
    Dim cn As New OleDbConnection(strConn)
    'create dataset
    Dim ds As New DataSet("Techkon")
    Dim sqlQRY As String = "SELECT * FROM Sheet"
    Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, cn)
    Dim cb1 As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
    da1.Fill(ds, "Sheet") ' Table Sheet is Table A
    Dim dt1 As DataTable = ds.Tables("Sheet")
    Dim newRow1 As DataRow = dt1.NewRow()
    newRow1("JobNo") = Job.JobNo
    newRow1("HeureMesure") = My.Computer.Clock.LocalTime
    newRow1("Papier") = Job.Papier
    ' ... a few more rows, as you can imagine ...
    ' dt1 = DataTable1
    ' da1 = DataAdapter1
    da1.Update(ds, "Sheet") ' ds = DataSet = Techkon
    Dim ComRecup As New OleDbCommand("SELECT @@IDENTITY", cn)
    MsgBox(CType(ComRecup.ExecuteScalar, Integer).ToString)
    Key = CType(ComRecup.ExecuteScalar, Integer)
    '... Then I do the same thing for Table B
    '... Then I close the database

    Any help is appreciated / Roger



    Sunday, October 31, 2010 3:11 AM


  • You didn't mentioned whether you were getting any errors. I would verify that you are opening and updating the correct database.

    Also, if the database is included in the project, set the Copy to Output Directory property value to Do not copy .

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, November 1, 2010 4:48 PM