0

I am trying to Insert records from a local Access database to an Oracle table. Same fields in both tables.

I don't want to loop through the lines if I don't have to. I have to code to connect to the oracle database. Just not sure of the code to select the local Access table called "RET_PARTS" and insert that data to the Oracle table called RET2_PARTS_ORACLE"

    Sub subFastUpload()
`enter code here`On Error GoTo Err_subFastUpload

  `enter code here`Dim intlength As Integer
  `enter code here`intlength = 8
  `enter code here`Dim strServer As String
  strServer = "MARS"
 
  Const maxItems = 500
   
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim objRst As New ADODB.Recordset
  Dim objCon As New ADODB.Connection
  Dim objCmd As New ADODB.Command
  Dim strTable As String
  Dim intCount As Integer
  
  Set dbs = CurrentDb()
  Set rst = dbs.OpenRecordset("Ret2_Parts")
  
  Dim mars_pass As String
  'mars_pass = InputBox("Enter MaRS Password")
   mars_pass = "XXXXXXX"
  intCount = 1
  If strServer = "MARS" Then
    strTable = Environ("UserName") & ".RET2_PARTS_ORACLE"
    objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MARSNEW.123;User ID=" & Environ("UserName") & ";Password=" & mars_pass & ";"
    strTable = Environ("UserName") & ".RET2_PARTS_ORACLE"
  End If
  objCon.Open
  objCmd.ActiveConnection = objCon
  objCmd.CommandType = adCmdText
  objCmd.Properties("PLSQLRSet") = False
  While Not rst.EOF
    If intCount = 1 Then
    strSQL = "Select * from RET2_Parts"
    Debug.Print strSQL
Daniel
  • 17
  • 3
  • If you set a link to Oracle table, can then execute INSERT SELECT action SQL. – June7 Oct 11 '21 at 18:49
  • Does this answer your question? [Programatically create ODBC connection and link tables in MS Access](https://stackoverflow.com/questions/3096236/programatically-create-odbc-connection-and-link-tables-in-ms-access) – June7 Oct 11 '21 at 19:12

0 Answers0