Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding Records to an Access Database using VB 2005

Posted on 2006-07-02
3
Medium Priority
?
236 Views
Last Modified: 2010-04-23
I am trying to add records to an existing Access datatable. The dataset,  bindingsource, and tableadapter are defined.  The following programs runs ok. I can see the new records in a datagrid view, but the source access database is unchanged after I close the program.  

Sub AddRecords()
        Dim dt As DataTable
        Dim dr As DataRow
        dt = DbTestDataSet.EventIndex      'the access dataset & datatable

        EventIndexTableAdapter.Fill(dt)

        Dim dr As DataRow
        For i = 1 To 5
            dr = dt.NewRow()
            dr!event = i * 2
            dr!timesim = i - 4
            dr!date = Now
            dr!duration = 89
            dt.Rows.Add(dr)
        Next
        dt.AcceptChanges()
        EventIndexTableAdapter.Update(dt)
       
0
Comment
Question by:BW999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 17028426
Hi BW999,
>         dt.AcceptChanges()
Try remarking this line and see how it goes

Dabas
0
 

Author Comment

by:BW999
ID: 17033724
Dabas,

The problem was that the dataset was referenced to the wrong file.  Apparently VB makes a copy of the database and stores it in a working directory somewhere. So it was making changes to copied database instead of mine. I thought it was supposed to copy that back to my original file when the changes were accepted.  But apparently not for some reason.

I changed the file reference in the Project/ Properties/Settings window to the actual source file instead of the copy.  After that, it works, whether or not the 'dt.acceptchanges' line is included.

It works, but not as advertised.  Any idea why the file refencet would be messed up?

BW
0
 
LVL 27

Accepted Solution

by:
Dabas earned 1000 total points
ID: 17033850
BW999,
I am glad you have solved your problem
As far as I can tell, the AcceptChanges name is misleading.
What most people understand is something like "This dataset (or datatable) has changed. Please accept them before updating"
Internally vb KNOWS that you have made changes, and actually remembers which rows have changed by setting a flag on each of the changed rows. When you say AcceptChanges, vb understands you are asking it to unmark all of those flags. Theferore when you call update, nothing gets changed in your original data

You are right. The changes to the data are on a local copy (I presume in memory, not in a working directory). When you call UPDATE, the data in the original source gets updated. This is one of the powerful features of ADO.NET, and it takes into account that your source could be half way around the world. Hence instead of keeping a connection to your source open (which can be very slow if your source is far away), it only communicates when you issue the update statement, which can happen after many changes have been done, like in your case (It would be wrong to do the .Update inside the loop, for example)

Dabas
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question