I have captured data from PervasiveSQL, modified it (so I have the 'ET' of 'Extract, transform, and load). (Well, I actually load it into a table in access for safe keeping). At any rate, where I need this data is to update and insert a table in Oracle.
Here is what it looks like:
Dim cnOr As New ADODB.Connection
Dim rsAccess As New ADODB.Recordset
Dim strSQL As String
Dim strDBL As String
On Error GoTo TBackErrHand
'Set double quote
strDBL = Chr(34)
'Connect directly to cimnet through oracle
With cnOr
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDASQL.1;Password=cimf;Persist Security Info=True;User ID=cimfadmin;Data Source=cimv8local"
.Open
End With
strSQL = "Insert INTO CIMFADMIN_CIM_FOLDER01 ([FOLDER_ID], [PART_NO], [OP_NO], [MACHINE], " & _
"[REVISION], [SETUP], [TEARDOWN], [MINUTES], [OP_DESC], [BY_PART], [UDF1], " & _
"[UDF2], [PIECE_MULT], [PIECE_DIV], [D_RELEASED] ) " & _
"SELECT TestOracle.[FOLDER_ID], TestOracle.[PART_NO], " & _
"TestOracle.[OP_NO], TestOracle.[MACHINE], " & _
"TestOracle.[REVISION], TestOracle.[SETUP], " & _
"TestOracle.[TEARDOWN], TestOracle.[MINUTES], " & _
"TestOracle.[OP_DESC], TestOracle.[BY_PART], " & _
"TestOracle.[UDF1], TestOracle.[UDF2], " & _
"TestOracle.[PIECE_MULT], TestOracle.[PIECE_DIV], " & _
"TestOracle.[D_RELEASED] " & _
"FROM TestOracle IN " & strDBL & "\\platsrvr\db reports\AMData\TestTable.mdb" & strDBL & ";"
My thought was to have one connection object (to Oracle - this would prevent to popup login) and use the 'FROM' statement to make the connection to the access data. HOWEVER, and you all know where this is going, it doesn't work. I get 2 flavors of errors: 1) invalid identifier (the index is 'FOLDER_ID'), or 2) "Missing SELECT keyword..."
Should I be using a command object: objCmd.execute strSQL? Should I give up and shell out to Access and have the action queries execute on linked tables?
Here is what it looks like:
Dim cnOr As New ADODB.Connection
Dim rsAccess As New ADODB.Recordset
Dim strSQL As String
Dim strDBL As String
On Error GoTo TBackErrHand
'Set double quote
strDBL = Chr(34)
'Connect directly to cimnet through oracle
With cnOr
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDASQL.1;Password=cimf;Persist Security Info=True;User ID=cimfadmin;Data Source=cimv8local"
.Open
End With
strSQL = "Insert INTO CIMFADMIN_CIM_FOLDER01 ([FOLDER_ID], [PART_NO], [OP_NO], [MACHINE], " & _
"[REVISION], [SETUP], [TEARDOWN], [MINUTES], [OP_DESC], [BY_PART], [UDF1], " & _
"[UDF2], [PIECE_MULT], [PIECE_DIV], [D_RELEASED] ) " & _
"SELECT TestOracle.[FOLDER_ID], TestOracle.[PART_NO], " & _
"TestOracle.[OP_NO], TestOracle.[MACHINE], " & _
"TestOracle.[REVISION], TestOracle.[SETUP], " & _
"TestOracle.[TEARDOWN], TestOracle.[MINUTES], " & _
"TestOracle.[OP_DESC], TestOracle.[BY_PART], " & _
"TestOracle.[UDF1], TestOracle.[UDF2], " & _
"TestOracle.[PIECE_MULT], TestOracle.[PIECE_DIV], " & _
"TestOracle.[D_RELEASED] " & _
"FROM TestOracle IN " & strDBL & "\\platsrvr\db reports\AMData\TestTable.mdb" & strDBL & ";"
My thought was to have one connection object (to Oracle - this would prevent to popup login) and use the 'FROM' statement to make the connection to the access data. HOWEVER, and you all know where this is going, it doesn't work. I get 2 flavors of errors: 1) invalid identifier (the index is 'FOLDER_ID'), or 2) "Missing SELECT keyword..."
Should I be using a command object: objCmd.execute strSQL? Should I give up and shell out to Access and have the action queries execute on linked tables?
