Page 1 of 1

Access (yeah, Access) data types

Posted: Sat Mar 22, 2003 12:17 pm
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

Posted: Sat Mar 22, 2003 12:52 pm
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

Posted: Sat Mar 22, 2003 1:16 pm
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

Posted: Sat Mar 22, 2003 1:35 pm
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