asked on

Connect to MS Access db

This question has probably been asked a hundred times, but what the heck...

I want to connect to a MS Access DB;
filename="MsgTaker.mdb"; tableName="Msgs"; Coloums="MsgID,MsgFor,MsgFrom,....."
I want to add the data from the textboxes to the db using coding because the user won't be viewing the data just adding to the db.

So I am looking for your help to connect to the database then table and datset and whatever needed cause I'm quite new to VB.NET.
I want to do all this by coding. Please include any references that have to be added.

Thanks

P.S. I would prefer not to use SQL if that matter, probably JET or something instead.

Jeff Certain

1. You can't use SQL to Access an Access DB. Instead, you need the OLEDB objects. Add Imports System.Data.OleDB to your class.

2. The addition must be done using a SQL INSERT statement to add new records (UPDATE to update an existing record).

3. Your code needs to look something like this:

            Dim conn AsoledbConnection = New OLEDBConnection(connectionString)
            conn.open
            Dim cmd As oledbCommand = "INSERT INTO tableName(columnA,columnB,...) VALUES (Value1,Value2,...)"
            cmd.ExecuteNonQuery
            cmd.Dispose()
            conn.Close()

You need to list the values to insert in exaclty the order that the columns are listed. Autonumber columns do not need to be listed. Also, ensure that all mandatory fields have values provided.

Hurb

ASKER

How do i go about getting the connectionString? Probably a Basic Question but sorry.

What about datasets and all that crap.

SOLUTION

Howard Cantrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff Certain

If all you're doing is adding records to the database, you can do this directly, without datasets/datareaders/dataadapters/etc. You just need a connection and the SQL string to tell the server (i.e. the Access database) what to do...

For Access databases, the connection string looks like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\databaseName.mdb"

Note that this is not a password protected database in this example.

Hurb

ASKER

Chaosian,

What Sub do i call to add to the database then.

ASKER CERTIFIED SOLUTION

Jeff Certain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hurb

ASKER

What reference do i have to add cause:
Type 'oledbConnection' is not defined.
Type 'oledbCommand' is not defined.

Jeff Certain

you need to add the line Imports System.Data.OleDb (or something close to this -- intellisense will let you know) at the beginning of the class, before the class declaration....see the very first thing I posted (#1 in the first response to your post).

Imports System.Data.Oledb

Public Class MyClass
etc
etc

Hurb

ASKER

Just more errors, is there a reference i must add like from the project menu, add reference.
Could you check your solution explorer for the references that you have there please.

Thanks again.

Howard Cantrell

All the references are in my code above

Jeff Certain

Reference System.Data