ColdFusion with Access query question

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?

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?

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.

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?

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?

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.

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?

I believe you can.
Sql Server is much more powerful.

Thanks dgrafx.

good luck & thanks - glad I could help ...

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

_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.

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.