Re: Intermittent problem in data transfer MSAccess to Oracle using VB

Actually, the rows with null values in the Hours column don't seem to be triggering the error condition. When it does fail, the row it fails on has always had a numeric value in the Hours column.
But I will try your suggestion.

Phil Hunt wrote:
So if the hour on Access is null, your sql will insert an empty string into a number field. That will cause an error.
You can try changing the IIF clause to put the word NULL, but with quote.

It is an Oracle error caused by your program, not by Oracel itself

"Carl" <nospam@xxxxxxxxxx> wrote in message news:OBTBj3PLLHA.6100@xxxxxxxxxxxxxxxxxxxxxxx

I created a small executable which runs 5 days a week as a scheduled task. It retrieves data from a MSAccess database and writes to an Oracle database. I write to a log file to make sure the process runs to completion. It worked fine for about two weeks then the app would abort, but only intermittently which makes it very difficult for me to troubleshoot. I tried a number of things without luck, and I am currently pursuing the problem from the Oracle end of things but wanted to see if anyone here has any advice.

Here's the running code in its entirety; the only changes I made are to passwords and file server names.

Sub Main()

On Error GoTo ErrorHandler

Dim LogFileName As String, ff As Long, QueryName As String
LogFileName = App.Path & "\ServiceLearningUpload.log"
QueryName = "qryServiceLearningExport"
' open log file for writing - close it at Exit Sub
ff = FreeFile
Open LogFileName For Append As #ff
' using Print instead of Write because Write delimits everything in quote marks...
Print #ff,
Print #ff, "Starting Service Learning upload: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss am/pm")

Dim MSAccessConn As String
Dim MSAccessConnObj As ADODB.Connection
Dim rsSource As ADODB.Recordset
'Dim counter As Long

Dim OracleConn As String
Dim cmmnd As ADODB.Command

MSAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Server1\Folder1\Subfolder1\SERVICE LEARNING.mdb;User Id=admin;Password=;"
OracleConn = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCE;User Id=USERID;Password=PASSWORD;"

Set MSAccessConnObj = New ADODB.Connection

MSAccessConnObj.ConnectionTimeout = 10 ' default is 15 seconds
MSAccessConnObj.ConnectionString = MSAccessConn
MSAccessConnObj.CommandTimeout = 30

If Not MSAccessConnObj.State = adStateOpen Then
Print #ff, "Failed to connect to MSAccess Service Learning database"
GoTo ExitSub
End If

Set rsSource = New ADODB.Recordset
rsSource.ActiveConnection = MSAccessConn
rsSource.CursorLocation = adUseClient ' to be able to use the RecordCount property
rsSource.Source = "SELECT * FROM " & QueryName

If rsSource.EOF And rsSource.BOF Then ' no recordset
Print #ff, QueryName & " returned no rows from MSAccess Service Learning database"
GoTo ExitSub
End If

Set cmmnd = New ADODB.Command
cmmnd.ActiveConnection = OracleConn
cmmnd.CommandType = adCmdText
cmmnd.CommandText = "DELETE FROM DATABASE1.TABLE1"

Do Until rsSource.EOF
cmmnd.CommandText = "INSERT INTO DATABASE1.TABLE1 SELECT '" & rsSource.Fields("Provider") & _
"', '" & rsSource.Fields("Procedure") & "', " & IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) & _
", '" & rsSource.Fields("Category") & _
"', '" & Replace(rsSource.Fields("Description"), "'", "''") & _
"', '" & rsSource.Fields("DateX") & _
"', '" & rsSource.Fields("Country") & _
"', " & rsSource.Fields("TripID") & " FROM DUAL"

cmmnd.Execute Options:=adExecuteNoRecords

Print #ff, "Upload completed successfully. Number of rows inserted: " & rsSource.RecordCount
Set rsSource = Nothing
Set MSAccessConnObj = Nothing

Print #ff, "Log file closed: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss am/pm")
Close #ff
Exit Sub

Print #ff, "Err.Number = " & Err.Number
Print #ff, "Err.Description = " & Err.Description
Print #ff, "Provider = " & rsSource.Fields("Provider")
Print #ff, "Procedure = " & rsSource.Fields("Procedure")
Print #ff, "Hours = " & rsSource.Fields("Hours")
Print #ff, "Category = " & rsSource.Fields("Category")
Print #ff, "Description = " & rsSource.Fields("Description")
Print #ff, "Date = " & rsSource.Fields("DateX")
Print #ff, "Country = " & rsSource.Fields("Country")
Print #ff, "TripID = " & rsSource.Fields("TripID")
GoTo ExitSub

End Sub

Additional information:

1) It has always executed correctly down to the first iteration of the 'do until...' loop. In other words, it has always written to the log file, connected to the Access database, returned a recordset, connected to the Oracle database and deleted all rows from DATABASE1.TABLE1 with no failures.

2) Every time it fails, it has failed on the first row of the recordset. To check this, I have sorted the Access query in different ways and on different columns; no matter how the recordset is sorted, when it fails, the row returned in the error handler has always been the first row retrieved from the dataset.

3) It has run successfully being executed from the server where it resides, and it has failed from that same server. This is also true running the executable from my machine, both within the IDE and just double-clicking on the .exe file.

4) It runs at 4:00 am five days a week. I check it when I get in to work. If it has failed, I run it by double-clicking on the .exe on my machine. It usually runs to completion at that point; every so often I need to double-click on it a second time because it will fail on my machine.

5) Here's the error log of the latest run; the error message is always the same. The data values will change based on how I have sorted the recordset as described earlier.

Starting Service Learning upload: 26-Jul-2010 4:00:00 am
Err.Number = -2147217900
Err.Description = ORA-01861: literal does not match format string
Provider = I8858303
Procedure =
Hours = 6
Category = Local Dental
Description = Kansas Avenue Church Health Fair
Date = 2010-APR-18
Country =
TripID = 2590
Log file closed: 26-Jul-2010 4:00:01 am

I do realize this is an Oracle error message and I am currently pursuing this with an Oracle newsgroup as well.

I then double-clicked on the .exe file on my machine and it ran to completion, as follows:

Starting Service Learning upload: 26-Jul-2010 8:01:35 am
Upload completed successfully. Number of rows inserted: 3924
Log file closed: 26-Jul-2010 8:01:50 am

6) Part of one line of code is as follows:

IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours"))

I had to do this because in a very few cases there needs to be a NULL value inserted into the Oracle database in the "Hours" column. If the incoming value is null, it needs to be enclosed in apostrophes; if it is not null, it cannot be enclosed in apostrophes because it is a numeric value. But, I don't think this snippet is causing the problem because it would be happening every time rather than intermittently. I have googled the Oracle error message number and have not been able to figure out how it applies in this case.

7) Here's the table format in Oracle:

desc TABLE1
Name Null Type
------------------------------ -------- -------------
Provider NOT NULL CHAR(10)
Procedure CHAR(7)
Hours NUMBER(5,1)
Category VARCHAR2(30)
Description VARCHAR2(80)
Country VARCHAR2(30)
8 rows selected

One thing that would help is to know which column the error message is referring to; because I'm doing an insert, all data values must be supplied at the same time so I don't know how to isolate the one causing the problem. Also, as far as I know, I'm not using any 'format strings' as stated in the error message. The REPLACE... construct is to replace every instance of one apostrophe with two so that when it gets inserted into the Oracle database the two apostrophes will be stripped back to one.

Any and all help will be very much appreciated. And if you've made it thus far, my thanks for simply wading through all of this...