By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,425 Members | 2,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,425 IT Pros & Developers. It's quick & easy.

VB.Net - connecting to Access

P: n/a
I've been using OdbcConnection, OdbcCommand, etc to do work associated with
an Access 2003 database - is that the best? What are the other
alternatives - and their pros/cons?

Any/all insights - much appreciated.

BBB
Jun 6 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On 6 Giu, 15:25, "booner" <b3boo...@yahoo.comwrote:
I've been using OdbcConnection, OdbcCommand, etc to do work associated with
an Access 2003 database - is that the best? What are the other
alternatives - and their pros/cons?

Any/all insights - much appreciated.

BBB
hi BBB,

I have been using the OleDB connection and works fine and fast.
Let me know if you need more info about that.
Tommaso
-- Datatime Universal (free) http://cam70.sta.uniroma1.it/DatatimeUniversal/
--

Jun 6 '07 #2

P: n/a
I've got ODBC connection working.

I guess I was looking for a breakdown of why I would use (pros/cons) Odbc or
OleDb, Sql, Ado ...

Just trying to educate myself on the options and when/why to use them.
"to**************@uniroma1.it" <To**************@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
On 6 Giu, 15:25, "booner" <b3boo...@yahoo.comwrote:
>I've been using OdbcConnection, OdbcCommand, etc to do work associated
with
an Access 2003 database - is that the best? What are the other
alternatives - and their pros/cons?

Any/all insights - much appreciated.

BBB

hi BBB,

I have been using the OleDB connection and works fine and fast.
Let me know if you need more info about that.
Tommaso
-- Datatime Universal (free)
http://cam70.sta.uniroma1.it/DatatimeUniversal/
--

Jun 6 '07 #3

P: n/a
You definitely want to use the OleDB connection because that allows you
to use the OleDBDataAdapter which is the BEST way to move data to and
from any OleDB database. The DataAdapter (OleDBDataAdapter,
SqlDataAdapter) forgo having to loop through data. You can use the Fill
method to retrieve data into a .Net Dataset in one shot and you can use
the Update Method to Insert and Update Data on the Database side.

The DataAdapter has revolutionized how you Read/Write data From/To any
RDBMS

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 6 '07 #4

P: n/a
On 6 Giu, 17:26, Rich P <rpng...@aol.comwrote:
You definitely want to use the OleDB connection because that allows you
to use the OleDBDataAdapter which is the BEST way to move data to and
from any OleDB database. The DataAdapter (OleDBDataAdapter,
SqlDataAdapter) forgo having to loop through data. You can use the Fill
method to retrieve data into a .Net Dataset in one shot and you can use
the Update Method to Insert and Update Data on the Database side.

The DataAdapter has revolutionized how you Read/Write data From/To any
RDBMS

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
I agree with Rich about the OleDb Connection. Oledb is the way to go
now.

But, if I may, I would not recommend too much the OleDBDataAdapter. In
my opinion,
in most situations one could even disregard its existence and focus on
the OleDbDataReader
directly (which is anyway used by the adapter), because more general
and flexible.

Another big advantage is given by the OleDbSchemaGuid which makes
possible
easy integration among platforms and metadata retrieval.
Tommaso
-- Datatime Universal (free) http://cam70.sta.uniroma1.it/DatatimeUniversal/
Jun 6 '07 #5

P: n/a
What advantages do I get by using the OleDB classes (Connection, Command)
versus their Odbc counterparts?

From what I can see - other than connection string used - the general means
for doing db work - is nearly identical.

So why use Ole vs. Odbc vs. ADO ... set of classes?

I have the general mechanics down - just not sure which set of classes get
me the most bang for the buck.

"to**************@uniroma1.it" <To**************@gmail.comwrote in message
news:11**********************@q69g2000hsb.googlegr oups.com...
On 6 Giu, 17:26, Rich P <rpng...@aol.comwrote:
>You definitely want to use the OleDB connection because that allows you
to use the OleDBDataAdapter which is the BEST way to move data to and
from any OleDB database. The DataAdapter (OleDBDataAdapter,
SqlDataAdapter) forgo having to loop through data. You can use the Fill
method to retrieve data into a .Net Dataset in one shot and you can use
the Update Method to Insert and Update Data on the Database side.

The DataAdapter has revolutionized how you Read/Write data From/To any
RDBMS

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

I agree with Rich about the OleDb Connection. Oledb is the way to go
now.

But, if I may, I would not recommend too much the OleDBDataAdapter. In
my opinion,
in most situations one could even disregard its existence and focus on
the OleDbDataReader
directly (which is anyway used by the adapter), because more general
and flexible.

Another big advantage is given by the OleDbSchemaGuid which makes
possible
easy integration among platforms and metadata retrieval.
Tommaso
-- Datatime Universal (free)
http://cam70.sta.uniroma1.it/DatatimeUniversal/


Jun 6 '07 #6

P: n/a
Here is a quicky sample of the OleDB class used with an Access mdb

Imports System.Data.OleDb
Imports System.Data

Public Class Form7
Dim conn As OleDbConnection, da As OleDbDataAdapter
Dim ds As DataSet, curMgr As CurrencyManager

Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source =
C:\Code\testAcc\db2test.mdb"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandType = CommandType.Text
da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
curMgr = CType(Me.BindingContext(ds.Tables("tbl1")), CurrencyManager)
curMgr.Position = 0
tssL3.Text = curMgr.Count.ToString
tssL2.Text = (curMgr.Position + 1).ToString
dgrv1.DataSource = ds.Tables(0)
End Sub

Private Sub dgrv1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles dgrv1.Click
'--this will display the number of the record you are on
'--in the Datagridview - same as a table or query in Acc
tssL2.Text = (curMgr.Position + 1).ToString
End Sub
End Class

Note: tssL2 and tssL3 are labels on a statusStrip (my naming
convention). The statusStrip is the control that replaces the StatusBar
- much more horsepower. dgrv1 is a DatagridView control (the equivalent
of a subform, but way more horsepower and flexibility).

You could use the OleDBDataReader or DataAdapter. The OleDBDataReader
doesn't have the Fill method, but if you don't need to fill a table to
view in a DatagridView control, then the Reader is probably more
efficient.

This sample may not look like much, but if you apply it to a large
project, you will end up writing way less code using the OleDB class
than the ODBC class. THe ODBC class is mostly for legacy stuff, like
for DBF tables. Access is an OleDB application, so you should take
advantage of the improvements OleDB offers over ODBC.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 6 '07 #7

P: n/a
On 6 Giu, 20:07, "booner" <b3boo...@yahoo.comwrote:
What advantages do I get by using the OleDB classes (Connection, Command)
versus their Odbc counterparts?

From what I can see - other than connection string used - the general means
for doing db work - is nearly identical.

So why use Ole vs. Odbc vs. ADO ... set of classes?

I have the general mechanics down - just not sure which set of classes get
me the most bang for the buck.
You may also want to take a look ar these.

http://www.4guysfromrolla.com/webtech/063099-1.shtml
http://www.4guysfromrolla.com/webtech/070399-1.shtml

In any case switching to oledb you will experience advantages,
especially
if you ever need to write (general) code to be used against different
dbms

[to me, for programming purposes, the decisive argument is the
oledbschemaguid:
I would use it even if it performed slower, just because of the
metadata available]

T

Jun 6 '07 #8

P: n/a
Thanks.

They threw me off by calling it OLE - clearly, with .net - not my father's
OLE (from back in the day).

"to**************@uniroma1.it" <To**************@gmail.comwrote in message
news:11*********************@g37g2000prf.googlegro ups.com...
On 6 Giu, 20:07, "booner" <b3boo...@yahoo.comwrote:
>What advantages do I get by using the OleDB classes (Connection, Command)
versus their Odbc counterparts?

From what I can see - other than connection string used - the general
means
for doing db work - is nearly identical.

So why use Ole vs. Odbc vs. ADO ... set of classes?

I have the general mechanics down - just not sure which set of classes
get
me the most bang for the buck.

You may also want to take a look ar these.

http://www.4guysfromrolla.com/webtech/063099-1.shtml
http://www.4guysfromrolla.com/webtech/070399-1.shtml

In any case switching to oledb you will experience advantages,
especially
if you ever need to write (general) code to be used against different
dbms

[to me, for programming purposes, the decisive argument is the
oledbschemaguid:
I would use it even if it performed slower, just because of the
metadata available]

T

Jun 8 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.