ColdFusion with Access query question

I have an Access database with ColdFusion to display data over the web.

We are currently creating a separate table for the web, from multiple tables with relationships and updating it daily.

I would like to create an Access query that has all the web data.

Can ColdFusion connect to a query in Access instead of a table?

Can I avoid creating relationships in ColdFusion by creating a query in Access?
DJPr0Asked:
Who is Participating?
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.

dgrafxCommented:
Access is a database and when you save data in Access you are saving it in a table.
So when you run a query to pull data - whether Access or Sql Server or Oracle etc - you are pulling this data from a table.

Could you describe what you are actually trying to do.

You say you are creating one table just for the website - generating it from several other local tables - is that what you are doing?
And you want this process to be easier.

Are you hosting the website or is this a professional host?
0
DJPr0Author Commented:
Could you describe what you are actually trying to do.
You say you are creating one table just for the website - generating it from several other local tables - is that what you are doing?
Yes
And you want this process to be easier.
Yes

Are you hosting the website or is this a professional host?
We are hosting.

In lieu of creating a separate table for the website using update/append queries - have the website connect to the query itself - the website does accept input on some fields - can we input data in a query which is routed to the table? Input to an Access form via ColdFusion?
0
dgrafxCommented:
just use the same database / tables for the website - no need to export to a different table then use the different table - do you see what I mean?

Just modify your query that the website uses to use the original table(s).

Even though it isn't ideal using Access as a web database you can still do it.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DJPr0Author Commented:
The Idea was instead of using the tables and creating relationships in ColdFusion - use the query from the tables so I don't have to create all the relationships in CF.

Is this a good idea?
0
dgrafxCommented:
I don't know what you mean ???

Define this: "instead of using the tables and creating relationships in ColdFusion".

Define this: "use the query from the tables so I don't have to create all the relationships in CF".

Are you by chance referring to a View or Report or ??? that has been created in Access?



With all due respect but to help me understand: Are you really new at SQL?
0
DJPr0Author Commented:
OK I will try and clarify.

I have tables in an Access database with relationships:
(Simplified scenario)
Tables:
Makes (Ford, Chevy, Etc)
Models (Mustang, Impala, Etc)

I have a datasheet type form in my Access database that displays both tables via the relationships.

If I want ColdFusion to display what my datasheet form displays in Access I need to create these relationships in ColdFusion.

My question is, instead of creating relationships in ColdFusion to display something similar to my Access datasheet form – just have ColdFusion connect to my Access datasheet form and no need for creating relationships in ColdFusion.

If Coldfusion connects to the form I don’t need to create relationships in CF due to Access is putting the data tables (Make & Models) together.

The reason I don’t want to create relationships in ColdFusion is we are still developing the Access database and making adjustments periodically – rather than keep changing the ColdFusion code we can just change the Access form that ColdFusion connects to.
0
dgrafxCommented:
OK - in answer to is this a good idea - no it isn't ...

First off you should forget Access and move to Sql Server - I imagine cost comes into play though. That's why people use shared hosting - so you pay a small monthly fee and the host provides the expensive software - you should look into it.

But if you stay with Access - forget trying to display this datasheet - It's a faulty idea.
I still don't get this "creating relationships with CF" idea - but let's continue.
Just query the tables - it's as simple as that.
You don't need to build relationships to query.
You just query.

If you need help with the query just provide relevant details and myself or others will help you write it.
Just provide your table structure and what you want to query for.
0

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
DJPr0Author Commented:
OK, I guess I'm trying to avoid creating complicated queries in ColdFusion. Our database has 30 tables and some queries can be difficult to create.

Can I use the free version of MS SQL?
0
dgrafxCommented:
I believe you can.
Sql Server is much more powerful.
0
DJPr0Author Commented:
Thanks dgrafx.
0
dgrafxCommented:
good luck & thanks - glad I could help ...
0
_agx_Commented:
> just have ColdFusion connect to my Access datasheet form

I'm curious ... what exactly is a "datasheet form"? If it's some sort of graphical component like the old report/query builder features of old - the answer is "no".  You can't access the graphical stuff from CF.  Well .. not easily.  It would require Automation which is complicated. Not to mention it requires running Automation on the server (which even MS discourages because it's designed for desktops, not servers.).

Switching to SQL Server is a much better choice..
0
websauceCommented:
_agx_ said it best.  SQL server is a much better choice.

I will add that you can sometimes deal with it a few other ways

1. query of queries -- you can query a previous result set
ie (from CF docs )

<!--- Master Query --->
<cfquery datasource="cfdocexamples" name="master"
    cachedwithin=#CreateTimeSpan(0,1,0,0)#>
    SELECT * from Employee
</cfquery>

<!--- Detail Query (dbtype=query, no data source) --->
<cfquery dbtype="query" name="detail">
    SELECT Emp_ID, FirstName, LastName
    FROM master
    WHERE LastName=<cfqueryparam value="#LastNameSearch#"
cfsqltype="cf_sql_char" maxLength="20"></cfquery>

****
Not so many folks query the query results.
I find it useful sometimes.

Arrays can be another way to deal with it on the CF side.

Overall, getting a real DB backend and keeping the table schema tight, is your best bet.
Once I got used to SQL server, I never looked back.
0
websauceCommented:
As you move to SQL server (or any real relational db), you can keep most of the SQL logic on the DB side by using stored procedures, and then call them with CFC's or in CFM files.

It's a little different at first, but it lets you have the complicated queries in a DB environment, and easily call them from CF. You manage them on SQL server, and use them as needed.
0
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
ColdFusion Language

From novice to tech pro — start learning today.