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
18
Replies
AdChoices