Topic: Connecting to your ODBC Connecters, MySQL and MSACCESS

[marcon3001]

   Hello.  We recently set up a windows testing host site with hostned to establish database connectivity online with a MS ACCESS database.  I went in through plesk and created the ODBC dns connection to MS ACCESS.  It has a good path, being that the 'test' buttons says it connected to the .mdb file.  There was no password established or username for the access mdb (blank fields) so I didn't create one for the ODBC controller.  That was it, than I made sure all the permissions were read/write for the ACCESS folder and the surrounding folders.  I was unable to set permissions on the httpdocs folder, which is standard, I believe.
     So I then connected to the website remotely from my Dreamweaver, create an FTP link to the site with all usernames and passwords, go to create a database connection through DNS, go into the ODBC dialog box and check for connections...after a moment I get an error that says there might be a permissions problems or the dreamweaver failed because the server map path didn't link up to the _mmServerScripts folder.... so no ODBC connections show up and I can never connect to them.  Does anyone have an idea what I am missing?
     There is another type of connection called custom connection string....should I be using that?  Thanks for any help...MW

[ctwjr]

I am not a dreamweaver user, so I probably cannot help you with the specifics of that program usage.  I try to use MySQL database whenever possible since it is designed to be used over the internet.  Although Microsoft allows you to use Access on the internet, it is still not the best choice.  For those times I just have to use Access database, I find that creating an ODBC DSN is the best way.  I belive "custom connection string" is just Dreamweaver's lingo for DSN-less connection.  I guess you can use DSN-less; it is just that I always get frustrated with them and people I know feel the same way about them.

Anyways, this might help you.  I find that for troubleshooting it is good to create a "testing" connection to the database.  This just bypasses all the other things that could be throwing the error messages and will at least allow you to discover if the permissions are set right.
Create an ODBC DSN Database Connection
First use your control panel to create the DSN.  If you have a DSN (data source name) connection called "apples" you can connect to the database with the following ASP code:

Code: [Select]

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "apples"
%>

With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.  For this reason, I find it more secure to name my ODBC database DSN connections something more complex like apples12763.
Create a DSN-less Database Connection
Another way to connect to a database is to use a DSN-less connection.  Some people find it easier but I do not, mainly because it relies on server mappath.  You can use a DSN-less connection for any Microsoft Access database on your web site.

If you have a database called "apples.mdb" located in a web directory like "c:\webspace\user\domain.com\private\", you can connect to the database with the following ASP code:

Code: [Select]

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:\webspace\user\domain.com\private\apples.mdb"
%>

Take notice from the example above that you have to specify the Microsoft Access database driver (Provider) and the actual physical path to the database on your server.
So try making a test connection to your db using these examples (save the code as dbtest.asp or something like that) and if it connects there will be no message.  If it cannot connect then there will be an error message displayed in the web browser.  I find that for some reason, firefox browser displays more accurate error messages than IE.

Hope this helps you.

[marcon3001]

I see.   I just worry about a DSN less connection because Dreamweaver has a dynamic update live data feature where you can see your database records, and I am not sure how it will work if it doesn't have the DSN connection.

I did finally get it to work, however.  I created an FTP connection to the site with just the IP address and login info for the remote files, and then an FTP connection to the site for the testing server, again with the IP address and login info, but for the testing server, I had to set the URL prefix at the bottom to my webuser account space, which used a ~ character to map to the web-user folders on my serverspace from the web. 

And then I had to set the passwords for the databases (MySQL) to be the same as the webuser passwords and set permissions on the web-user folders to be read/write for the same user passwords.  I notice HostNed under windows seems to have 2 entrys for usernames, a Plesk username and a IUsr_ username, so I set the permissions for both of the to read/write through the file manager.

After all this, dreamweaver connects up.  But the whole file structure will change if I use subdomains as opposed to web users....then I will have to figure it out again.

The weird thing is the virtual network structure....you have to know the syntax to get to your subdomains/webuser folders online