Front End: Access 2010; Back End SQL Server 2008
The following produces this error messages and I do not know why: Runtime error '3134' Syntax error in INSERT INTO statement. - zSQL2 = _
-
"INSERT INTO [dbo_Notes] " & _
-
"(partshortages_ID, date, [by], [note])" & _
-
" VALUES(" & _
-
" " & gblDBResult2 & _
-
", '" & RDateAdded & "'" & _
-
", '" & Rbuyer & "'" & _
-
", '" & Me.Notes.Value & "')"
-
Debug.Print zSQL2
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL zSQL2
The database definitions are:
partshortages_ID int
Date varchar(12)
By varchar(50)
note text
Thank you.
10 1142
When you Debug.Print, what is the result that you get? That will help us greatly!
BTW, please use Code tags when you post Code!
This what I get with a debug.print:
INSERT INTO [dbo_Notes] (partshortages_ID, date, [by], [note]) VALUES( 16, '6/25/2014', 'SClark', 'sdbsdfgsdfgsdfgsdfgfdsg')
sc5502,
I am assuming [dboNotes] is the table? If so, this should not be in brackets.
Your fields: (partshortages_ID, date, [by], [note]) also should not be in parentheses.
Your date "may" require "#" before and after it.
So, this might help: - zSQL2 = _
-
"INSERT INTO dbo_Notes " & _
-
"partshortages_ID, date, [by], [note]" & _
-
" VALUES (" & _
-
" " & gblDBResult2 & _
-
", '" & RDateAdded & "'" & _
-
", '" & Rbuyer & "'" & _
-
", '" & Me.Notes & "')"
-
Debug.Print zSQL2
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL zSQL2
I've tried it without the "#", but if this still causes an error, try this for line 6: - ", #" & RDateAdded & "#" & _
Hope this hepps!
I've tried your the suggestions and still get the error.
Actually, the parentheses around the field list is required.
After you print out the SQL, put it in a query and see if it will run.
Here what I get
INSERT INTO dbo_Notes (partshortages_ID, date, by , note ) VALUES( 17, '6/25/2014', 'SClark', 'fhdfghdfghdfghdfghdfghdfghdfgh')
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'by'.
@Rabbit,
Yes, thank you--my mistake! @sc5502,
"By" is a reserved word. Try putting it back into brackets.
date is also probably a key word, put that in brackets as well.
I guess this is a good demonstration showing the importance of choosing good field names and avoiding reserved/key words!
I admit, I learned that the hard way.... :-/
Also, reviewing some of my sparse code that uses the INSERT INTO, you will have to enclose your date with "#": - ", #" & RDateAdded & "#" & _
NeoPa 32,556
Expert Mod 16PB
As your [Date] field is designed as varchar(12) you actually won't want the # chars. Your SQL quotes there are already correct. Otherwise, the advice given so far, where not already corrected, should get you to where you want to be.
Good luck :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ann D. |
last post by:
Hi!
I'm creating a website that has a few pages... question is, how can I
prevent a user from going directly to a page.
i.e. user has to go through myPage1.asp (page takes some user info)
to...
|
by: Daniel |
last post by:
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data...
|
by: SJ Mo2 |
last post by:
I have several Access databases that I want to publish (or make
available) through our web-pages. The problem...our web server is on
a Linux-Apache machine while our Access db is obviously on a...
|
by: matt bourke |
last post by:
Hi,
Can you please tell me what the advantage is of using an access backend with
a VB frontend OVER access as a frontend and backend.
M
|
by: Michael J. Savage |
last post by:
Hi All,
I posted yesterday re appending a table to another but it seems I was
asking the wrong question.
What I'm trying to do is open a client information table and, with a
click of a...
|
by: Andre Laplume via AccessMonster.com |
last post by:
I have inherited a bunch of dbs which are are shared among a small group in
my dept. We typically use the dbs to write queries to extract data, usually
dumping it into Excel. Most dbs originated...
|
by: Daisy |
last post by:
If I connect to an MDB file in my C# app, would something other than the
..NET runtime require installing on the target clients?
--
Daisy The Cow
Now playing: Pascal ft Karen Parry - I Think...
|
by: Jason Hurder |
last post by:
Hello folks,
I am experiencing a rather strange problem. I have written a service
that attempts to open a file on a network share via a FileStream object.
When I try to open the file from my...
|
by: jason |
last post by:
saw another thread on this topic, but no solutions were presented, so
here is my question:
i have a c# class library dll that must access a config-like text file.
when using the FileStream...
|
by: wildman |
last post by:
It's been over 10 years that I don't look at Access. I've been working
in asp.net for the last 5 developing intranet sites.
Just got a project where we are trying to avoid building an...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |