Creating an ODBC connection to an access database

How would I create an ODBC connection to an Access database using coldfusion?

rudodooAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pinaldaveCommented:
     
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
rudodooAuthor Commented:
what if the database is on a remote server
rudodooAuthor Commented:
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.
 
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mr_NilCommented:
rudodoo,

This is the third time you've asked the same question.
Your original question is here : http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21788441.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.
rudodooAuthor Commented:
Thank you
dgrafxCommented:
<cftry>
<cfquery dataSource="#Request.Datasource#" name="Test">
SELECT *
FROM YourTable
</cfquery>
<cfcatch>
<cfdump var="#Error#">
</cfcatch>
</cftry>

Datasource OK
rudodooAuthor Commented:
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>
rudodooAuthor Commented:
Don't Forgot to change the <cfdump var="#Error#"> to <cfdump var="Error">
rudodooAuthor Commented:
But on the other hand if you leave the ## variable tags in their I get an error saying that it is undefined
dgrafxCommented:
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.
rudodooAuthor Commented:
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>
rudodooAuthor Commented:
Heck it looks like you might have figured something out, I have been getting unable to connect these past few days
rudodooAuthor Commented:
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#
SidFishesCommented:
"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.
rudodooAuthor Commented:
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
SidFishesCommented:
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.
SidFishesCommented:
err... set up a DSN for you
rudodooAuthor Commented:
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
SidFishesCommented:

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
rudodooAuthor Commented:
Unfortunatly it didn't work.  I still received the same error
dgrafxCommented:
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.
rudodooAuthor Commented:
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


dgrafxCommented:
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?

SidFishesCommented:
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.
rudodooAuthor Commented:
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>
dgrafxCommented:
"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 ???
rudodooAuthor Commented:
No I wasn't getting any errors  I received a blank page that read hello
dgrafxCommented:
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?


rudodooAuthor Commented:
yes it shows all the info from the database
rudodooAuthor Commented:
so i guess it dumps the query
dgrafxCommented:
ok - so now Use Exactly this:

<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
</cfquery>
Hello Again
<cfabort>

Will it show "Hello Again" without an error?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rudodooAuthor Commented:
I got it working I appreciate it you get the points
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.