Need Help creating this Table

[David Heller]

I have this schema I exported from a database running mysql version 4.01
I'm trying to create this table on a server running mysql 3.23
I keep getting a syntax error near line 11 (I added the line numbers for
clarity. I think it has a problem with the Table Field "ORDER#" I do
have it quoted as shown below so why do I keep getting syntax errors? I
also have problems with the Table Fields "BY" and "DESC" and they are
quoted the same way.
Any suggestions anyone ?

1 CREATE TABLE `tabPO` (
2 `PONUM` int(11) NOT NULL auto_increment,
3 `VENDOR` varchar(12) default NULL,
4 `STATUS` varchar(12) default NULL,
5 `TYPE` varchar(5) default NULL,
6 `PODATE` datetime default NULL,
7 `ORDERED` datetime default NULL,
8 `EXPECTED` datetime default NULL,
9 `SHIP` varchar(10) default NULL,
10 `CLOSEDATE` datetime default NULL,
11 `CHARGES` double default NULL,
12 `ORDER#` varchar(12) default NULL,
13 `NOTE` varchar(80) default NULL,
14 `SHFEE` double default NULL,
15 `PINVOICE` varchar(16) default NULL,
16 `SEPARATE` int(11) default NULL,
17 `LOCATION` varchar(5) default NULL,
18 KEY `PONUM` (`PONUM`)
19 ) TYPE=MyISAM;
Thanks,

Dave

ps I can't use anything but what I got. I'm trying to convert a JET3 MS
Access Database Backend to Mysql. I have no VBA experience to reprogram
the Access FrontEnd.

[Bill Karwin]

http://dev.mysql.com/doc/mysql/en/Legal_names.html

This doc says:
"Identifier quoting was introduced in MySQL 3.23.6 to allow use of
identifiers that are reserved words or that contain special characters.
Before 3.23.6, you cannot use identifiers that require quotes, so the
rules for legal identifiers are more restrictive..."

So I would assume that the # character is not allowed in a field name in
the earlier version of MySQL. I'm surprised the back-tick characters
are even parsed.

I think you would fix this problem by renaming the field "ORDER#" to
"ORDERNUM", for instance. This might require changing some of the VBA
application, but probably only places where it references that field by
name.

If you really can't touch the VBA code, you'll have to upgrade your
MySQL software to a version that supports special characters in quoted
identifiers.

Regards,
Bill K.

David Heller wrote:

I have this schema I exported from a database running mysql version 4.01
I'm trying to create this table on a server running mysql 3.23
I keep getting a syntax error near line 11 (I added the line numbers for
clarity. I think it has a problem with the Table Field "ORDER#" I do
have it quoted as shown below so why do I keep getting syntax errors? I
also have problems with the Table Fields "BY" and "DESC" and they are
quoted the same way.
Any suggestions anyone ?

1 CREATE TABLE `tabPO` (
2 `PONUM` int(11) NOT NULL auto_increment,
3 `VENDOR` varchar(12) default NULL,
4 `STATUS` varchar(12) default NULL,
5 `TYPE` varchar(5) default NULL,
6 `PODATE` datetime default NULL,
7 `ORDERED` datetime default NULL,
8 `EXPECTED` datetime default NULL,
9 `SHIP` varchar(10) default NULL,
10 `CLOSEDATE` datetime default NULL,
11 `CHARGES` double default NULL,
12 `ORDER#` varchar(12) default NULL,
13 `NOTE` varchar(80) default NULL,
14 `SHFEE` double default NULL,
15 `PINVOICE` varchar(16) default NULL,
16 `SEPARATE` int(11) default NULL,
17 `LOCATION` varchar(5) default NULL,
18 KEY `PONUM` (`PONUM`)
19 ) TYPE=MyISAM;
Thanks,

Dave

ps I can't use anything but what I got. I'm trying to convert a JET3 MS
Access Database Backend to Mysql. I have no VBA experience to reprogram
the Access FrontEnd.

[Mickey]

Bill

Thanks, I had a feeling that it might have had something to do with the
mysql server version. I believe the version we use at work is 3.23.58

Dave

Bill Karwin wrote:

http://dev.mysql.com/doc/mysql/en/Legal_names.html

This doc says:
"Identifier quoting was introduced in MySQL 3.23.6 to allow use of
identifiers that are reserved words or that contain special characters.
Before 3.23.6, you cannot use identifiers that require quotes, so the
rules for legal identifiers are more restrictive..."

So I would assume that the # character is not allowed in a field name in
the earlier version of MySQL. I'm surprised the back-tick characters
are even parsed.

I think you would fix this problem by renaming the field "ORDER#" to
"ORDERNUM", for instance. This might require changing some of the VBA
application, but probably only places where it references that field by
name.

If you really can't touch the VBA code, you'll have to upgrade your
MySQL software to a version that supports special characters in quoted
identifiers.

Regards,
Bill K.

David Heller wrote:

I have this schema I exported from a database running mysql version 4.01
I'm trying to create this table on a server running mysql 3.23
I keep getting a syntax error near line 11 (I added the line numbers
for clarity. I think it has a problem with the Table Field "ORDER#" I
do have it quoted as shown below so why do I keep getting syntax
errors? I also have problems with the Table Fields "BY" and "DESC" and
they are quoted the same way.
Any suggestions anyone ?

1 CREATE TABLE `tabPO` (
2 `PONUM` int(11) NOT NULL auto_increment,
3 `VENDOR` varchar(12) default NULL,
4 `STATUS` varchar(12) default NULL,
5 `TYPE` varchar(5) default NULL,
6 `PODATE` datetime default NULL,
7 `ORDERED` datetime default NULL,
8 `EXPECTED` datetime default NULL,
9 `SHIP` varchar(10) default NULL,
10 `CLOSEDATE` datetime default NULL,
11 `CHARGES` double default NULL,
12 `ORDER#` varchar(12) default NULL,
13 `NOTE` varchar(80) default NULL,
14 `SHFEE` double default NULL,
15 `PINVOICE` varchar(16) default NULL,
16 `SEPARATE` int(11) default NULL,
17 `LOCATION` varchar(5) default NULL,
18 KEY `PONUM` (`PONUM`)
19 ) TYPE=MyISAM;
Thanks,

Dave

ps I can't use anything but what I got. I'm trying to convert a JET3
MS Access Database Backend to Mysql. I have no VBA experience to
reprogram the Access FrontEnd.