Access VBA Code Updating Oracle Table!

[Guest]

Greetings

The following code is what I planned to use in order to
update an Oracle table from an Access table
However, I am having problems in my code recognizing both
databases in my sql statement. Code works fine as long as
I hard code the values to update. When I try to use the Access
table it doesn't work. Would anyone be able to help with the right synta
in order for this to work? Thank You for your tim

Sub Update_All_Assignments(
'*********************************************************
*************
'* Function RUN_OVERNIGHT ASSIGNMENT PROCES
'* Created by tp Winter 200
'
'* THIS MODULE IS FOR NIGHTLY REMEDY UPDATES FOR ACCT
ASSIGN
'* This runs update query with pass-through user id and
password
'
'
'* Select Remember password when linking out to ODBC
Driver fails whe
'* modifications are made to Oracle table. Asks for you to
login again
'*********************************************************
*****************

' Declare Statements for ADODB Connections and Comman

Dim cnn As ADODB.Connectio
Dim cnn1 As ADODB.Connectio
Dim cmd1 As ADODB.Comman

Set cnn = CurrentProject.Connectio
Set cnn1 = New ADODB.Connectio


' Set Provider, ConnectionString, Passwords, and Ope

With cnn
.Provider = "MSDAORA
.ConnectionString = "data
source=remedy.dnt.dialog.com;" &
"intial catalog = remedy; " &
"user id = xxxx;password=xxxx#;
.Ope

End Wit

' Set New Comman

Set cmd1 = New ADODB.Comman

' In Command Open both Current Project Connection and
Oracle Database tabl
' Command Text with Sql statement ment to update Oracle
table ARADMIN.T222 with local access table dat

With cmd

.ActiveConnection = cnn
.ActiveConnection = cn


.CommandText = "UPDATE ARADMIN.T222 "
& "SET C4 = [2003 Account Assignments-AutoUp]
[admin].[Assignments].[Rep1-Login], "
& "C536870986 = [2003 Account Assignments
AutoUp].[admin].[Assignments].[Rep2-Login] "
& "WHERE C536870988 IN(SELECT [2003 Account
Assignments-AutoUp].[admin].[Assignments].[Orig_User] FROM
[2003 Account Assignments-AutoUp].[admin].[Assignments]) "

& "AND C536870955 IN(SELECT [2003 Account
Assignments-AutoUp].[admin].[Assignments].[Source] FROM
[2003 Account Assignments-AutoUp].[admin].[Assignments])

.CommandType = adCmdTex

End Wit

' Execute Command cmd
cmd1.Execut

End Su

 

[Chris]

Yeah, it's not going to work.

You ADO connection knows about Oracle.

An Access connection knows about Access.

You can't really marriage the two. Either use values, or
create a linked table. I've always just executed Insert
Into Table (Field1, Field2) Values ('Value1','Value2')

Chris