VBA: Insert Into-Statement for linked Tables

hallo,

i have a problem with an insert-statement and vba. i use access 2002 and
linked tables to a oracle database 8.1. i want to insert a new record with
this:

Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)

for a not linked table, this code works fine. but for a linked one access
gives the errormessage "Syntax error in INSERT INTO statement" via sql-plus
there is no problem with the statement above.
where is the fault?

please help!

thanks,

sascha

On Wed, 5 Jan 2005 16:36:23 +0100, "Sascha Hennig"
wrote:


Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)

try db.Execute (strSQL),dbExecDirect

this is bypassing the Query-optimizer

and the field Name is text?
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)



try db.Execute (strSQL),dbExecDirect

access says that it does not know this argument "dbExecDirect"

and the field Name is text?

yes, this is ok. i have tested it with toad and oracle have no complaints.

any other suggestions?

Can you open the linked table..and edit, and add new records to the linked
table?

If you can't do the above..then you example will not work.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Can you open the linked table..and edit, and add new records to the linked
table?

If you can't do the above..then you example will not work.

yes, i can open and edit the linked table. if i use a recordset to insert
only 1 data set, it also works fine. But if i try to put a second data set
into the same table (within the same procedure, with the same recordset or a
second one), then the odbc-connection fails. because of this problem, i want
to try the way with "db.Execute". but this fails with this syntax error. but
why???

Could try the following:

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = CurrentProject.Connection
With cmd1
.CommandText = "INSERT INTO table1 (Name) VALUES ('bala')"
.CommandType = adCmdText
.Execute
End With
Set cmd1 = Nothing

Another thought is to enclose Name in square brackets []

Pete

"Sascha Hennig" wrote:



Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)



try db.Execute (strSQL),dbExecDirect

access says that it does not know this argument "dbExecDirect"

and the field Name is text?

yes, this is ok. i have tested it with toad and oracle have no complaints.

any other suggestions?

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = CurrentProject.Connection
With cmd1
.CommandText = "INSERT INTO table1 (Name) VALUES ('bala')"
.CommandType = adCmdText
.Execute
End With
Set cmd1 = Nothing


access do not know "ADODB" and i too. what is it? can i get it with a
libary, or so?

On Wed, 5 Jan 2005 17:34:22 +0100, "Sascha Hennig"
wrote:



Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)



try db.Execute (strSQL),dbExecDirect

access says that it does not know this argument "dbExecDirect"


A general Hint: nearly all if the answers are untested, therefore a
look in the help file would have shown you that I used brackets where
no brackets should be

db.Execute strSQL,dbExecDirect

---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)



try db.Execute (strSQL),dbExecDirect

access says that it does not know this argument "dbExecDirect"


A general Hint: nearly all if the answers are untested, therefore a
look in the help file would have shown you that I used brackets where
no brackets should be

db.Execute strSQL,dbExecDirect

i used no brackets too (and onesmore with brackets). that is not the
problem. access do not know "dbExecDirect".

The help file says that dbExecDirect can be used with ODBCDirect Connection
and QueryDef objects only. You will not be able to use it with your Database
object. Possibly using ODBCDirect might be the solution, but this outside of
my experience.

--
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.


"Sascha Hennig" wrote in message
...
Dim db As DAO.Database
Set db = CurrentDb()

Dim strSQL As String
strSQL = "INSERT INTO table1 (Name) VALUES ('bala')"
db.Execute (strSQL)



try db.Execute (strSQL),dbExecDirect

access says that it does not know this argument "dbExecDirect"


A general Hint: nearly all if the answers are untested, therefore a
look in the help file would have shown you that I used brackets where
no brackets should be

db.Execute strSQL,dbExecDirect

i used no brackets too (and onesmore with brackets). that is not the
problem. access do not know "dbExecDirect".