Prev: Intermittent problem in data transfer MSAccess to Oracle using VB
Next: Problem automating page in IE 8
From: Carl on 26 Jul 2010 17:35
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.
Thanks,
Carl

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(a)all.thanks> wrote in message
> news:OBTBj3PLLHA.6100(a)TK2MSFTNGP05.phx.gbl...
>
>>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
>> MSAccessConnObj.Open
>>
>> 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
>> rsSource.Open
>>
>> 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"
>> cmmnd.Execute
>>
>> 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
>> rsSource.MoveNext
>> Loop
>>
>> Print #ff, "Upload completed successfully. Number of rows inserted: " &
>>rsSource.RecordCount
>> rsSource.Close
>> Set rsSource = Nothing
>> MSAccessConnObj.Close
>> Set MSAccessConnObj = Nothing
>>
>>ExitSub:
>> Print #ff, "Log file closed: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss
>>am/pm")
>> Close #ff
>> Exit Sub
>>
>>ErrorHandler:
>> 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)
>>Date DATE
>>Country VARCHAR2(30)
>>TripID NOT NULL NUMBER(6)
>>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...
>>
>>Thanks,
>>Carl
>
>
>
From: Dee Earley on 27 Jul 2010 03:53
On 26/07/2010 21:05, Carl wrote:
> 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.
<SNIP>

Have you tried logging the SQL statement being run?
Assign it to a variable then execute that, and you can then include that
in the log (as it won't be changed by anything else)

--
Dee Earley (dee.earley(a)icode.co.uk)
i-Catcher Development Team

iCode Systems

(Replies direct to my email address will be ignored.
Please reply to the group.)
From: Carl on 27 Jul 2010 11:32
I will add functionality to look at the ADODB.Connection errors
collection - thanks for the link.
The MDB file is relatively small; 26 MB which compacted down to 13MB
this morning when I ran Compact and Repair Database from the
Tools>Database Utilities menu.
Just for testing purposes, I ticked the 'Compact on Close' checkbox
under Tools>Options>General. I'll post back when the issue gets resolved
and if I know what ultimately resolved it :-)
Again, thanks for your time.
Carl

ralph wrote:
> On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam(a)all.thanks> wrote:
>
>
>
>>ErrorHandler:
>> 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
>>
>
>
> Instead of just accessing the VB Error, also enumerate the
> ADODB.Connection Errors collection. There may be additional
> information in subsequent errors.
> http://www.devx.com/tips/Tip/13483
>
>
> How is the size of the MDB? Is it growing? Does it shrink dramatically
> when compacted? If so you may want to write an automatic compact
> routine providing weekly maintenance.
>
> I have on occasion run into weird intermittent errors with MSAccess
> where simply restarting the application or performing a "do-over"
> works. I often punt - at least until I can isolate the specific
> problem - by wrapping the routine with a process that simply waits for
> a bit then trys again. The 'wait interval' can be random or
> progressive. Always supply a bail-out count - so many tries then fail
> for good.
>
> -ralph
From: Carl on 27 Jul 2010 11:52
Thanks for your suggestion Paul. I threw this together in a hurry and
although I've used command parameters a couple of times, it was quicker
to create the SQL statement. Time permitting, I will redo this with
command parameters and post back with results.
Thanks again,
Carl

Paul Clement wrote:
> On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam(a)all.thanks> wrote:
>
>
> � 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
> � rsSource.MoveNext
> � Loop
> �
>
> Fairly certain you have a date format issue. I always recommend using Command Parameters instead of
> inserting the values directly into the SQL statement. If the DateX column in the source is not a
> Date data type then it should be converted before being assigned to a Command Parameter.
>
> You can find some Command Parameter examples at the below link:
>
> http://support.microsoft.com/kb/176936
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
From: Carl on 27 Jul 2010 12:27
Hi Phil,
I was wondering about something along those lines...how would that work?
Can an ADODB recordset be treated as a database table? For example,

INSERT INTO DATABASE1.TABLE1 SELECT * FROM ...

and then what? I don't think you can reference the recordset there, and
the INSERT statement will not be aware of the MSAccess table, so...

but I am willing to learn; I originally wanted to do it via a bulk
insert but did not know how to.

Thanks for your time!
Carl

Phil Hunt wrote:
> If you are going to re-write, may I suggest using "Insert Into Oracel_DB
> Select ... from Access" construct. You don't even need the loop and
> extranaous VB variable. One sql stmt does it all.
>
 |  Next  |  Last
Pages: 1 2
Prev: Intermittent problem in data transfer MSAccess to Oracle using VB
Next: Problem automating page in IE 8