We help IT Professionals succeed at work.

Retrieving & Updating an Access Database

Andrew Stillwell
on
Medium Priority
261 Views
Last Modified: 2010-05-18
For some reason (By even using the DataForm Wizard) I am unable to update an Access database from .NET
I use the OLE JET 4.0 Provider and a Microsoft Access 2003 database.
I can get my programs to add a blank record, but not any actual data.
Why is this happening?

Please include examples and explainations
Comment
Watch Question

Commented:
Check this out http://www.startvbdotnet.com/ado/msaccess.aspx

Basically,

' 1. Create your OleDB connection
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\yourDBPath\yourDB.mdb;"
Dim conn as New OleDbConnection(connString)

' 2. Let's say you want to insert a record in Table (tblCustomers {customer_id(PK), customerName})
' for this you can use an OleDbCommand
Dim cmd As New OleDbCommand("INSERT INTO tblCustomers (customerName) VALUES ('John Smith')", Conn)

' 3. Now execute the command
Try
  Conn.Open()
  cmd.ExecuterNonQuery()
Catch Ex As Exception
  MessageBox.Show(Ex.Message)
Finally
  Conn.Close()
End Try

HTH
~BC

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you getting any exceptions?  Are you using a DataAdapter?  Are you calling the Update?

Bob

Author

Commented:
TheLearnedOne:

This is my code for filling the dataset
            OleDbDataAdapter1.SelectCommand.Parameters("GSFEquipNo").Value = txtboxGSFEquipNo.Text
            OleDbDataAdapter1.Fill(DsFreon1)

This is my code for updating

            Me.BindingContext(DsFreon1, "Freon").EndCurrentEdit()
            Me.BindingContext(DsFreon1, "Freon").AddNew()

            OleDbDataAdapter1.Update(DsFreon1.Tables("Freon"))
            DsFreon1.AcceptChanges()

What is wrong with this?  I've never had such problems with SQL Server; why is an Access database so different?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008
Commented:
That's because Access sucks *GRIN*  Are you getting an exception?

Did you think about SQL Server 2005 Express?  It's free.

Bob
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.