locked
How to create dynamic connection for Ms Access database ? RRS feed

  • Question

  • User970350993 posted

    Hi

    I have to upload some records of Local Ms Access database to sqlserver database. It is working fine while writting the connection string like this.

    OleDbConnection AccessCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\airways1.mdb");

    But here source is hard coded.

    I did modification like this

    string vDbFile = System.IO.Path.GetFullPath(FileUpload1.PostedFile.FileName.ToString());

    OleDbConnection AccessCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + vDbFile);

    It is working  locally. But while uploading on server. It is not working.

    could you point me the error ? is it correct ?


    Thursday, July 23, 2009 6:47 AM

Answers

  • User-481631678 posted

    Make sure you are referring to correct location of database. Error indicates that connection string which you generate does not access the correct path. Make sure you are using Server.MapPath() function to get the physical file location.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 24, 2009 9:18 AM

All replies

  • User-481631678 posted

    I think in your local machine you are using VS internal server to run your application. When you host your application in IIS, it have some security restrictions. You need to give read/write rights on the folder where your file will be saved and accessed e.g.


    1-Give read/write rights to ASP.NET user to C:\Inetpub\vhosts\mysite\acecssFilesh\ folder
        1- Right Click on FOlder and Select Properties
        2- Goto Security Tab
        3- Click Add, then click Location.
        4- Select your pc name, usually first item.
        5- Press Ok, then press advance and then Find Now.
        6- Select ASP.NET user if not exist then select Authenticated Users.
        7- Press Ok. And Select Read/Write/Modify/List FOlder Contents
    2- Goto IIS->WebSites->DefaultWebSite->YourSiteName->Properties, and  at Virtual Directory Tab check 'Read' and 'Write' button.

    Friday, July 24, 2009 3:45 AM
  • User970350993 posted

    Thanks for sending the reply. While uploading the Recods of Ms-Access to sql server.

    I am getting the errors like This. I think the path of local database is not matching on

    remote server.

    Could not find file 'c:\windows\system32\inetsrv\airways1.mdb'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: Could not find file 'c:\windows\system32\inetsrv\airways1.mdb'.

    Source Error:

    Line 46:

    Line 47: DataSet ds = new DataSet();

    Line 48: da.Fill(ds);

    Line 49: string vSql = "";

    Line 50:

     

    Friday, July 24, 2009 6:44 AM
  • User2019981500 posted

    Hello Hreat Man

    Just check your server database path.i mean server on which you run it and change this path with server path[folder where your database is]


    then it will work fine,if not just let me know..
    if it is fine then just use server.MapPath(yourpath)

    Regards
    shabir hakim

    Friday, July 24, 2009 7:56 AM
  • User-481631678 posted

    Make sure you are referring to correct location of database. Error indicates that connection string which you generate does not access the correct path. Make sure you are using Server.MapPath() function to get the physical file location.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 24, 2009 9:18 AM
  • User-1800438376 posted

    Hi Experts,

     I am facing same type of problem 

    Please tell me how u solved the problem and how u formated u r vDbFile to a datasource


    Please help


    Monday, August 3, 2009 11:00 AM
  • User970350993 posted

    Hi

    my connection string is like

    string vDbFile = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName.ToString());

    string vFileExt = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName.ToString()).ToUpper();

    if (vFileExt != ".MDB")

    {

    return;

    }

    string vFilePath = (Server.MapPath("~\\MDB\\") + (vDbFile));

    FileUpload1.PostedFile.SaveAs(vFilePath);

    OleDbConnection AccessCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + vFilePath);

     

     

    AccessCon.Open();

     

    Firstly i was uploading the few record of Ms access to sql server. Then locally i was working but while uploading on server it was giving some errors. But now I am uploading the whole Ms access database to one folder i.e MDB.  and i also gave the full permission that MDB folder.

      

     

    Tuesday, August 4, 2009 6:41 AM
  • User-1800438376 posted

    Hi Boss,

      thanks for your reply, but..

          My requirement is i cant take my access file to MDB folder.

    Scenrio:-

       A lot of people punches, there punch record is saved in Access.mdb file, I have a browse.aspx which helps in browsing the Acess.mdb file and gathering only the fresh entered data by making use of flag(Yes/No).

        Updating the MDB folder Access file is of no use because the fresh punch record will come in Origional Access.mdb file So I have to update my origional file.



    Kindly help....


    Tuesday, August 4, 2009 8:38 AM
  • User970350993 posted

    Hi

    My scenario was also exactly same like you. One my friend created window based  tools for  creating fresh Access.mdb database for selected records of whole day. Before uploading on server, we are creating the Access.mdb. Now from file upload control we are appending the new recods in perivious Ms acess database on server.

     

     

     

    Tuesday, August 4, 2009 9:56 AM
  • User-1800438376 posted

    Sorry Boss,

      I am not getting which file u r updating, Can u tell me one thing is that possible...

    Tuesday, August 4, 2009 10:07 AM
  • User970350993 posted

    Hi

    Everyday  some new record will add in ms access database. But i have to upload only selected field so i have used window base tool to creat new database with selected field. Now i am uploading that database on server.

     you can solve this problem using so many methods.My first method was also working fine.By while uploading on server i was getting error. so i changed that method.  what type of errors are you getting ?

     

     

     

    Wednesday, August 5, 2009 12:36 AM
  • User-1800438376 posted

    Boss,


    This is my problem

    http://forums.asp.net/t/1453670.aspx

    Wednesday, August 5, 2009 1:06 AM
  • User172636902 posted

    Steps:

    1. First upload your MDB to server to temp folder or specified folder on server to which ASP.Net machine account has read/write permissions.

    2. Map that MDB file path using Server.MapPath into ur connection string.

    3. Provide appropraite provider for access db as per version.

    And go ahead.

    Thanks

    Wednesday, January 27, 2010 8:04 AM
  • User1456387794 posted

    Hi there i have got a similar problem!!!

    I have a page that brings back information from a database and also updates it but when i try to update it, it returns the error "Operation must use an updateable query"

    and highlights the following code

    objCmd.ExecuteNonQuery()

    I've searched for solutions and all i keep getting told is that when an .mdb is access it creates a .ldb which doesn't allow you to update it while this is opened.

    Is there anyway around this?


    Tuesday, June 29, 2010 8:22 AM
  • User-1199946673 posted

    Hi there i have got a similar problem!!!
     

    Next time. open a new thread!

    http://www.mikesdotnetting.com/Article/69/How-to-get-your-forum-question-answered-avoid-thread-jacking

    I've searched for solutions and all i keep getting told is that when an .mdb is access it creates a .ldb which doesn't allow you to update it while this is opened.

    Is there anyway around this?

    yes, by Setting the right permissions:

    http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error

    Tuesday, June 29, 2010 9:05 AM