Solved

Could not use ''; file already in use error when opening Access database

Posted on 2010-08-27
14
913 Views
Last Modified: 2014-01-06
I have a dropdown on my page that gets populated from Access dataabse. I am not using AccessDataSource but instead just using OleDbConnection object. I keep getting "Could not use ''; file already in use" error. Sometimes it works but most of the time that error comes up.  I gave ASPNET user full control to the folder where my mdb file is stored.

here is my connection string and code below:

 <add name="conn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb;" providerName="System.Data.OleDb"/>
public OleDbConnection GetConnection(string strConn)

    {

        OleDbConnection conn = null;

        try

        {

            conn = new OleDbConnection(strConn);

            conn.Open();

        }

        catch (OleDbException ex)

        {

            Console.Write("OleDb ERROR: " + ex.Message);

        }

        catch (Exception ex)

        {

            Console.Write("ERROR: " + ex.Message);

        }

        return conn;

    }

Open in new window

0
Comment
Question by:YZlat
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541485
It might be that you missed to close the connection once you are done with it. Please make sure you close the connection in all scenarios.

You would find a mdb lock file under the the same folder where ur mdb file is sitting. please delete that file and you should be all set.

If you are not able to delete the file. close the application and try.
0
 
LVL 35

Author Comment

by:YZlat
ID: 33541519
The Access application is closed at all times and I do clode the connection after reading data;
cmd.ExecuteReader(CommandBehavior.CloseConnection);
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541560
are you closing your data reader? I meant closing your .net application and the access application. close your asp.net development servers sitting in the system tray and try deleting the lock files if any.
0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33541620
Hi,

Please check link:
http://support.microsoft.com/kb/289681

Regards,
VSS
0
 
LVL 35

Author Comment

by:YZlat
ID: 33541637
Yes, I am closing the data reader.

Also what i noticed is that sometimes app loads data into dropdown fine and when I select an item from a dropdown, it displays data on the page, but if I try to select another item the second time around, it also gives me this error. What can i do to fix it?
0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33541648
Hi,

You didn't respond for link.

Regards,
VSS
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 33541657
Could you post your code ? So we could understanding what is happening around
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Author Comment

by:YZlat
ID: 33541910

public OleDbDataReader ExecuteSimpleQuery(string query, string strConn)

    {

        OleDbConnection conn = GetConnection(strConn);

        OleDbDataReader rdr = null;

        try

        {



            using (OleDbCommand cmd = new OleDbCommand(query, conn))

            {

                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }

        }

        catch (Exception ex)

        {

            Console.Write("Error executing query " + query + ": " + ex.Message);

            rdr.Close();

            rdr = null;

        }



        return rdr;

    }

Open in new window

0
 
LVL 35

Author Comment

by:YZlat
ID: 33541921
vs00saini, the database is not on the server. Instead it's on the same machine as the web application. It's an access database
0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33542143
Hi,

I agree that database is not on the server. But however you are creating web application. Then either you are using inbuilt server of VS or your system would be considered as server in that case.

Is your same folder is having .ldb files to prevent different users to access same resource at a time.

Regards,
VSS
0
 
LVL 35

Author Comment

by:YZlat
ID: 33543008
It's not a permissions issue, i checked before I posted the question here
0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 500 total points
ID: 33564970
Hi,

After carefully reading your both following points:

(1) here is my  connection string and code below:

 <add name="conn"  connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=C:\Northwind.mdb;" providerName="System.Data.OleDb"/>


(2) Also what i noticed is that sometimes app loads data into dropdown fine  and when I select an item from a dropdown, it displays data on the page,  but if I try to select another item the second time around, it also  gives me this error. What can i do to fix it?

I reached at some conclusion that:

(1) (This is my answer based on my development experience and knowledge) Rectify me if I am wrong at some point. After all mistakes are part of learning...

Your Data Source is at location C:\Northwind.mdb, so what actually is happening behind the scene is that when you are creating connection, and using the file (with extension .mdb), you are actually using this file handle (& yes I agree that .Net automatically manages this) but sometimes if the same file's handle is in memory, and in between if GC.Dispose() is not called, you would get error of above type (sometimes you also get file is used in by another process). So what should be the solution for this: Try the code as:

public OleDbDataReader ExecuteSimpleQuery(string query, string strConn)
    {
        OleDbConnection conn = GetConnection(strConn);
        OleDbDataReader rdr = null;
        try
        {

            using (OleDbCommand cmd = new OleDbCommand(query, conn))
            {
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        catch (Exception ex)
        {
            Console.Write("Error executing query " + query + ": " + ex.Message);
            rdr.Close();
            rdr = null;
        }

        OleDbConnection conn = null; //So that all resources are released.
         OleDbDataReader rdr = null;

        return rdr;
    }

(2) See all comments of gopijoshi                                  at link: http://forums.aspfree.com/microsoft-iis-12/could-not-use-file-already-in-use-error-strange-variation-34803.html

(3) And last if your database is corrupted then also the same problem comes. So better to check if it is not corrupted.

Let us know if the problem still persists or solved out.

Regards,
VSS
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 33637243
I actually didn't have to change anything in my code, it just started working
0
 

Expert Comment

by:dejandejanovic
ID: 39759071
FINALLY SOLUTION ALSO FOR ME.
To reset all resources, posted by Vikram was helpful form.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now