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.


"Zephyl" <(E-Mail Removed)> wrote in message
news:24f9a01c46073$8eaa5690$(E-Mail Removed)...
> 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.

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.

>-----Original Message-----
>go to query and make New query. choose query/append from
menu and you should
>be able to do it yourslf.
>
>
>"Zephyl" <(E-Mail Removed)> wrote in
message
>news:24f9a01c46073$8eaa5690$(E-Mail Removed)...
>> 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.
>
>
>.
>

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.

--
Steve Schapel, Microsoft Access MVP


(E-Mail Removed) wrote:
> 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.
>

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
>-----Original Message-----
>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.
>
>--
>Steve Schapel, Microsoft Access MVP
>
>
>(E-Mail Removed) wrote:
>> 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.
>>
>.
>

Jason,

Normally, the []s would not be required.

--
Steve Schapel, Microsoft Access MVP

Jason wrote:
> 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
>