Webmaster Forum / ColdFusion / Getting Started / January 2007

pheeeeeeew that one's a minefield man. Access... where do you start? I'm sure
they've changed it since I started in this game but it used to have major
issues with having more than 5 users accessing it concurrently. I believe it
still suffers from the problem of opening the database directly while your CF
app tries to use it - Access locks the database file so only that user can
view/edit/whatever at that point in time.

used to have an issue where updating the access file in your development
environment, if you then went and re-uploaded it to the live server, you had to
recreate the ODBC connection. (probably not such an issue now as I think CF's
inbuilt dsn management stuff makes that easier (someone correct me if i'm wrong
there!))

Ideally, for a database you're learning with, look into something like MySQL -
it's free, easy to install, the DB tools are relatively straightforward and
there's some nice free tools for it. Heaps of docos at www.mysql.org . Be aware
though, that whichever database you use, they all have their own extra little
commands within their SQL implementations - so if you switch databases mid
project for whatever reason this may be something you need to be aware of.

But as to the process of creating a database - most DB tools nowadays are
roughly the same:
1) filemenu (or somewhere like that) there should be a Create Database option
- click it and follow the prompts, naming your database etc NOTE: WHATEVER
DATABASE YOU USE, SEARCH GOOGLE FOR A LIST OF RESTRICTED WORDS THAT MAY NOT BE
USED IN DATABASE/TABLE/COLUMN/VIEW/TRIGGER/etc naming - also no spaces in
names, only punctuation you should use is underscore.

2) if you were not prompted to create a user account for the database - do so
now, usually there will be a USERS option somewhere near where the new database
shows in your interface (assuming you arent doing this over command line).
create a new user - name it whatever (remember point about naming above) - give
it a password - remember these details, you'll need them for the CF
Administrator's datasource setup. assign the user to the new database (usually
in the user creation process but sometimes you're required to go back to the
database itself and find a users section in it's options) - assign the user
whatever permissions you need - read, write, delete, update, whatever.

3) open the database up and add a new table to it, name it whatever -you
should be taken to the point where you create columns at this stage - give your
table an ID column - numeric/int - autoincrement - and set it to primary key...
from here on  create whatever columns you need, reference your chosen
database's docos for descriptions of the column datatypes and assess them to
see which ones meet your needs.

note, some of the above bits may be arse-about depending on your DB/toolset.

Cheers

ergh that'll learn me to read your ENTIRE POST before replying...

you can create in access yes. get a book from the o'reilly family if someone
else doesnt suggest something better. Uploading the database will depend on
your webhost - as the ODBC connection will likely need to be recreated each
time you do this.

Please see this TechNote: http://www.adobe.com/go/tn_17034

If you are THAT new to creating databases, I would like to suggest that you
avoid Access altogether. Start with MySQL or SQL Server. The reason I say this
is because once you are up and running with Access, you will no doubt want to
move to a more capable database anyway. Not only that, but some of the stuff
you learn for Access will contradict or at least confuse you with regards to
SQL databases.

Access is a desktop database, and not intended for multiple-use situations,
even though you can "get by" with it in some cases.

It is just not a good idea.

If your sql book does not say much about data modelling, or how to design a
database, you really want more reference material.  If you google "data
modelling" or "database normalization", you should find an online tutorial.

Hmmmm. I hate to intrude with a reality check but when you use Access in a web
environment you only have ONE connection, not muliple like when it is being
used as an application itself.

Access is a nice way for newbies and small websites to have a database that is
easy to create and manage.  It does NOT require redoing the ODBC everytime you
reload it. (where does this stuff come from?)

The nice thing is that a person can create the whole thing, test it, and then
upload it right on their local computer without going thru cumbersome
interfaces.

That being said, if you are looking for power etc then go with MS SQL.  I
highly recommend against MySQL. It has some flaws and lacks some features that
will drive you nuts.  Of course MSSQL will probably cost you some  extra on
your host, but yo could use godaddy where it won't.

There is more than one connection to an Access database if you have someone opening the database in Access at the same time a web user is accessing the database through CF.

There are people dedicating their careers to database creation and
administration. "Teach yourself SQL in 10 minutes" is not going to teach you
everything you need to know.

It is entirely possible to set up a lousy, poorly performing database that
will work with ColdFusion in a matter of minutes. It is quite another project
if you want it to be fast, reliable, and easily maintainable.

You will get out of it what  you put into it. How good do you want it to be?

What I've run into is things like embedded queries don't work, tho somebody
told me that was fixed in the newest version.  Just doesn't meet ANSI
standards.  Time after time simple queries that are doable in even Access won't
work in MySQL.    Of course from what I understand now v5 has fixed a lot of
the problems and even has some limited stored procedure ability but that still
has a ways to go too it seems.

Anyway, when you say that you have to recreate the ODBC connection, what do
you mean? You mean like in ASP, .NET etc etc where you have to open a
connection to the db everytime you want to use it but in CF you don't have to
do that?

I've used CF since v4 and many people were using Access for dbs because it was
the easiest thing to use at the time and never had any trouble with the ODBC
needing to be redone everytime the db was reloaded.  I can't even see any
reason for it unless you changed the name or path.  I think you had a bug or
something for that to happen.

I think I vaguely remember having to recreate the ODBC connection if you did
something like the following:

Say you have an Access database in use with a hosting provider.

You have a local copy of the database that you make some changes to.

You now want to replace the host copy with your local copy.

If you DELETE the host copy, and then UPLOAD your local copy, you THEN need to
recreate the datasource.

This is going back many years at this point. Does that ring a bell with
anyone???

I made the mistake of NOT getting a solid foundation in database design before
developing a lot of my sites. I have regretted it many times over.

At this point I grab every opportunity to build on my database design skills.

And now, thanks to Dinghus, I just bought the book he mentions above, for 8.50
on Amazon (used).

Even if I look throught it once a month it has to be worth $8.50!!!

Actually let me back up. If you DELETE your database you remove the ODBC
connection also.  If you just overwrite the db then you don't.  But then there
is normally no need or reason to DELETE a db before uploading the new version.  
Especially if it is a live db.  You could lose data that way.

Of course I have seen dbs that were so messed up that deleting them was the
kindest thing I could do for them.  Put them out of their misery.