Access database connectivity


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Access database connectivity

  1. #1
    Tomas Guest

    Access database connectivity


    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

  2. #2
    Michael Murphy Guest

    Re: Access database connectivity


    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



  3. #3
    Michael Murphy Guest

    Re: Access database connectivity


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center