Connection string for Ms Access Database

Question

My Ms-Access .mdb file is on my website.
It is in the App_Data Folder

The current connection string is:

OleDbDataAdapter Da = new OleDbDataAdapter("Select * from SerialNo", 
           new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; 
           data source=ftp://ftp.WebsiteName.com/App_Data/SerialNo.mdb"));

Where am I making a mistake in above connection string?

Note: CRs added in the code for better readability

Answer 1

Agreed it should be on SO.

use: ~/App_Data/SerialNo.mdb for the data source in your connection string above.

Answer 2

Access (or the access drivers) doesn't understand about FTP - or hostnames for that matter and it expects to be able to open the database as if it's is on your local disk (or on a network drive that's visible to you). So you need to remove the hostname stuff and provide the driver with an absolute or relative path such as 'c:\inetpub\wwwroot\myWebsite\App_data\SerialNo.mdb'. The next trick is that you'll want to determine that path at runtime so that it's not hardcoded. There are a variety of functions that let you convert from absolute<->relative file paths and one such method is described here http://stackoverflow.com/questions/867485/c-getting-the-path-of-appdata

If you're using Visual Studio >=2008 and datasources and the data is all defined in the solution you may also get away with the "|DataDirectory|" directive which should/may/won't expand to the proper path depending on your version of VS, DB drivers and other things. I tend to stick with more manual approach.

And for future reference, connectionstrings.com is just about the best resource for getting connection string examples.

Cheers