Error in simple Insert Into Statement

[EddyLLC]

Can anyone see what's wrong with this code? It's a simple INSERT INTO statement yet I continue to get a "Syntax error in INSERT INTO statement". The Renewal table is the "many" of a one to many relationship but a similar record exists in the "one" table. If I open up the Renewal table I can manually add the owner with out error. Thanks.

Dim strOwner as String
strOwner = txtOwner
strSQLRenewal = "INSERT INTO Renewal(Comp) Values ('" & strOwner & "')"

 

[gmmastros]

Are you trying to add a value that contains an apostrophe?

try this:

Dim strOwner as String
strOwner = [!]Replace([/!]txtOwner[!], "'", "''")[/!]
strSQLRenewal = "INSERT INTO Renewal(Comp) Values ('" & strOwner & "')"

If this allows things to work, I would encourage you to do some reading about [google]SQL Injection[/google].


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Do you need a space after Renewal?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

Also, it would be nice to see what you get in the Immediate Window if you do:

strSQLRenewal = "INSERT INTO ...[blue]
Debug.Print strSQLRenewal[/blue]

Have fun.

---- Andy

 

[EddyLLC]

Thanks for the responses. gmmastros, I tried your suggestion without success. Below is what appeared in the Immediate Window both for what strSQLRenewal contained and the resulting error.

?strSQLRenewal
INSERT INTO Renewal(Comp) Values ('Johnson Group')

?Err.Description
Syntax error in INSERT INTO statement.

 

[gmmastros]

What type of database are you using? I ask because sometimes people will name tables and/or columns using reserved words. For example, imagine creating a table named insert. Your insert query would be:

Insert Into Insert(Column) Values('X')

To accommodate this problem, you can usually put special characters around table and column names to let the database engine know that you are referring to a table or column instead of part of the syntax. With Microsoft SQL Server, it would be:

Insert Into [!][[/!]Insert[!]][/!](Column) Values('x')


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

Do you have a table named: Renewal?
If so, do you have a field in this table called: Comp?
How is this field defined?
Are there any other fields in the Renewal table? Any of them required?

Have fun.

---- Andy

 

[EddyLLC]

I'm using an Access 2003 database. Yes I do have a table named Renewal and the first field, the key field, is Comp. No other fields in the table are required. The field is defined as a text field.

I cant help thinking I'm missing something really simple (read "stupid").

Thanks again.

 

[Andrzejek]

What will happen if you would take your statement:[tt]
INSERT INTO Renewal(Comp) Values ('Johnson Group')[/tt]
and use it straight in Access (not in VBA) in a query builder, editor, or whatever?

Would get the same error?

BTW - How do you use later your strSQLRenewal?

Have fun.

---- Andy

 

[Bluejay07]

Also, is there a max column width value for Comp?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[EddyLLC]

I created an Append Query in Access and successfully appended the owners name from the related table into the Renewal table without error. I then took the SQL from access modified and dropped it into the VB project. The result was the same, Syntax Error. The SQL is below:

strSQLRenewal = "INSERT INTO Renewal(Comp)SELECT Owners.Owner " _
& "From Owners WHERE Owners.Owner = 'Johnson Group'"

Regarding my use of strSQLRenewal, if I understood you question, it is simply a local (local to this private sub) variable used to hold the SQL. The next line of code after the insert statement is;

Conn.Execute strSQLRenewal

then I close the connection.

 

[EddyLLC]

Hi Bluejay07, I just noticed your question. If you mean a field size setting for Comp it is 75.

 

[dilettante]

[tt]COMP[/tt] is a reserved word in Jet SQL, both in the old (DAO) and new (OLEDB) forms.

To avoid frustrations like this I suggest you just get in the habit of escaping all names with brackets:

INSERT INTO [Renewal]([Comp]) VALUES ('Johnson Group')

 

[dilettante]

If you have a version of Office (or Access) 2000 or later you should also have JETSQL40.CHM some where on your system.

I have Office 2003 on this PC, so mine is at:

[tt]"C:\Program Files\Common Files\microsoft shared\OFFICE11\1033\JETSQL40.CHM"[/tt]

This is a useful document to make a shortcut to and put on your desktop, in your Start Menu somewhere, etc.

One of the things it has is a list of SQL reserved words. It also has a ton of other useful info, though it isn't a complete reference since it is biased toward Access (which uses the old DAO SQL).

There is also a ton of info in the MSDN Library CDs that come with VB6. You'll want the October 2001 CDs since they were the most up to date that still have VB6 info in them. There are many useful items in the Technical Articles section under Microsoft Access 2000 and most of them remain relevant today. Many articles describe differences between Jet 4.0 DAO SQL and OLEDB SQL.

Though Access supports SQL statements, Access SQL doesn't completely adhere to ANSI SQL standards. In Access 97, Access SQL most closely resembles the SQL-89 standard. However, with the release of Jet 4.0 in Access 2000, Access SQL was updated to more closely emulate the SQL-92 standard through syntax exposed by ADO and the Microsoft Jet OLE DB Provider.

This is one reason why playing around in Access doesn't always result in queries that will work in VB6. Even today Access sticks to a more ancient SQL syntax.

Don't confuse Access with Jet. Access is just one funky client of Jet.

 

[EddyLLC]

You nailed it dilettante. I tried your brackets suggestion and it worked. I've also set up the short cut as suggested.

Thanks so much for everybody's time.