INSERT INTO syntax error in MS Access

qball · May 21st, 2002, 11:05 PM

Not a big fan of access, anyway...

Have the following table, named table1

col1 named string1 text datatype
col2 named integer1 number (long integer) datatype

Try the following SQL statement:

INSERT INTO table1 (string1, integer1) VALUES ("access sucks", 45);

Get "syntax error in INSERT INTO statement" when I attempt to run in Access (query design mode) and through java client, so I doubt the jdbcodbc driver issue.

Any ideas, or does Access just blow?

^hyd^ · May 21st, 2002, 11:50 PM

hhmm, well, you probably tried it, but first thing to mind is to try using single quotes instead of double quotes... next I'd check to see if the string being entered isn't too long for the field in the db. ...surely you have double-checked the spelling of field names??

anyways, g'luck... I'll try to check back later and see how it goes...

^hyd^ · May 22nd, 2002, 01:53 AM

OMG!!!

ok, re-read your post, and recreated exact same, threw your query in via sql-view in the query editor. didn't work... "syntax error in Insert..." blah blah... and it highlighted 'integer1'.

so, changed everything to 'finteger1' and no prob!! back to 'integer1', error... then tried '_integer1', worked fine again... it appears that you can't name a field 'integer1'... go figure...

anyways, g'nite...

qball · May 22nd, 2002, 02:19 AM

omg back at you? lol

good try. and helpful,thanks but,

[bigbut]
I'm, basically an access idiot, and can get to Java error.
tired and will try "better", later
[/bigbut]

thanks. mo later...

qball · May 22nd, 2002, 06:30 PM

^hyd^,

It was the name of the fields, that caused the problem, though the original names fit the naming conventions, from access help file:

Quote:

Can be up to 64 characters long.

Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]).

Can't begin with leading spaces.

Can't include control characters (ASCII values 0 through 31).
...

When you name a field, control, or object, it's a good idea to make sure the name doesn't duplicate the name of a property or other element used by Microsoft Access; otherwise, your database can produce unexpected behavior in some circumstances.

A good example of why Access IS NOT a real DB.

vass0922 · May 22nd, 2002, 09:53 PM

lol qball, I agree.. I hate Access

Good call hyd, I never would of that of that

Rhino · May 22nd, 2002, 10:47 PM

Dude, you mean Qball actually needs help sometimes? I thought he knew it all. LOL

Anyways, I would try changing the query type in Acess to an update query if you haven't changed it. It will be on Select query by default, although you think it would be smart enough to read the SQL and change the Query.

qball · May 23rd, 2002, 01:08 AM

Quote:

Dude, you mean Qball actually needs help sometimes?...

qball, needs help, all the time! (email money),lol.

btw, am I, both the subject and participle of that sentence, nevermind.

Anyway, the INSERT INTO syntax using reserved words STARTING column names in Access, is my bad, thanks already given.

What I was doing was a simple benchmark test. Something I've done before, fairly easily...

Ok, connect to DB.
Insert 100000 rows of a variety of datatypes. (hundred thou, 100k, iffin don't wanna count zero)
Report results.

Access CAN do 10000 in 38 secs, pathetic...

That's 10 thou.

Rhino · May 23rd, 2002, 01:37 AM

What pc setup are you using? That could have something to do with it. I may give it a try.

vass0922 · May 23rd, 2002, 05:56 AM

Can you give us the results against maybe MySQL and SQL Server? Maybe even Oracle?

Well in Access's (coughcough bleh chokechoke) defense, its not meant to be a scalable database, just something for fairly small databases with not to many users

That also depends on what kind of indexes you have on the tables, too many and it'll slow things down