Help with Code Updating Oracle table from an Access table!

  • Thread starter Thread starter tp
  • Start date Start date
T

tp

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 Access
table doesn't work.

Sub Get_All_Users_Usage()
'**********************************************************
**************
'* Function RUN_OVERNIGHT ASSIGNMENT PROCESS
'* Created by Mike Perrou Winter 2004
'*
'* 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 when
'* modifications are made to Oracle table. Asks for you to
login again.
'**********************************************************
******************

' Declare Statements for ADODB Connections and Command

Dim cnn As ADODB.Connection
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command

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


' Set Provider, ConnectionString, Passwords, and Open

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

End With

' Set New Command

Set cmd1 = New ADODB.Command

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

With cmd1

.ActiveConnection = cnn1
.ActiveConnection = cnn


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

End With

' Execute Command cmd1
cmd1.Execute


End Sub


Would you be able to help with the right syntax
in order for this to work?
 
You need to have a database engine that can see both sets
of data. Either an Oracle server with a link to your
Access Data, or a Jet Database Engine with a link to your
Oracle data.

If I understand your code correctly, you are trying to
tell Oracle to update an Oracle table. I don't see in
your code where you are telling it the name of a linked
Access Table or Database?

If you decide that the Oracle Engine should be able to
see your Access data, you should test a Select statement
on the Oracle connection, to see that your Access database
is connected correctly to your Oracle Server.

An option might be to connect to Jet instead of Oracle,
and tell Jet to use an Access database, and use Jet
to update either a linked table or an ODBC connection.

If this code is in Access, you could use the default
database connection and a linked table to the Oracle
table, or you could use SQL with the target table as
a fully qualified name: [odbc connect].
as [alias]

(david)


tp said:
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 Access
table doesn't work.

Sub Get_All_Users_Usage()
'**********************************************************
**************
'* Function RUN_OVERNIGHT ASSIGNMENT PROCESS
'* Created by Mike Perrou Winter 2004
'*
'* 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 when
'* modifications are made to Oracle table. Asks for you to
login again.
'**********************************************************
******************

' Declare Statements for ADODB Connections and Command

Dim cnn As ADODB.Connection
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command

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


' Set Provider, ConnectionString, Passwords, and Open

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

End With

' Set New Command

Set cmd1 = New ADODB.Command

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

With cmd1

.ActiveConnection = cnn1
.ActiveConnection = cnn


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

End With

' Execute Command cmd1
cmd1.Execute


End Sub


Would you be able to help with the right syntax
in order for this to work?
Click to expand...
 
Hi David

Thank you for your reply! If I understand correctly the Jet Database Engine with a link to you
Oracle data approach is what I want to do.
In the past, I had an Oracle table that I have linked into an access database( I have been given admi
rights and password). I updated daily using an access query.
However, my problem is that I don't want to manually put in the Oracle admin password everyday I run update

Would you be able to show the changes needed in order for the Jet Database Engine approach to work

Thank you for your time and expertise
tp
 
First establish a linked table in Access, linked to
your Oracle table. Tell Access to save the user name
and password with the link. Check that you can read
and write the Oracle data using the linked table.

Then test an update query in Access, using your
source table and your destination linked table

If you wish, you can stop here.

Or,

Then get the connect string from the linked table:
....type [ctrl][g] and in the immediate window type
?codedb.tabledefs("linkname").connect ...

Open in SQL view the update query you were testing,
and replace the references to LinkName with references
to Alias name, except in the FROM clause of the SQL,
where you use [connect].[tablename] as [aliasname].

Now you have SQL for a Jet update query.


Try it and see how far you get.

(david)



ap said:
Hi David,

Thank you for your reply! If I understand correctly the Jet Database Engine with a link to your
Oracle data approach is what I want to do.
In the past, I had an Oracle table that I have linked into an access
Click to expand...
database( I have been given admin
rights and password). I updated daily using an access query.
However, my problem is that I don't want to manually put in the Oracle
Click to expand...
admin password everyday I run update.
 
Back
Top