"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

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

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

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

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

> 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/kb/176936
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

"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/kb/176936
>>
>>
>> Paul
>> ~~~~
>> Microsoft MVP (Visual Basic)

> 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:ORvOYiaLLHA.2276@TK2MSFTNGP06.phx.gbl...
> 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.
>>

"Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message
news:s56u46dcmt1bo3g9t9u9laqjqbt23t37m6@4ax.com...
> On Tue, 27 Jul 2010 13:08:09 -0400, "Phil Hunt" <a**@aaa.com> wrote:
>
> ¤ You raised a good point, it is a little tricky to cross ref 2 DB.
> Although
> ¤ you can do it with linked table, it gets to be as complicated as what
> you
> ¤ have now. Forget I said that, unless someone can chime in
> ¤
> ¤
>
> Yes, can be done if the Oracle table is linked to the Access database:
>
> INSERT INTO LinkedOracleTable (Col1, Col2, Col3) SELECT Col1, Col2, Col3
> FROM AccessTable
>
> If it's not linked then the (ODBC) Oracle connection string would need to
> be provided in the SQL
> statement.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

On Tue, 27 Jul 2010 18:55:43 -0400, "Kevin Provance" <k@p.c> wrote:

>
>"Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message
>news:6ldu46tllij1ftuusdnimulmsiopk0miqp@4ax.com...
>: On Tue, 27 Jul 2010 14:24:08 -0400, "Phil Hunt" <a**@aaa.com> wrote:
>:
>: ¤ Paul, I am curious what the SQL stmt looks like with connect striing in
>it .
>: ¤
>:
>: I'll see if I can come up with something functional.
>
>Didn't someone post a site that dealt with connection strings?  Damn, I
>should have bookedmarked it.

"Kevin Provance" <k@p.c> wrote in message
news:i2no5b$1js$1@news.eternal-september.org...
>
> "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message
> news:6ldu46tllij1ftuusdnimulmsiopk0miqp@4ax.com...
> : On Tue, 27 Jul 2010 14:24:08 -0400, "Phil Hunt" <a**@aaa.com> wrote:
> :
> : ¤ Paul, I am curious what the SQL stmt looks like with connect striing
> in
> it .
> : ¤
> :
> : I'll see if I can come up with something functional.
>
> Didn't someone post a site that dealt with connection strings?  Damn, I
> should have bookedmarked it.
>

> On Tue, 27 Jul 2010 14:24:08 -0400, "Phil Hunt" <a**@aaa.com> wrote:
>
> ¤ Paul, I am curious what the SQL stmt looks like with connect striing in it .
> ¤
> ¤
>
> With a connection to the Access database this worked for me:
>
> INSERT INTO [ODBC;Driver={Microsoft ODBC For
> Oracle};Server=ServerName;Uid=userID;Pwd=password;].[ORDERS] (ORDERID, CUSTOMERID, EMPLOYEEID)
> SELECT ORDERID, CUSTOMERID, EMPLOYEEID FROM ORDERS
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

"Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message
news:vlt0565gc0457injhfsd41j5scnij15his@4ax.com...
> On Tue, 27 Jul 2010 14:24:08 -0400, "Phil Hunt" <a**@aaa.com> wrote:
>
> ¤ Paul, I am curious what the SQL stmt looks like with connect striing in
> it .
> ¤
> ¤
>
> With a connection to the Access database this worked for me:
>
> INSERT INTO [ODBC;Driver={Microsoft ODBC For
> Oracle};Server=ServerName;Uid=userID;Pwd=password;].[ORDERS] (ORDERID,
> CUSTOMERID, EMPLOYEEID)
> SELECT ORDERID, CUSTOMERID, EMPLOYEEID FROM ORDERS
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)