MDB Cannot be opened

[Rob]

I have finally exhausted all resources and time to try and open an
Access 2000 MDB using ADO.NET. I can query my SQL box all day long.
I'm using VS 2003 and have the latest MDAC and Jet.

I included all attempted code that refuses to work. The directory
where the MDB is has EVERYONE set to all but Full rights.

I can easily query the MDB using VB6 and ADO.

Any help would be greatly appreciated!

My code:
Option Explicit On
Imports System
Imports System.ComponentModel
Imports System.Drawing
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Oledb

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim myConnection As OleDbConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\rb\My Documents\db3.mdb")
'myConnection.Open()
'Dim myTrans As OleDbTransaction =
myConnection.BeginTransaction()
'Dim mySelectQuery As String = _
' "SELECT * FROM Names ORDER BY lname"
'Dim myCommand As New OleDbCommand(mySelectQuery,
myConnection, myTrans)
'myCommand.CommandTimeout = 20
Dim catCMD As OleDbCommand = myConnection.CreateCommand()
catCMD.CommandText = "SELECT * FROM Names"

myConnection.Open()

'************************************************************************************
Dim myReader As OleDbDataReader = catCMD.ExecuteReader()
'<<<<<--------- this line fails with error: An unhandled exception of
type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
'*************************************************************************************

Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}",
myReader.GetInt32(0), myReader.GetString(1))
Loop

myReader.Close()
myConnection.Close()

'If myReader.HasRows Then
' Do While myReader.Read()
' Console.WriteLine(vbTab & "{0}" & vbTab & "{1}",
myReader.GetInt32(0), myReader.GetString(1))
' Loop
'Else
' Console.WriteLine("No rows returned.")
'End If

'myReader.Close()


End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button2.Click
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String
objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data(Source
=C:\Documents and Settings\rb\My Documents\db3.mdb")
strSQLQuery = "SELECT * FROM Names"
objCommand = New OleDbCommand(strSQLQuery, objConnection)

'****************************************************************
objConnection.Open() '<<<<---- An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll
'****************************************************************
objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
objConnection.Close()
objDataReader.Close()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click
Dim objDs As DataSet
Dim ObjDa As OleDbDataAdapter
Dim objRow As DataRow
Dim objTable As DataTable
Dim objColumn As DataColumn

Dim sSQL As String
Dim sName As String
Dim sCustID As String
Dim sOrderID As String
Dim sOrderIDText As String

Dim sAddress As String
Dim sProduct As String
Dim sQuantity As String
Dim sTotal As String



'Me.Show()

sSQL = "SELECT fname From Names"
'sSQL = "SELECT * FROM (CUSTOMERS INNER JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMERID) "
'sSQL = sSQL & "INNER JOIN ORDERDETAILS ON ORDERS.ID =
ORDERDETAILS.ORDERID"
objDs = New DataSet
'Connect to database and specify sSQL

ObjDa = New OleDbDataAdapter(sSQL,
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Environment.CurrentDirectory & "\db3.mdb")

Try
ObjDa.Fill(objDs, "New")
Catch ex As System.Exception
Debug.WriteLine(ex)
Debug.WriteLine(ex.Message)
Throw ex
'*****************************************************
End Try '<<<--- An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in WindowsApplication7.exe
'******************************************************


For Each objRow In objDs.Tables(0).Rows
sAddress = "Name: " & objRow("fname").ToString & " " &
objRow("lname").ToString & CrLf & CrLf
TextBox1.Text += sAddress
Next
objDs.Dispose()
ObjDa.Dispose()

End Sub


Private Sub Button4_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button4.Click
Dim oConnection As New OleDbConnection
Dim oCommand As New OleDbCommand
Dim oDataReader As OleDbDataReader

oConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Environment.CurrentDirectory & "\db3.mdb"
oConnection.Open()

oCommand.Connection = oConnection
oCommand.CommandText = "SELECT * FROM Names"

'********************************************************************
oDataReader = oCommand.ExecuteReader() '<<<---An unhandled
exception of type 'System.Data.OleDb.OleDbException' occurred in
system.data.dll
'******************************************************************

While oDataReader.Read()
'Loop though data
End While

oDataReader.Close()
oConnection.Close()

End Sub
Please refrain from emailing me so the other
group members can gain from the info as well...

 

[Carl Prothman [MVP]]

Dim catCMD As OleDbCommand = myConnection.CreateCommand()

Try this instead:
Dim catCMD As OleDbCommand = New OleDbCommand(mySelectQuery, myConnection)

catCMD.CommandText = "SELECT * FROM Names"

Try this instead:
catCMD.CommandText = "SELECT * FROM [Names]"

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data(Source
=C:\Documents and Settings\rb\My Documents\db3.mdb")

Why do you have a "(" in the "Data Source" name?

sSQL = "SELECT fname From Names"
'sSQL = "SELECT * FROM (CUSTOMERS INNER JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMERID) "
'sSQL = sSQL & "INNER JOIN ORDERDETAILS ON ORDERS.ID =
ORDERDETAILS.ORDERID"

If you are using the NWIND.MDB, then try:

SELECT Customers.*, Orders.*, [Order Details].*
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID;

oCommand.CommandText = "SELECT * FROM Names"

oCommand.CommandText = "SELECT * FROM [Names]"

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Rob]

Carl,

I finally got it to read using the code below...now I need to get it
to write, but I'll try to work through it on my own.

Guess I need to get an ADO.NET book to add to my collection

Thanks again!

Rob

Dim myConnection As OleDbConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\rb\My Documents\db3.mdb")
Dim mySelectQuery As String = "SELECT * FROM [Names] ORDER BY
[lname]"
Dim catCMD As OleDbCommand = New OleDbCommand(mySelectQuery,
myConnection)
myConnection.Open()
Dim myReader As OleDbDataReader = catCMD.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(myReader("fname"))
Loop
myReader.Close()
myConnection.Close()

Dim catCMD As OleDbCommand = myConnection.CreateCommand()

Try this instead:
Dim catCMD As OleDbCommand = New OleDbCommand(mySelectQuery, myConnection)

catCMD.CommandText = "SELECT * FROM Names"

Try this instead:
catCMD.CommandText = "SELECT * FROM [Names]"

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data(Source
=C:\Documents and Settings\rb\My Documents\db3.mdb")

Why do you have a "(" in the "Data Source" name?

sSQL = "SELECT fname From Names"
'sSQL = "SELECT * FROM (CUSTOMERS INNER JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMERID) "
'sSQL = sSQL & "INNER JOIN ORDERDETAILS ON ORDERS.ID =
ORDERDETAILS.ORDERID"

If you are using the NWIND.MDB, then try:

SELECT Customers.*, Orders.*, [Order Details].*
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID;

oCommand.CommandText = "SELECT * FROM Names"

oCommand.CommandText = "SELECT * FROM [Names]"

Please refrain from emailing me so the other
group members can gain from the info as well...

 

[Rob]

For those that might need the code...here's what worked for me
inserting new records into an MDB.

Dim cn As OleDbConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\rb\My Documents\db3.mdb")
Dim myInsertQuery As String = "INSERT INTO [Names] ([fname],
[lname]) VALUES ('" & txtFirst.Text & "', '" & txtLast.Text & "')"
Dim catCMD As OleDbCommand = New OleDbCommand(myInsertQuery,
cn)
cn.Open()
Try
catCMD.ExecuteNonQuery()
Catch ex As OleDbException
End Try
cn.Close()
Please refrain from emailing me so the other
group members can gain from the info as well...

 

[Joe Fallon]

David Sceppa's book (ADO.NET) is great!
--
Joe Fallon
Access MVP

Carl,

I finally got it to read using the code below...now I need to get it
to write, but I'll try to work through it on my own.

Guess I need to get an ADO.NET book to add to my collection

Thanks again!

Rob

Dim myConnection As OleDbConnection = New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\rb\My Documents\db3.mdb")
Dim mySelectQuery As String = "SELECT * FROM [Names] ORDER BY
[lname]"
Dim catCMD As OleDbCommand = New OleDbCommand(mySelectQuery,
myConnection)
myConnection.Open()
Dim myReader As OleDbDataReader = catCMD.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(myReader("fname"))
Loop
myReader.Close()
myConnection.Close()

Dim catCMD As OleDbCommand = myConnection.CreateCommand()

Try this instead:
Dim catCMD As OleDbCommand = New OleDbCommand(mySelectQuery, myConnection)

catCMD.CommandText = "SELECT * FROM Names"

Try this instead:
catCMD.CommandText = "SELECT * FROM [Names]"

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data(Source
=C:\Documents and Settings\rb\My Documents\db3.mdb")

Why do you have a "(" in the "Data Source" name?

sSQL = "SELECT fname From Names"
'sSQL = "SELECT * FROM (CUSTOMERS INNER JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMERID) "
'sSQL = sSQL & "INNER JOIN ORDERDETAILS ON ORDERS.ID =
ORDERDETAILS.ORDERID"

If you are using the NWIND.MDB, then try:

SELECT Customers.*, Orders.*, [Order Details].*
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID;

oCommand.CommandText = "SELECT * FROM Names"

oCommand.CommandText = "SELECT * FROM [Names]"

Please refrain from emailing me so the other
group members can gain from the info as well...