Access (yeah, Access) data types

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Skorp
Forum Newbie
Posts: 3
Joined: Sat Mar 22, 2003 12:17 pm

Access (yeah, Access) data types

Post by Skorp »

I'm writing a script for a site that is hosted on a Windows NT platform. Although they have the good taste to run PHP, they do not follow through with MySQL and only offer Access DB. That's OK I suppose - I only need a lightweight DB and Access is ODBC compliant.

Anyway, I'm having some trouble with Access as I've never used it before. I'm trying to create a table through a SQL query and am running into some difficulties in creating a field with the "AUTONUMBER" Access datatype.

$query="CREATE TABLE users (id AUTONUMBER,name TEXT,hash TEXT)";

when executed gives me

Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition., SQL state 37000 in SQLExecDirect

I can make any other datatype - TEXT, MEMO, NUMBER, YES/NO - but the AUTONUMBER throws an error. Ideally I'd like to make the ID the primary key, but at this point I'll settle for just getting an auto_increment!

Please don't tell me to RTFM...I don't have the docs, and I'm not about to buy a book on Access for one lousy script.

Thanks in advance!
Tony
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Have you tried AUTOINCREMENT instead of AUTONUMBER?

You may want to have a search of the MSDN Library (if you have a lot of patience):
http://msdn.microsoft.com/library/default.asp

Mac
Skorp
Forum Newbie
Posts: 3
Joined: Sat Mar 22, 2003 12:17 pm

Post by Skorp »

Yup. I tried Autoincrement, auto_increment, auto increment, auto_number, auto number...can you tell I was getting desperate? :)

However, I just figured it out, after yet more Google trawling. It turns out that MS changed the name of the datatype between different versions of Access. "AUTONUMBER" (which is simply a INT AUTO_INCREMENT datatype) is now called "COUNTER". :? This new name is in the same vein of calling a BIT datatype as a YES/NO datatype in Access. "Cutesy" names so as not to confuse the unwashed masses I suppose.

You'd think that MS would make things backwards compatible, but no.

(I actually searched Google before this post, but since I was searching for "autonumber", naturally old hits came up and enlightened me not. I just stumbled across the correct answer more by accident that clever search statement).

*sigh*

Give me MySQL
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Skorp wrote:You'd think that MS would make things backwards compatible, but no.
That kind of thing is so frustrating, I tried Googling the answer too but came up with nothing. Glad you were able to sort it.

Mac
Post Reply