New to Forum, need help with ADO code

[JTKrupa]

Hello,

I'm new to this forum, and I need a little help with some code I've been commissioned to create to speed up some of our processes here at work. What I have is an Access '03 DB that is linked to several ODBC connections. In order to speed the processes and modules within these DBs up, I have been creating local tables using ADO connections to the Oracle tables (these local versions are MUCH MUCH smaller than the Oracle ones, as all the Oracle tables have tons of historical data). The thought process has been that if the program only has to hit 30,000 local records it will run a lot faster than if it has to hit 3,500,000 remote records. Its been working quite well, but for some reason, I can't figure this one out. Its the first time I've hit this particular table, and I know very little about it (data types, formats, etc)

What I have done is created a local version of the Oracle table (structure copy to make it easy). I am feeding in a date from an Access table (works fine) and then need to append all the records from the Oracle table that are greater than or equal to the date stored. It opens the connection just fine, but when it tries to open the remote recordset, it returns an "ORA-01858: non-numeric character was found where a numeric was expected" error message.

The bold faced Red is where its dying.

Here is what I have:

Dim myDate As Date
Dim cnnL As New ADODB.Connection
Set cnnL = CurrentProject.Connection
Dim mySQL As String

     mySQL = "SELECT * FROM ABC.DEF_GHI a"
     mySQL = mySQL & " WHERE TO_DATE('a.DT_INTERVAL','MM/DD/YYYY') >= TO_DATE('" & myDate & "')"
          Dim cnnR As New ADODB.Connection
          cnnR.Open "Provider=xxxx;Password=xxxx;User ID=xxxx;Data Source=xxxx"
          Dim rsR As New ADODB.Recordset
          [COLOR="Red"][B]rsR.Open mySQL, cnnR, adOpenStatic, adLockReadOnly[/B][/COLOR]
          Dim rsL As New ADODB.Recordset
          rsL.Open "SELECT * FROM ABC_DEF_GHI_LOCAL", cnnL, adOpenStatic, adLockOptimistic
    
If Not rsR.BOF And Not rsR.EOF Then
Do Until rsR.EOF
            rsR.MoveFirst
                rsL.AddNew 
                Dim i As Long
                For i = 1 To rsR.Fields.Count - 1
                    rsL.Fields(i).Value = rsR.Fields(i).Value
                    rsL.Update
                Next
            rsR.MoveNext
     Loop
End If

Obviously I've removed the cnnR info for security purposes.

I think it may possibly be an issue with the date format on the remote table. Is there a way I can determine what format is on the Oracle table easily if that is the case?

Again, I've been writing this using help from everywhere, as I've just recently started, so if its something obvious, please let me know.

Any help anyone can give would be greatly appreciated. If I have put this in the wrong forum, please let me know and I'll repost where its needed. T.I.A.

-JT

 

[boblarson]

I don't know anything about Oracle (and I don't know if this has anything to do with your problem), so if it's a syntax problem I can't contribute. But, I did notice that, unless you just didn't include it in this code shown here, myDate hasn't been assigned a value.

Also, I only know SQL Server and not Oracle, but is the syntax to assign an alias really

"SELECT * FROM ABC.DEF_GHI a"

instead of

"SELECT * FROM ABC.DEF_GHI As a"