Creating a DSN less Connection for ASP

Generally, a DSN less connection is a better and faster connection to a database.
Also, in many cases web hosting companies only allow you to have one DSN connection setup which limits you to a single database on your site. But what if you need more than one and are reluctant to upgrade you hosting plan to a much more expensive one. Well in that case, DSN less connection is your ticket to happiness babe!

Assuming that you have already created an MS Access database and that your server like IIS or PWS is running,
we'll proceed as follows:

1. Create a new page in ultradev or open one that you have already created.
2. Now go to the Modify menu and click on Connections. The connection box for site "your site name" comes up.
3. Click on the new button and select "Custom Connection String".
4. In the "Connection Name" text field enter the name of you database without the extension. Like this: mydatabase and not mydatabase.mdb.
5. OK. We're doing good so far. In the "Connection String" text field enter the following:
   Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\wwwroot\Academy\curriculum.mdb
   Where the URL after DBQ= in the above line is the exact URL of the location of the database you are trying to connect to.
   The easy way to find the exact address is by simply navigating to the folder on your computer where the database is located.
   In the windows address bar on top you will see the URL of the folder which you can copy and then paste after the DBQ= shown above. Then you will have to add the name of the database at the end. So, for example if your database named "mydatabase.mdb" is located on the C: drive in a folder called "mysite", then the above code would look like this:
   Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\mysite\mydatabase.mdb Pretty easy ehhh!
6. Now click on the "Test" button to test your connection. It should work just fine. If it isn't, then check the URL you entered
   after DBQ=. Make sure that it is correct. If it is, your connection should test just fine.
7. Click OK and you are done creating a DSN less connection. In you site's root folder there should be a folder now called Connections and an .asp file inside it called whatever you have typed in the "Connection String" text field.
8. Now, the page you have opened in the beginning should still be open in front of you. Click on Ctrl-F9 to bring up the Server Behavior window if it is not already open.
9. Click on the "Data Bindings" tab, then click on the plus sign and choose "Recordset (Query)".
10. In the "Name" text field type the name of the database (again without the extension)
11. In the "Connection" list menu choose the connection you have just defined. It should show the exact same name as the name of the .asp file in the Connection folder in your site root.
12. Choose the table you are going to work with and click the test button to test your connection. You can also define your filter settings according to your needs. Now everything should work OK. Click the OK button and you are done creating a recordset.
13. Now here comes the tricky part. All dynamic pages you create in this site should deploy just fine on your computer but if you were to upload all your files to your ISP's server, they would not work because the connections file would be looking for the database at the URL which you have defined in the connection string.
14. So here is what I usually do (and I am sure that there are better ways, but this has allays worked for me).
    When you are done working on your files and ready to upload them to the server, open the .asp file in the connections folder and change the URL after the DBQ= to the exact path to the database on your server. You may want to ask you service provider for the exact path you should use. If you are a beginner at the ASP game this would be the easier approach to take. If you like figuring things out for yourself, you can create a test file with a Server.MapPath code to find out where your database is located. To do this follow the steps below.
    • Create a web page containing the following ASP code:
      <%LANGUAGE="VBSCRIPT"%>
      <%=Server.MapPath("YourDatabaseNameHere.mdb;")%> This assumes that your database is already uploaded to the server.
    • Save it as test.asp and upload it to your web server/host and then pull it up in your browser.
    • This should return a URL something like this d:\64.226.219.24\YourDatabaseNameHere.mdb;
    • Now copy and paste this URL into your connection file after the DBQ=. Now your code should look like this:
      Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\64.226.219.24\YourDatabaseNameHere.mdb; Save and close the file.
    • Now upload the connection file to your web server/host along with your other files and you are ready to go. All your dynamic pages should work now on you web site.

Enjoy!

Tamas Bakody