Ms Access ADO.NET OleDb Database Connection with OleDBDataReader

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print
'This tutorial explains how to create a connection to an Ms Access
'Database using an OleDB Connection Object

Public Shared Function GetReader() AS OleDBDataReader


Dim myReader as OleDbDataReader
Dim myOleDbConnection as OleDbConnection
Dim myOleDbCommand as OleDbCommand

'Instantiate a new connection with a connection string to an Ms Access Database
myOleDbConnection = new OleDbConnection("Server=localhost;" & "Database=NORTHWIND.MDB;UID=sa;PWD="")

'A new command object that would execute a TSQL statement
myOleDbCommand = new OleDbCommand("SELECT * FROM Employees", myOleDbConnection)

'.NET Framework Error handling : Try, Catch, Fiinally Block
Try

myOleDbConnection.Open()

myReader = myOleDbCommand.ExecuteReader()

Catch


Finally

myOleDbConnection.Close()

End Try


Return myReader


End Function
profile
KINGSLEY TAGBO
 : KINGSLEY TAGBO     : O'FALLON, Missouri, United, States   Reply  

Replies (15)

hi

I'm developing an app which is gonna work on a little lan: a Win2000 server(server) and 2 other win XP connected to it. my app is gonna be installed on these two, accessing a db on the server.

now does ur code work in such case? if so what should be the server's name? the name of the server computer or something else?

and i don't any username and password in the connection string. i guess it means the oledb doesn't reqied it unlike sqlserever. what should i do for the security? as the server is the sever of an ISP i'm affraid users connecting to the ISP can explore these db's.

thanks aloy

: mrdj1002    Reply

profile
KINGSLEY TAGBO

What kind of database are you connecting to : Sql Server or another type of database .

If you are connecting to Sql Server you will be using a SqlConnection and if you are connecting to another ype of database you can use an OleDBConnection or ODBC Connection.

The method above uses ADO.NET and that will also work for you.

We can go into the specifics if you want and I can help you to establish the connection in your application :

Thanks


Yes please. I seriously need to know the specifics as I’m totally a newcomer to ado and ado.net remote data accessing app’s. All my past experiences were with my love, “DAO” and native db’s.

I try to describe the case clearly:

As I said there is an ISP I’m developing the app for. The ISP has a LAN and on the server there are a couple of access databases (.mdb) . I need to install my app on another computer in the LAN and work with the db’s on the server remotely as the server needs to be logged off most of the time and the ISP’s employees need to work with my app.

As ISP admin asked me: they prefer not to install any extra apps on the server. So installing sql server on the server is my last choice. I though I could use ODBC or OLEDB drivers on the server. But I’m not sure if they are designed to serve a remote client as sql server does.

In case they are able to do this, I still don’t know what configuration should I do on the server side to make ODBC or OLEDB serve my application. And after that I need the connectionstring for OleDBConnection according to server’s configurations.

You also make me a great favor if you tell me what would your choice be in such case?

thanks

: mrdj1002    Reply

profile
KINGSLEY TAGBO

I would choose Sql Server over Ms Access for a database on the server.

However, if you decide to choose Ms Access, you will configure the connectio string and open a connection like this :

VB.NET

Dim myOleDbConnection As OleDb.OleDbConnection

Dim myConnectionString As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=F:SharedApp.mdb;" & _

"User ID=Admin;" & _

"Password="

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

myOleDbConnection.Open()

where F:SharedApp.mdb could be the path to your remote ms access database.

Their will be other considerations that can affect the permonace of your application .

1. How many users will typically be connected to the database

2. How much data are you transfering over the LAN

3. What kind of concurrency management or record update management do you want

4. Do you want to benefit from using Sql Server Stored Procedures ?

5. Sql Server offers the extra benefit of connection pooling

I look forward to your feedback

Thanks


Thanks a lot

You really helped me. And I guess you I should append “Server = Servername” to the correctionstring A few more questions:

1.Is Servername the Computername of the server and the one that appears in VS Server explorer under Servers node?

2.Is there any possibility that users connecting to ISP (the same server where db’s exsit) can view the db’s in the same way I do?

3.Can you introduce some good books, sites, and other resources discussing different aspects of db programming?

And I guess that’s it.

I have a few more questions that are not related to this thread. And I’ m gonna open a new one for those I’d be thankful if you could help my.

: mrdj1002    Reply

profile
KINGSLEY TAGBO

Hey :

I am glad I could be of help. Open as many questions as you want and thanks for appreciating my little help.

Yes, you are correct about the server name.

The exposure of the database depends on what kind of database.

If it's Sql Server, other users will have to know the server name, ip address and (login / password)

If it's Ms Access, other users will have to know the physical location of the ms access database.

Your database can be exposed if you throw an exception in your application and include the connection string information with that.

I will see what books and sites I can look up.

Please feel free to ask questions or contribute your own topics.

Thanks


profile
KINGSLEY TAGBO

Hi :

Please find more than 100 ado.net source code and .net samples at http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6847


Hi

Well I tested the following code:

Try

Dim oOleDbConnection As OleDb.OleDbConnection

Dim sConnString As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:NorthWind.mdb;" & _

"User ID=Admin;" & _

"Server=ServerName;" & _

"Password="

oOleDbConnection = New OleDb.OleDbConnection(sConnString)

oOleDbConnection.Open()

MsgBox("Connection Established!")

oOleDbConnection.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Where Servername is the name of the computer, db’s are located (in here the same computer the app runs on)

But the following exception was thrown: “Could not find installable ISAM”.

I don’t know what’s wrong and have no idea what ISAM is? Can you help me please?

Thank you

: mrdj1002    Reply

profile
KINGSLEY TAGBO

Please take a look at this link http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6848


I have the same problem in connecting a access file which has password set.

By removing the password, it can connection to the access file without any error, however, when I enable the password in my access file again, same error reported.

I'd like to know which keyword should be used in the connection string for passing the password in opening the access file.

I have try both password and PWD, but both of them are failed.

All example I can find from books or even the MS technical library are only showing how to connect a access file without password, is it possible to connect the file with password protected?

: James MA    Reply

I would like to create a countdown column in a datagridview, based on a date in another BindingFiled column in the same datagridview.

: maaw    Reply

How can I connect access database with Jet OLEDB ?

: qt    Reply

profile
KINGSLEY TAGBO

Please see these new posts that i just created. These posts have .NET code samples attached. Please be sure to read or examine the attached examples.

1. How to create an OleDB MsAccess Connection String using the Web.Config File :

http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6851

2. How to create an MS Access (Microsoft Access) OleDB Connection :

http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6852

3. How To Fill a DataSet from MsAccess using an OleDBDataAdapter and OleDBConnection :

http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6853

4. How to Retrieve Ms Access Data using an OleDBDataReader and OleDBCommand :

http://www.kdkeys.net/ms-access-ado-net-oledb-database-connection-with-oledbdatareader/#link-6854

Please let me know if you have any questions.

Thanks[Edit by="Kingsley Tagbo on Sep 1, 12:29:00 PM"][/Edit]


profile
KINGSLEY TAGBO

Hi :

I have uploaded source code for Ms Access Data Access with ADO.NET .

Please give me a feedback.

Thanks


In visual.net You can use access database controls such as:

oledbconnection

oledbadapter



Post A Reply

 Questions & Answers