Jet ADODB SQL interface "INSERT INTO"

[Guest]

I am TRYING to insert into a table os a database that I have successfully
opened.

I use a ADODB.Connection object and the Execute method.

The table has only two columns: an autonumber item and a string item.

I continually get a SQL INSERT format error. I have tried many small
variations of the SQL command and I am convinced I am missing something vital.

The code segment id:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<yada, yada>
cnn.Execute "INSERT INTO Category values (,'whatever')", , adExecuteNoRecords

I get a INSERT command format error. What am I doing wrong?
PS I have also tried using a ADODB.command object with the same resulting
error, so it must be the SQL string, but why?

 

[Dirk Goldgar]

I am TRYING to insert into a table os a database that I have
successfully opened.

I use a ADODB.Connection object and the Execute method.

The table has only two columns: an autonumber item and a string item.

I continually get a SQL INSERT format error. I have tried many small
variations of the SQL command and I am convinced I am missing
something vital.

The code segment id:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<yada, yada>
cnn.Execute "INSERT INTO Category values (,'whatever')", ,
adExecuteNoRecords

I get a INSERT command format error. What am I doing wrong?
PS I have also tried using a ADODB.command object with the same
resulting error, so it must be the SQL string, but why?

If that's your exact SQL, then you have an unwanted leading comma in
your value list. Since you don't specify the field list, I don't know
whether that comma is there as a placeholder for a field, but you do
have to provide a value for every field you list -- or, in the absence
of a field list, for every field in the table.

 

[Brendan Reynolds]

In addition to Dirk's good advice, here's an example ...

"INSERT INTO Category (CategoryName) VALUES ('Whatever')"

.... where 'CategoryName' is the name of your text field.

If you were updating multiple fields, you'd include them, separated by
commas, between the first set of parentheses, and the values, separated by
commas and in the same order, between the second set of parentheses ...

"INSERT INTO Category (CategoryName, CategoryNumber) VALUES ('Whatever',
10)"

 

[Guest]

Thank you all for replying. I am at a stopping point (beginning of my task).
I have tried to name the table column as in:

INSERT INTO Category (desc) VALUES ('whatever')

and still get an error. The aforementioned command was my attempt at hacking
for a solution. Am I really doing everything right?

Kim

 

[Dirk Goldgar]

Thank you all for replying. I am at a stopping point (beginning of my
task). I have tried to name the table column as in:

INSERT INTO Category (desc) VALUES ('whatever')

and still get an error. The aforementioned command was my attempt at
hacking for a solution. Am I really doing everything right?

Now you're getting an error because your field name, "desc", is also a
SQL keyword, so it's causing your statement to be parsed incorrectly.
You need to put it in square brackets ([]) to keep it from being
misinterpreted:

INSERT INTO Category ([desc]) VALUES ('whatever')

 

[Guest]

Thanks SOOOOOO much. I guess that I should change my column name from desc to
descript.

Two other (probably simple) questions.

Can you also tell me how to grab focus on a window (restrict operator input
to only one window until the window exits)?

What function returns status regarding an object instantiation?

TKS TKS TKS,
Kim
--
Mrs. Kim A. Yoshiwara, Diamond Community School, Palm Bay, Florida

Thank you all for replying. I am at a stopping point (beginning of my
task). I have tried to name the table column as in:

INSERT INTO Category (desc) VALUES ('whatever')

and still get an error. The aforementioned command was my attempt at
hacking for a solution. Am I really doing everything right?

Now you're getting an error because your field name, "desc", is also a
SQL keyword, so it's causing your statement to be parsed incorrectly.
You need to put it in square brackets ([]) to keep it from being
misinterpreted:

INSERT INTO Category ([desc]) VALUES ('whatever')

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks SOOOOOO much. I guess that I should change my column name from
desc to descript.

Yes, that would save trouble in the long run. As a rule, it's best to
avoid names that contain spaces or other nonstandard characters, or that
are reserved words in SQL, VBA, the Access object model, the DAO object
model, or the ADO object model.

Two other (probably simple) questions.

Can you also tell me how to grab focus on a window (restrict operator
input to only one window until the window exits)?

That depends on what you mean by "window". You can set a form's Modal
property to Yes, which will restrict the user's interaction to either
that form or the Access menus. You can set both the Modal and PopUp
properties to Yes, which will restrict the user's interaction to the
form alone. Or, without making changes at design time, you can use code
to open a form in dialog mode, which will have a similar effect. The
code for that is like this:

DoCmd.OpenForm "YourFormName", WindowMode:=acDialog

What function returns status regarding an object instantiation?

I'm not sure what you mean. If you'll explain more, I may be able to
answer.

 

[Guest]

Usually in error exit code, you want to know of an object is valid, as in the
code:
Set rcdset = New ADODB.Recordset
has been executed and rcdset points to a valid object. (for the sake of
thuroughness and portability).

I tried functions isNull and isEmpty (or something like that). Is there a
function which returns the status of an object variable?

kim

 

[Dirk Goldgar]

Usually in error exit code, you want to know of an object is valid,
as in the code:
Set rcdset = New ADODB.Recordset
has been executed and rcdset points to a valid object. (for the sake
of thuroughness and portability).

I tried functions isNull and isEmpty (or something like that). Is
there a function which returns the status of an object variable?

Not a function, but a condition: Is Nothing. See this example:

Dim rs As ADODB.Recordset

' ... various code statements ...

If rs Is Nothing Then
' it was never set to anything
Else
' Close it if it's open, and set it to Nothing
End If

 

[Guest]

TKS U R a peach.