Access database connectivity

[Tomas]

I am trying to get data from an access database but am getting an error that
the database is either opened exclusively or I need permission to view the
data. I configured an oledbdataadapter and can see the data when I preview
it, but when I try to fill the dataset during pageload() I get the previous
error.

Thanks
Tomas

[Michael Murphy]

Hi Tomas,
I am having the same problem trying to use an Access DB. If and when you
find the solution, would you be kind enough to email it to me? I will do
the same.
Thanks,
Michael

"Tomas" <vb.@127.0.0.1> wrote:
>
>I am trying to get data from an access database but am getting an error
that
>the database is either opened exclusively or I need permission to view the
>data. I configured an oledbdataadapter and can see the data when I preview
>it, but when I try to fill the dataset during pageload() I get the previous
>error.
>
>Thanks
>Tomas

[Michael Murphy]

Tomas,
I have found a block of code that opens both SQL Server data and MS Access
Data. All you have to do is change the paths to the SQL and Access databases
and add these statement to top of module1 of a new Console App:
Imports System.data.Sqlclient
Imports System.data.OleDb

============================================================
Populating a DataSet from Multiple DataAdapters
Any number of DataAdapters can be used in conjunction with a DataSet. Each
DataAdapter can be used to fill one or more DataTable objects and resolve
updates back to the relevant data source. DataRelation and Constraint objects
can be added to the DataSet locally, enabling you to relate data from multiple
dissimilar data sources. For example, a DataSet can contain data from a Microsoft
SQL Server database, an IBM DB2 database exposed via OLE DB, and a data source
that streams XML. One or more DataAdapter objects can handle communication
to each data source.

The following code example populates a list of customers from the Northwind
database on Microsoft SQL Server 2000, and a list of orders from the Northwind
database stored in Microsoft® Access 2000. The filled tables are related
with a DataRelation, and the list of customers is then displayed with the
orders for that customer. For more information about DataRelation objects,
see Adding a Relationship between Tables and Navigating a Relationship between
Tables.

[Visual Basic]
Dim custConn As SqlConnection= New SqlConnection("Data Source=localhost;Integrated
Security=SSPI;" & _
"Initial Catalog=northwind;")
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers",
custConn)

Dim orderConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=c:\Program
Files\Microsoft Office\" & _
"Office\Samples\northwind.mdb;")
Dim orderDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Orders",
orderConn)

custConn.Open()
orderConn.Open()

Dim custDS As DataSet = New DataSet()

custDA.Fill(custDS, "Customers")
orderDA.Fill(custDS, "Orders")

custConn.Close()
orderConn.Close()

Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"),
_
custDS.Tables("Orders").Columns("CustomerID"))

Dim pRow, cRow As DataRow

For Each pRow In custDS.Tables("Customers").Rows
Console.WriteLine(pRow("CustomerID").ToString())

For Each cRow In pRow.GetChildRows(custOrderRel)
Console.WriteLine(vbTab & cRow("OrderID").ToString())
Next
Next
[C#]
SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);

OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
+
"Data Source=c:\\Program
Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");
OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);

custConn.Open();
orderConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");

custConn.Close();
orderConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],

custDS.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(pRow["CustomerID"]);
foreach (DataRow cRow in pRow.GetChildRows(custOrderRel))
Console.WriteLine("\t" + cRow["OrderID"]);
}
"Tomas" <vb.@127.0.0.1> wrote:
>
>I am trying to get data from an access database but am getting an error
that
>the database is either opened exclusively or I need permission to view the
>data. I configured an oledbdataadapter and can see the data when I preview
>it, but when I try to fill the dataset during pageload() I get the previous
>error.
>
>Thanks
>Tomas