Creating an ODBC connection to an access database

     
Setting up the database


The first step in your application is to set up the database. This example uses a Microsoft Access database, but any database will work with the schema you create here. There are actually two steps involved:

1      Create the database.
2      Set up the data source.

Read more : http://www.macromedia.com/devnet/dreamweaver/articles/webservice_07.html

------------------------------------------------------
Another good tutorial:
http://www.peachpit.com/articles/article.asp?p=29452&rl=1

what if the database is on a remote server

because I have set up the database but I always get this error

 Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
 

rudodoo,

This is the third time you've asked the same question.
Your original question is here : https://www.experts-exchange.com/questions/21788441/Difference-between-actual-database-name-and-DSN.html#16288140

When I get a chance I will work out exactly what you need to do and post the answer there, unless someone else has the opportunity before me.

Thank you

<cftry>
<cfquery dataSource="#Request.Datasource#" name="Test">
SELECT *
FROM YourTable
</cfquery>
<cfcatch>
<cfdump var="#Error#">
</cfcatch>
</cftry>

Datasource OK

I took out the <cfdump var="#error#"> and just put
<cf dump var="error" because it wasn't working correctly the other way.  However now it says that I have a

 Element RECORDCOUNT is undefined in TESTSEARCH.

Could you take a look at my code and see if there are some modifications that need to be made

<!--- Get mls list from database --->
<cfif isDefined("FORM.submit")>
<cftry>
<cfquery name="testsearch"  datasource="#Request.DSN#" username="userid" password="passcode">
SELECT mls.mlsid, mls.city, mls.state, mls.zip, mls.area, mls.price, mls.listing,
(SELECT TOP 1 mlspicpath FROM TblPicPaths WHERE mlsid = mls.mlsid) AS mlspicpath
FROM MLS
Where 0=0
<!---Search by mlsid or text fields--->
<cfif Form.mlsid IS NOT "">
AND mls.mlsid LIKE '%#FORM.mlsid#%'
</cfif>
<!---Search by CITY or list fields--->
<cfif Form.city is not "">
and mls.city = '#Form.city#'
</cfif>
<!--- search by state --->
<cfif Form.state is not "">
and mls.state = '#Form.state#'
</cfif>
<!--- Select zip --->
<cfif Form.zip is not "">
and mls.zip like '%#form.zip#%'
</cfif>
<!--- select area --->
<cfif Form.area is not "">
and mls.area ='#form.area#'
</cfif>
<!--- select price--->
<!--- select min price--->
<cfif Form.min_price is not "">
and mls.price >=#form.min_price#
</cfif>
<!--- select max price--->
<cfif Form.max_price is not "">
and mls.price <=#form.max_price#
</cfif>
<!---select listing--->
<cfif form.listing is not "">
and mls.listing ='#form.listing#'
</cfif>
</cfquery>
<cfcatch>
<cfdump var="#Error#">
</cfcatch>
</cftry>
</cfif>

Don't Forgot to change the <cfdump var="#Error#"> to <cfdump var="Error">

But on the other hand if you leave the ## variable tags in their I get an error saying that it is undefined

Looking back at your question - I think I posted in the wrong question or something ???
I could have sworn your question was How to determine if datasource if configured or setup or something like that.

Where are you using a #testsearch.recordcount#???
Don't see it anywhere.

farther on down the page. On line 332

It looks like this

<cfoutput><strong><font color="##D68C3A">#testsearch.RecordCount# Listings Matched Your Search Criteria</font></strong></cfoutput></td>

Heck it looks like you might have figured something out, I have been getting unable to connect these past few days

Instead of cf dump=#Error# I put cf dump=#variables# that looked like it did something I received a different error that goes back to element undefined in #Testsearch.RecordCount#

"How would I create an ODBC connection to an Access database using coldfusion?
what if the database is on a remote server"

You won't likely be able to do this your self unless your host provides a control panel of some sort.. Most hosts require you submit a request to create a datasource as you won't be allowed access to CFAdmin.

Email tech support and ask them to set up the DSN for you.

I have a control panel where I can select an option called directory management.

Here I can create a custom directory where I can select between 6 different types of user permissions

1.   Read Permission: This enables anonymous users to view the contents of a directory.

2.  Web Permission: This enables anonymous users to view files in a directory through a Web browser using HTTP.

3.  Browse Permission: This enables anonymous users to view all files in the directory (in directory listing format) by pointing their Web browser to the directory path. You must enable “Read” and “Web” permissions for "Browse" permission to work properly.

4.  Write Permissions: This enables anonymous users to modify the contents of a directory.

5.  Set Root: This enables you to set the directory/subdirectory folder as an application root in the Web server within your site. An application root is the root directory for an application. All directories and files contained within the application root are considered part of the application.

6.  Reset: Click this to reset the permissions for this directory to their default values.

I also have to type the directory name that I want to customize

which is what I am confused about.

Should I type in the directory where I have to store my access database

or

the root folder where my web-pages are stored

these are server settings (ie: folder access control) and not odbc settings. Just because the file is "accessible" doesn't mena that cf can make a connection to it. That has to be done via cfadmin.

I expect you'll need to contact your host and get them to set up an odbc for you.

err... set up a DSN for you

I have a dsn set up already.  I just would like to know how to configure the cf query to where I can submit the search form and have it display results from the dsn.

I already put the database in the correct folder

Then if you have set up your dsn correctly in application.cfm then there should be no problem


Looking at this from a different view...This tells me you've a problem with your query not with your dsn      


Element RECORDCOUNT is undefined in TESTSEARCH.


try this first...

<cfquery name="testsearch"  datasource="#Request.DSN#" username="userid" password="passcode">
SELECT * from mls
</cfquery>

<cfoutput>
#Testsearch.recordcount#
</cfoutput>

if that works we can move on to find out what's up with the query

Unfortunatly it didn't work.  I still received the same error

rudodoo
It looks like you do Not have your database / datasource setup correctly.
This is an access db - is that correct?
Verify where it is at (the folder location on your host)
Go into your "admin section" on your host where you setup your datasources.
Try deleting the existing datasource (not database) and recreate it again.
When you are selecting database for your datasource - browse to it instead of typing it in (if you can) - this will eliminate misspellings.
You realize the slightest of misspellings will be the same as if you had an entirely different datasource name.
Also, double check the value you have on your site for Request.DSN - make sure it is not misspelled.
Also, do the query without the username & password in your cfquery tag:
<cfquery name="testsearch"  datasource="#Request.DSN#">

If this has been covered in previous posts - I apologize.

Yes it is a access db file

I can't browse to the datasource like you suggested but I have double checked to make sure of its locations and they are correct


 I also create just one line in the application.cfm page <cfset request.dsn="MyDataSource">

I also created a .mdw file that I uploaded into the same folder as the access database

I recreated the datasource and have double checked the values and still it is unable to define a recordcount in testsearch

What about the cf dump="variables"

Could I delete that from my above code? I also deleted the username and password.  However I created one in the access database

Remove the username / password from the access database
What is an .mdw file?

Use Exactly this:
<cfquery dataSource="#Request.DSN#" name="Test">
SELECT *
FROM YourTable
</cfquery>
Hello
<cfabort>

Do you get an error?

an mdw file is irellevant in a CF setting...it's ONLY used by access when using access as a front end....


" I also create just one line in the application.cfm page <cfset request.dsn="MyDataSource">"

if you actually used "MyDataSource" as the name it will fail...you need to set the dsn to the dsn that was set up by your host

cfdump has no effect on anything... it simply outputs then variables referenced///it is a debugging tool and nothing more...you can delete all references and it will make no difference to your code.

a .mdw file is the workgroup information file that I was told was missing from the remote server.  Which previously stopped me from connecting to the access database or so I was informed

No I didn't get an error!!!!  How can I configure that solution with the original script that I created?

Just to refresh your memory

<!--- Get mls list from database --->
<cfif isDefined("FORM.submit")>
<cftry>
<cfquery name="testsearch"  datasource="#Request.DSN#" >
SELECT mls.mlsid, mls.city, mls.state, mls.zip, mls.area, mls.price, mls.listing,
(SELECT TOP 1 mlspicpath FROM TblPicPaths WHERE mlsid = mls.mlsid) AS mlspicpath
FROM MLS
Where 0=0
<!---Search by mlsid or text fields--->
<cfif Form.mlsid IS NOT "">
AND mls.mlsid LIKE '%#FORM.mlsid#%'
</cfif>
<!---Search by CITY or list fields--->
<cfif Form.city is not "">
and mls.city = '#Form.city#'
</cfif>
<!--- search by state --->
<cfif Form.state is not "">
and mls.state = '#Form.state#'
</cfif>
<!--- Select zip --->
<cfif Form.zip is not "">
and mls.zip like '%#form.zip#%'
</cfif>
<!--- select area --->
<cfif Form.area is not "">
and mls.area ='#form.area#'
</cfif>
<!--- select price--->
<!--- select min price--->
<cfif Form.min_price is not "">
and mls.price >=#form.min_price#
</cfif>
<!--- select max price--->
<cfif Form.max_price is not "">
and mls.price <=#form.max_price#
</cfif>
<!---select listing--->
<cfif form.listing is not "">
and mls.listing ='#form.listing#'
</cfif>
</cfquery>

"No I didn't get an error!!!!  How can I configure that solution with the original script that I created?"

Are you saying it's working now?
Or what do you mean by No I didn't get an error?

If you're still getting errors - it's pointless to configure original script.

If not getting errors then try the query you have. It looks ok on the surface ???

No I wasn't getting any errors  I received a blank page that read hello

Well you should be cooking with heat now with no errors.

Use Exactly this:
<cfquery dataSource="#Request.DSN#" name="Test">
SELECT *
FROM YourTable
</cfquery>
<cfdump var="#test#">
<cfabort>

Do you get an error or does it dump the query?

yes it shows all the info from the database

so i guess it dumps the query

I got it working I appreciate it you get the points