DAO SQL INSERT INTO - Syntax?

  • Thread starter Thread starter Zephyl
  • Start date Start date
Z

Zephyl

I have defined a small table Mfrs as follows:

MfrID - Autonumber
SupplierID - Number
MfrName - Text
MfrURL - Hyperlink
MfrSupplierID - Text
Active - Yes/No

I would like to add a new record to the database via
SQL / INSERT INTO. What is the proper syntax? MSAccess
is giving me an error: "Run-time error '3134': Syntax
error in INSERT INTO statement.". What should the INSERT
INTO statement look like?

Thanks for your help.
 
go to query and make New query. choose query/append from menu and you should
be able to do it yourslf.
 
I presume the error is related to what I don't know about
the format of the VALUES () clause in the statement or
other assumptions I've made. How would I get an answer
from the Query Builder? Several questions come to mind
that may be important. How should I specify YES/NO
fields or URLs in the VALUES? When I retrieve a URL from
the database it has a format
like: "#http://aaa.bbb.ccc/index.htm#" when are "#"s
needed. Some people use 'yes' and 'no' others '1'
and '0', etc. for comparison purposes the Query Builder
suggests true or false with no quotes. How can I find
the correct answer using the Query Builder? I see INSERT
INTO statements with an ending ";" and others without.
Is it required? If I don't give a field list, is MfrID
assumed to be in the list? What is the impact? I've
tried several combinations but still get the same error.
For example:

sInsertMfrs = "INSERT INTO Mfrs
(SupplierID,MfrName,MfrURL,MfrSupplierID,Active) VALUES
{33,'3M CO','http://aaa.bbb.ccc/index.htm','AAA',true);"

goCurrentDB.Execute(sInsertMfrs)

generates an error.
 
Zephyl,

The only problem I can see off-hand with your code is the { rather than
( at the beginning of the VALUES clause, and I assume this is a typo in
your newsgroup post. It is normal to put spaces between the items in
the lists, but I don't think this will cause an error... what I mean is
like this...
sInsertMfrs = "INSERT INTO Mfrs (SupplierID, MfrName, MfrURL,
MfrSupplierID, Active) VALUES (33, '3M CO',
'http://aaa.bbb.ccc/index.htm', 'AAA', True);"

To answer a few of your specific questions...
- No, you do not have to include the autonumber field in the query.
- No, the ; at the end of the SQL is not necessary.
- True, Yes, On, -1, False, No, Off, 0 are all valid values for
appending to a Yes/No data type field.
- As far as I know, the ''s around the string for appending to the
Hyperlink field is correct, and I have never seen ##s associated as
delimiters with hyperlinks.

By way of trouble-shooting, I would try removing each field one at a
time from the SQL code, from both clauses, to try and identify which
part of it is causing the problem.
 
I had this same problem. I put [] around each field name
and now my code works.

For example:
sInsertMfrs = "INSERT INTO Mfrs ([SupplierID], [MfrName],
[MfrURL], [MfrSupplierID], [Active]) VALUES (33, '3M
CO', 'http://aaa.bbb.ccc/index.htm', 'AAA', True);"

I do not know if that will help, but it worked for me. I
did not think that I needed the [] since my field names
had no spaces, but I guess I did.

Jason
 
Back
Top