Solved

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

Posted on 2010-08-27
14
956 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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