How to do "INSERT INTO VALUES" with Access SQL

[Dominic Olivastro]

I have a vb5.0 program. I want to insert a record into an access table, and
I'd prefer not to use dao.AddNew ... dao.Update. Instead I want to use
dao.Database.Execute ("SQLString"), but I don't know what the string is. It
does not seem to be standard SQL (why is that?). In cases like this I
usually go to Access itself to find out, but to insert a record, I just use
the table itself -- there is not query for that that I can see.

What is the SQL for this? I want something like (in standard SQL):

INSERT
INTO <table> (field1, field2, field3)
VALUES ("this", 10, 25)



--
Dominic Olivastro
CHI Research, Inc

web: http://www.ChiResearch.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

 

[Brendan Reynolds]

This works for me, Dominic. Possibly the version of Jet you're using *might*
make a difference, I can't say for sure. This works with Jet 4, but I am not
in a position to test it with Jet 3.x right now.

Public Sub TestInsert()

CurrentDb.Execute "INSERT INTO Categories (CategoryName, Description)
VALUES ('MyNewCategory', 'My New Category')"

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tim Ferguson]

What is the SQL for this? I want something like (in standard SQL):

INSERT
INTO <table> (field1, field2, field3)
VALUES ("this", 10, 25)

Looks good to me. What error are you getting? It might help to post the VBA
you are using, as quoting the delimiters is what often knackers this kind
of statement.

B Wishes


Tim F

 

[Dominic Olivastro]

I'll never understand what happened. It wasn't working before, and now it
works just fine. I must have had a quote out of place, or something like
that, and I jumped the gun by deciding that Access SQL is non-standard.

Sorry. And thanks to Brendan and Tim for the help.

BTW, before I make a new post. Does access SQL have something similar to
the "TOP n" qualifier in SQL? For example, can I say "SELECT TOP 10 * FROM
<Table>"

Dom
--
Dominic Olivastro
CHI Research, Inc

web: http://www.ChiResearch.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

 

[Bas Cost Budde]

BTW, before I make a new post. Does access SQL have something similar to
the "TOP n" qualifier in SQL? For example, can I say "SELECT TOP 10 * FROM
<Table>"

Not similar--same!

 

[Brendan Reynolds]

To the best of my memory, the only difference in the Jet and T-SQL
implementations of 'TOP' are that Jet doesn't have the 'WITH TIES' option,
it always returns ties.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.