[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating an ODBC connection to an access database

Posted on 2006-03-26
32
Medium Priority
?
5,703 Views
Last Modified: 2013-12-24
How would I create an ODBC connection to an Access database using coldfusion?

0
Comment
Question by:rudodoo
  • 18
  • 7
  • 5
  • +2
32 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 16297945
     
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
0
 

Author Comment

by:rudodoo
ID: 16297954
what if the database is on a remote server
0
 

Author Comment

by:rudodoo
ID: 16297992
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.
 
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 10

Expert Comment

by:Mr_Nil
ID: 16298553
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.
0
 

Author Comment

by:rudodoo
ID: 16298624
Thank you
0
 
LVL 25

Expert Comment

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

Datasource OK
0
 

Author Comment

by:rudodoo
ID: 16300812
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>
0
 

Author Comment

by:rudodoo
ID: 16300825
Don't Forgot to change the <cfdump var="#Error#"> to <cfdump var="Error">
0
 

Author Comment

by:rudodoo
ID: 16300870
But on the other hand if you leave the ## variable tags in their I get an error saying that it is undefined
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16301017
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.
0
 

Author Comment

by:rudodoo
ID: 16301109
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>
0
 

Author Comment

by:rudodoo
ID: 16301298
Heck it looks like you might have figured something out, I have been getting unable to connect these past few days
0
 

Author Comment

by:rudodoo
ID: 16301481
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#
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 16301565
"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.
0
 

Author Comment

by:rudodoo
ID: 16301755
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
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 16301961
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.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 16301971
err... set up a DSN for you
0
 

Author Comment

by:rudodoo
ID: 16305405
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
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 16305648

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
0
 

Author Comment

by:rudodoo
ID: 16306510
Unfortunatly it didn't work.  I still received the same error
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16306729
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.
0
 

Author Comment

by:rudodoo
ID: 16307378
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


0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16307414
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?

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 16307495
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.
0
 

Author Comment

by:rudodoo
ID: 16307503
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>
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16307672
"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 ???
0
 

Author Comment

by:rudodoo
ID: 16307770
No I wasn't getting any errors  I received a blank page that read hello
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16309535
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?


0
 

Author Comment

by:rudodoo
ID: 16314084
yes it shows all the info from the database
0
 

Author Comment

by:rudodoo
ID: 16314489
so i guess it dumps the query
0
 
LVL 25

Accepted Solution

by:
dgrafx earned 1500 total points
ID: 16314870
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?
0
 

Author Comment

by:rudodoo
ID: 16317170
I got it working I appreciate it you get the points
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question