How to create dynamic connection for Ms Access database ?

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 ?

Answer 1

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.

Answer 2

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:

 

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.

  

 

Answer 7

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

Answer 8

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.

 

 

 

Answer 9

User-1800438376 posted

Sorry Boss,

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

Answer 10

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 ?

 

 

 

Answer 11

User-1800438376 posted

Boss,

This is my problem

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

Answer 12

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

Answer 13

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?

Answer 14

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