Going from system DSN to DSN-less connection

[Guest]

Hello,

This is my first time developing a dynamic site. I'm working with Dreamweaver MX, using ASP and VBScript, and a Microsoft Access database. I was working with a local server using a system DSN and everything was working well. But now I'm trying to host this website in a remote server, to which I have no physical access, and do not know the full path of my "root folder". So I went back to Dreamweaver, thinking it would be really easy to change to a DSN-less connection by simply edditing the Connections file... here problems begin.

My connection string looks like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Driver={Microsoft Access Driver(*.mdb)}; Data Source =" & Server.MapPath("\db\db2.mdb") & "UserID=;Password=;"

My connection file (connEIN.asp, included in all my asp files) looks like this right now:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_connEIN_STRING
MM_connEIN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; Driver={Microsoft Access Driver(*.mdb)}; Data Source =" & Server.MapPath("\db\db2.mdb") & "UserID=;Password=;"
%>

These are the errors I get:

1 - while oppening an .asp file in Dreamweaver: "The DynamicSelectList script does not define the findServerBehavior function."

2 - while testing the connection in Dreamweaver, or in a browser: "Microsoft JET Database Engine (0x80004005)
Could not find installable ISAM."

Things I've tryed and failed:

a) I've also used DBQ instead of Data Source, but it does not seem to make any difference.

b) I've also tryed erasing Server.MapPath, and writing the full path (since I'm now working in a local server I control), but it still doesn't work.

c) I've also tryed rewriting my connection file into this format after reading some scatered foruns, but it didn't work either:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_connEIN_STRING
set MM_connEIN_STRING = Server.CreateObject("ADODB.Connection")
MM_connEIN_STRING.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn = "Driver={Microsoft Access Driver(*.mdb)};DBQ =" & Server.MapPath("/einservices/db/db2.mdb")
MM_connEIN_STRING.Open conn
%>

I've wasted hours browsing and searching for an answer, and trying tinny changes. I would really appreciate help from someone who has successfully changed from a system DSN to a DSN-less connection.

Thanks in advance!

[bregent]

A dsn-less connection should be as simple as:

MM_AccessTest_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.Mappath("/site1/database/mdbtest.mdb")

I believe you NEED to use forward slashes, not backslashes.

Check to see if MMHTTPDB.asp has been uploaded into your _mmServerScripts folder.

And before proceeding too far, are you sure you want to go with MS Access? You do realize it will only work for sites with very small volumes as it does not handle concurrent connections very well.

[Lon_Winters]

Yes, some hosting providers are more MS Access friendly than Krueger. The DB file should not be in your site folder for security reasons, and the host shold give you easy acess to put it where it belongs and provide the correct path. And as mentioned, if you're on a windows platform, chances are they also provide MSSQL - very easy to convert your Access database into this.

[Guest]

Thank you both, Lon Winters and bregent for your feedback.

I'm gonna go ahead with MS Access because it's the only database I know

for now, and because I don't expect a lot of traffic in the begining. I

hope it will allow me to test my site concept. If it becomes successfull, I

will think about "upgrade it" to another DB type.

Take care

[Lon_Winters]

One thing I like about MSSQL databases is that you can manage them using Access itself. This is done by creating an Access project file with an ADP extension. It presentsna dialog for the server connection which uses the same information as the connection string. Once connected, you can manage the database the same way as you would a regular Access database in almost every aspect. You can import and export, create tables and Queries, etc. One noticeable difference is that you're presented a lot more options for datatypes for your table fields. One downside is that the web Hist must allow remote connections which some do not for security reasons. Another is that I don't know how applicable this would be in a ASP.net environment, but that's not supported by DW and even classic ASP is fading fast.  Beyond MS-based solutions is PHP with MySQL, a different animal in many respects but conceptually similar to the overall principles of dynamic websites. I still manage a small number of ASP sites, and a couple years ago decided I was overdue in choosing which technology I needed to learn. I went  the PHP route because I still wanted to use DW and many Web Assist extension which many now only support PHP. (not to say I won't ever delve into dot net, but I need to be able to hand code PHP at least to the extent that I can with VBscript)

[Guest]

Cool. Thanks for the insights.

[Guest]

Thank you very much bregent! You fixed it!

First I checked the MMHTTPDB.asp. It was where it was supposed to be.

Then I started to simplify my connection string to bring it closer to

what you had written, trying to pinpoint the problem. I started getting a

new error about TeamAdminTepDelete.htm. So I started thinking I might have

accumulated errors. I deleted the FileCache.dat file and used the

connection string with the componentes exactly as you wrote and it worked

just fine!

Thanks a lot!

Take care

Citando bregent <forums@adobe.com>:

>> Before proceeding any further, are you sure you want to go with MS

> Access? You do realize it will only work for sites with very small

> volumes as it does not handle concurrent connections very well.

>

>

[bregent]

Great news!