none
ODBC or OLEDB for COnnecting to Access ?? RRS feed

  • Question

  • Using ASP.NET 2.0, C# Windows application, I have a project which requires connecting to and updating an MS Access 2003 database. The data access/updating is done with the standard query strings

     I am used to connecting to SQL Server using native provider.

    What is the difference  between ODBC and OLEDB? Which has better performance, fewer pifalls, etc.

    I have gotten this to work with a DSN-less ODBC connection, but want to explore the possibilities before I get much farther.  Does anyone have opinions on this, are there any articles to be recommended?

    Many thanks
    Mike Thomas


    Thursday, March 27, 2008 12:16 AM

Answers

  • Just a few comments concerning using an Access database in this scenario...

    If you're experiencing a fair amount a concurrent usage then Access is somewhat limited in this respect and prone to corruption. As suggested, SQL Server Express might be a better option. If the ASP.NET app is of reasonably low usage then Access is probably OK, although you will want to periodically compact the database.

    If you do decide to use Access then the Jet OLEDB provider is preferred. It's more stable and current with respect to features in comparison to the first generation Microsoft Access ODBC driver.

    Monday, March 31, 2008 12:49 PM

All replies

  • Do you have a database choice or ability to migrate MS Access database to SQL Express? If so I'd strongly advise you to use SQL Express instead of MS Access. In this case you'll be able to use .NET SqlClient which gives you a better performance in the managed environment.
    Thursday, March 27, 2008 9:25 PM
  • How do you do reporting in SQL Server 2005 Express?

     

    How do you do forms in SQL Server 2005 Express?

     

    How do you do charts in SQL Server 2005 Express?

     

    Access 2007 is the BEST lightweight relational database.

    Friday, March 28, 2008 1:13 AM
  • Thanks Oleg,  I had not thought about SQL Express for awhile.  I may look into it over the weekend.  One of my problems is that I like to steer clear of the DBA's at my client's company.  My guess is that they would make us use use SQL Server, which would be great, but open a huge mess of red tape on a really simple application.

    Mike Thomas
    Friday, March 28, 2008 12:26 PM
  • Since I am using C# ASP.NET, I use rdlc reports.  It's a Window FORMS app.  Ditto for charts.  I went from FoxPro to Access to ASP.NET and am REALLY glad to get out of the Access world. 

    Many thanks
    Mike Thomas
    Friday, March 28, 2008 12:30 PM
  • You might want to read about SQL Server Reporting Services http://msdn2.microsoft.com/en-us/library/ms159106.aspx. Reporting Services are available in "SQL Express with Advanced Services" http://www.microsoft.com/sql/editions/express/comparison.mspx

     

    Since you have a C# application it's the best to use SQL Server as the back-end and Reporting Services to do your reports and charts (unless the cost of transition is too high).

    Friday, March 28, 2008 6:43 PM
  • Just a few comments concerning using an Access database in this scenario...

    If you're experiencing a fair amount a concurrent usage then Access is somewhat limited in this respect and prone to corruption. As suggested, SQL Server Express might be a better option. If the ASP.NET app is of reasonably low usage then Access is probably OK, although you will want to periodically compact the database.

    If you do decide to use Access then the Jet OLEDB provider is preferred. It's more stable and current with respect to features in comparison to the first generation Microsoft Access ODBC driver.

    Monday, March 31, 2008 12:49 PM
  • Many thanks Paul; a very helpful answer.
    Mike Thomas
    Saturday, April 5, 2008 11:43 PM