VBA: Insert Into-Statement for linked Tables

[Sascha Hennig]

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

 

[Andi Mayer]

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?

 

[Sascha Hennig]

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?

 

[Albert D. Kallal]

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.

 

[Sascha Hennig]

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

 

[Guest]

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]

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?

 

[Andi Mayer]

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

 

[Sascha Hennig]

Dim db As DAO.Database

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

 

[Brendan Reynolds]

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.

 

[Andi Mayer]

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.

Your are right, forgot to write this.

I had to use it three days ago with SQLBase and reused an old Function
I wrote for Universe without looking which technology I used.

Here is an example :

Dim wkODBC As Workspace
Dim con As DAO.Connection

Set wkODBC = CreateWorkspace("ODBCWorkspace", "Admin", "", dbUseODBC)
Set con = wkODBC.OpenConnection("cAlpha", dbDriverNoPrompt, False,
"ODBC;DSN=cAlpha;UID=a;PWD=a;DATABASE=cAlpha")

Set rs1 = con.OpenRecordset("SELECT * FROM MyTab, , dbExecDirect )