[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.vb.general.discussion

Intermittent problem in data transfer MSAccess to Oracle using VB

Carl

7/26/2010 8:05:00 PM

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
21 Answers

James Tyler

7/26/2010 8:50:00 PM

0

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@all.thanks> wrote in message
news:OBTBj3PLLHA.6100@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


Carl

7/26/2010 9:35:00 PM

0

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@all.thanks> wrote in message
> news:OBTBj3PLLHA.6100@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
>
>
>

Dee Earley

7/27/2010 7:54:00 AM

0

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@icode.co.uk)
i-Catcher Development Team

iCode Systems

(Replies direct to my email address will be ignored.
Please reply to the group.)

ralph

7/27/2010 11:13:00 AM

0

On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam@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...


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

Paul Clement

7/27/2010 1:21:00 PM

0

On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam@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...


Paul
~~~~
Microsoft MVP (Visual Basic)

Carl

7/27/2010 3:19:00 PM

0

Thanks for your reply. I created the SQL statement as a string during
the testing/debugging process but removed that part when it worked
correctly. I'm going to put that back in to see the exact statement
being executed.
Carl

Dee Earley wrote:
> 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)
>

Carl

7/27/2010 3:33:00 PM

0

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@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...
>
>
> 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

Carl

7/27/2010 3:53:00 PM

0

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@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...
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

James Tyler

7/27/2010 4:12:00 PM

0

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.

"Carl" <nospam@all.thanks> wrote in message
news:uWS2HPaLLHA.6128@TK2MSFTNGP06.phx.gbl...
> 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@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...
>>
>>
>> Paul
>> ~~~~
>> Microsoft MVP (Visual Basic)


Carl

7/27/2010 4:27:00 PM

0

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.
>