Ms access reading data from c# code is giving unspecified error

The unspecified error you are encountering while reading data from MS Access in your Windows service may be related to permission issues or temporary directory access. When you restart the VM, it likely resets the environment, allowing the service to access the necessary resources correctly.

To troubleshoot this issue, consider the following steps:

1. Check Permissions: Ensure that the process identity used by your Windows service has the necessary permissions to access the MS Access database and any temporary directories it may need.
2. Modify Temporary Directory Settings: If you are using IIS, ensure that the temporary directory permissions are set correctly. You can adjust the discretionary access control list (DACL) for the temporary directory to allow the service to write to it. Use the following command:

      icacls %systemroot%\serviceprofiles\networkservice\AppData\Local\Temp /grant Users:(CI)(S,WD,AD,X)
   

3. Check for Missing References: If your code relies on specific libraries or components, ensure they are correctly registered and available. Missing or incorrectly registered components can lead to errors when trying to access the database.
4. Re-register DLLs: If you suspect that a DLL might not be registered correctly, you can re-register it using the regsvr32 command.

By following these steps, you should be able to identify and resolve the underlying issue causing the unspecified error when accessing the MS Access database.

References:

• How to resolve reference issues in an Access database
• ASP and Microsoft Access Database Failures

Hi @Dasu, Siva (Dasu,Siva)

Thank you so much for contacting Microsoft Q&A Forum.

Based on your description, I understand you were facing an issue when your C# Windows Service tried to read data from MS Access, and this issue was resolved after restarting the VM.

Can I please confirm one more time: after you restarted the VM, is the issue now fully resolved? Have you experienced any further problems since then?

I need a bit more information about your current request, but in the meantime, I've researched some troubleshooting steps that might explain what happened and could be helpful for you in the future. Can you describe in more detail when and how the error occurred?

Here are the most common causes for this behavior:

 1. Check Bitness Mismatch (32-bit vs. 64-bit)

MS Access (via the ACE OLEDB provider) relies on COM components that expect a "Desktop" folder in the system profile paths. Services running under accounts like LocalSystem use these paths. This mismatch can cause unexpected behavior.

 2. Permissions and File Locks on Database and Temp Folders

Access databases (.accdb or .mdb) can lock up due to improper connection handling, leading to intermittent failures that are resolved by restarts.

Please ensure the service account (e.g., LOCAL SYSTEM or whichever account your service runs as) has full read/write permissions on:

• The .accdb/.mdb file and its folder.
• The lock file (.laccdb or .ldb).
• The temp folder used by the system account (e.g., C:\Windows\Temp or C:\Windows\System32\config\systemprofile\AppData\Local\Temp). The ACE driver creates temp files (like ~jet*.tmp) that can accumulate and cause issues.

3. Ensure Proper Connection Handling (Code)

If connections are not closed properly, they can "leak" and keep the database file locked, preventing new connections. In your C# code, you must use using statements to ensure connections and commands are disposed of, even if an error occurs. For example:

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=yourdb.accdb;")) 
{
    conn.Open();     using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM YourTable", conn)) 
    { 
        // Read data 
    } 
} // <-- Connection is automatically closed here, even if an error happened

Due to limitations in my environment, I can't test this directly. The steps above are what I've found based on your description. Please let me know if the issue returns or if you have more information. I hope this is helpful for you in the future.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.