Solved

Data entered in Access table via VB seem to work, but missing next time file is opened.

Posted on 2014-01-05
4
928 Views
Last Modified: 2014-01-13
I have used MS Access 2013 to create a db-file with some data. I have then written some code in VB.NET that opens and access the data. I can create new records, and I can page through them back and forth and all seems fine. But when I run my program again, the data I entered has gone missing. I use
    Private Function storeData(ByVal mySql As String) As Boolean
        Dim ret As Boolean = False
        Dim ac As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myPath)
        Try
            Using ac
                Dim myCommand = New System.Data.OleDb.OleDbCommand(mySql, ac)
                myCommand.Connection.Open()
                myCommand.ExecuteNonQuery()
                myCommand.Connection.Close()
            End Using

            ret = True
        Catch ex As Exception
            MsgBox("There is something wrong." & _
                   vbNewLine & ex.ToString, MsgBoxStyle.Exclamation, "SQL Error")
        End Try
        Return ret
    End Function

Open in new window

The SQL command looks something like this:
Insert into NAD(Name,Address,Postnumber,Telefonnumber,Epostaddress,Webpage) values('Thor','Oslo','0575','999 88 777','epost@epost.no','www.web.com');

Open in new window

and seem to work fine, and when I browse through the table using my program after having entered a new record, I can see the new records. But when I close the program and reopens it, or query the DB from outside, the newly entered data have disappeared. What am I missing? Do I need som kind of file save or commit or something?
0
Comment
Question by:IverErling
[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
4 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39757907
Check the location of where the Access DB file is. Then go to the programs project folder debug\bin directory and see if there is a copy there, if so open that version of the DB file and see if the records are there.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 400 total points
ID: 39757955
ExecuteNonQuery returns an Integer that indicates the number of rows affected by the command. Check its value.

If it is zero, then the command did not add the data to the table in the database.

If it is more than zero, then the data was inserted. In such a case, as Fernando suggested, you might be looking at 2 copies of the database and might be inserting in the wrong one. If myPath contains a relative path, this can easily arise.

If this is the case and If you see the database file in your project, click on it and look at the Copy to Output Directory in the Properties window. You might need to change it to another value to adjust the way that you work with the database during development.
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 100 total points
ID: 39757975
Hi IverErling;

As @JamesBurger stated you might be using a local database file in your project and the reason for my questions in my post. If that is the case this web page will give you the steps you need to follow for the configuration you want.

How to: Manage Local Data Files in Your Project
0
 

Author Comment

by:IverErling
ID: 39763089
Thanks for your advice! I will check this as soon as I get a chance, hopefully within a day or two! So easy to miss such things ;-)

IVer
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

627 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