We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Creating an ODBC connection to an access database

Medium Priority
5,726 Views
Last Modified: 2013-12-24
How would I create an ODBC connection to an Access database using coldfusion?

Comment
Watch Question

CERTIFIED EXPERT

Commented:
     
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

Author

Commented:
what if the database is on a remote server

Author

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.
 

Commented:
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.

Author

Commented:
Thank you
CERTIFIED EXPERT

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

Datasource OK

Author

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>

Author

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

Author

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

Commented:
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.

Author

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>

Author

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

Author

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#
CERTIFIED EXPERT

Commented:
"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.

Author

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
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
err... set up a DSN for you

Author

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
CERTIFIED EXPERT

Commented:

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

Author

Commented:
Unfortunatly it didn't work.  I still received the same error
CERTIFIED EXPERT

Commented:
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.

Author

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


CERTIFIED EXPERT

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

CERTIFIED EXPERT

Commented:
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.

Author

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>
CERTIFIED EXPERT

Commented:
"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 ???

Author

Commented:
No I wasn't getting any errors  I received a blank page that read hello
CERTIFIED EXPERT

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


Author

Commented:
yes it shows all the info from the database

Author

Commented:
so i guess it dumps the query
CERTIFIED EXPERT
Commented:
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?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I got it working I appreciate it you get the points
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.