Solved

Error -2147217900 (Syntax error in INSERT INTO statement.)

Posted on 2009-04-07
33
479 Views
Last Modified: 2013-12-19
I am getting an  error msg - possible issue with the Insert Into portion of the statement.

I am using a Oracle connection string to access oracle data and want to update/append to an Access table.

what am I missing?  Why can't I use the Insert Into in this context?

k
       strsql = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,"
        strsql = strsql & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,"
        strsql = strsql & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model,"
        strsql = strsql & "VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE"
        strsql = strsql & "VALUES('"
        strsql = strsql & Nz(adoRS("FirstofEquipment_ID"), "") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '"""
        strsql = strsql & varNM & "', '" & varEQL & "', '"""
        strsql = strsql & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "', '"
        strsql = strsql & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "', '"
        strsql = strsql & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
        strsql = strsql & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
        strsql = strsql & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & varPDate & "')"
    
    DoEvents
    Debug.Print strsql
        CurrentProject.Connection.Execute strsql, dbFailOnError
 
DIES HERE AFTER THE ATTEMPT TO CONNECTION THE SQL STRING
 
 
results of query:
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model,VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH,PWRD,TEST', '"WORK BENCH', 'RG TRLR', '"4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '')658', '658', '658')3/1/1967', '3/1/1967', '3/1/1967')

Open in new window

0
Comment
Question by:Karen Schaefer
  • 19
  • 12
  • 2
33 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 24091143
You have several type mistakes on your SQL INSERT statment like ) instead of '. The code below it's OK for you yo try it.

jppinto
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH,PWRD,TEST', '"WORK BENCH', 'RG TRLR', '4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '', '658', '658', '658','3/1/1967', '3/1/1967', '3/1/1967')

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 24091168
Could you please update the code instead of the actual query statement results, Not sure where to make the changes in my code.

thanks for the assist.

K
0
 
LVL 33

Expert Comment

by:jppinto
ID: 24091170
Sorry, still found 6 more errors on my code! Mainly ' missing! I think that this is OK now.
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH' ,'PWRD', 'TEST', 'WORK BENCH', 'RG TRLR', '4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '', '658', '658', '658', '3/1/1967', '3/1/1967', '3/1/1967')
 

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24091175
Having used a database to access Oracle before, I used one of two ways

1. Linked tables
2. Using ODBC, create ADO connection

Im assuming here you have linked tables?

Looking at your sql statement, you may have missing values, I know you used NZ but you would need to give a number for any numeric fields as default value.

You are dumping the generatd sql in the immediate window

debug.print strSQL

I suggest you look at that sql, paste it here, I can have a look and also run it in a new query to see what it is complaining about
0
 

Author Comment

by:Karen Schaefer
ID: 24091209
I am using a DSN-less connection string - here is my entire code - it works find until I try to update the Access table TL_FTEM_Temp.

Thanks for the assist.

K
Public Function FTCSConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim rstOra As ADODB.Recordset, rs As ADODB.Recordset
    Dim adoRS As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim ctl As Control
    Dim j, I As Long
    Dim rsField, tblField As String
    Dim rsValue, tblValue As String
    Dim varNM As Variant  ' Nomenclature_Modifier
    Dim varLSD As Variant ' Last Service Date
    Dim varRNG As Variant ' Range
    Dim varSDD As Variant ' Service Due Date Cmt
    Dim varEQL As Variant ' Equipment Location
    Dim varPAmt As Variant ' PURCHASE_AMT
    Dim varPDate As Variant ' PURCHASE_DATE
   On Error GoTo FTCSConnection_Error
 
    DoCmd.SetWarnings False
 
    Set cn = CurrentProject.Connection
 
    sConn = _
        "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
        "(Host=Server1.com)(Port=1521)))(CONNECT_DATA=(SID=serv1)));" & _
        "User Id=*****;Password=******"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    Set adoRS = New ADODB.Recordset
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT varchar"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE varchar"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Delete * from TL_FTEM_TEMP"
        CurrentProject.Connection.Execute STRSQL
    Set rs = New ADODB.Recordset
    STRSQL = "Select * from TL_FTEM_TEMP"
    rs.Open STRSQL, cn, adOpenDynamic, adLockOptimistic
 
    STRSQL = " SELECT DISTINCT A.FTEM_ID AS FirstofEquipment_ID, A.EQPT_NAME AS NOMENCLATURE, A.NOMEN_MODIFIER_NAME AS Nomenclature_Modifier," & _
                    " A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE,COUNT(*) AS CountOfEQUIPMENT_ID," & _
                " A.SERVICE_DUE_DATE_CMT, A.LAST_SERVICE_DATE, A.MFR_NAME AS Manufacturer, A.MFR_NO AS Model, A.PART_VENDOR_SERIAL_NO AS VendorPart, A.RANGE," & _
                " EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
                " FROM Server1.FTEM_VI_VW AS A INNER JOIN Server1.EQUIPMENT_MANAGEMENT AS EM ON A.FTEM_ID = EM.FTEM_ID" & _
                " GROUP BY A.FTEM_ID, A.EQPT_NAME, A.NOMEN_MODIFIER_NAME, A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE," & _
                    " A.Service_Due_Date_CMT," & _
                    " A.LAST_SERVICE_DATE, A.MFR_NAME, A.MFR_NO, A.PART_VENDOR_SERIAL_NO, A.RANGE," & _
                    " EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
                " HAVING (((A.SERVICE_ORGN_CODE) Is Not Null))" & _
                " ORDER BY A.FTEM_ID;"
                Debug.Print STRSQL
    Set adoRS = New ADODB.Recordset
    adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly
    adoRS.MoveFirst
    Do Until adoRS.EOF
        If Not IsNull(adoRS("Nomenclature_Modifier")) And adoRS("Nomenclature_Modifier") <> "" Then
            varNM = Replace(adoRS("Nomenclature_Modifier"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varNM = Replace(adoRS("Nomenclature_Modifier"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") <> "" Then
            varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS("SERVICE_DUE_DATE_CMT") <> "" Then
            varSDD = Format(DateSerial(Left(adoRS("SERVICE_DUE_DATE_CMT"), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3, 2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
        Else
            varSDD = adoRS("SERVICE_DUE_DATE_CMT")
        End If
        Debug.Print varSDD
        If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS("LAST_SERVICE_DATE") <> "" Then
            varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short Date")
        Else
            varLSD = adoRS("LAST_SERVICE_DATE")
        End If
        If Nz(adoRS("Range"), "") <> "" Then
            varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
            varRNG = LTrim(varRNG)
        End If
        If Not IsNull(adoRS("PURCHASE_AMT")) And adoRS("PURCHASE_AMT") <> "" Then
           varPAmt = Replace(adoRS("PURCHASE_AMT"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
           varPAmt = Replace(adoRS("PURCHASE_AMT"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("PURCHASE_DATE")) And adoRS("PURCHASE_DATE") <> "" Then
            varPDate = Format(CDate(adoRS("PURCHASE_DATE")), "Short Date")
        Else
            varPDate = adoRS("PURCHASE_DATE")
        End If
        STRSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        STRSQL = STRSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        STRSQL = STRSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, "
        STRSQL = STRSQL & "VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE" & _
        STRSQL = STRSQL & "VALUES('"
        STRSQL = STRSQL & Nz(adoRS("FirstofEquipment_ID"), "") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '"
        STRSQL = STRSQL & varNM & "', '" & varEQL & "', '"
        STRSQL = STRSQL & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "', '"
        STRSQL = STRSQL & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "', '"
        STRSQL = STRSQL & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
        STRSQL = STRSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
        STRSQL = STRSQL & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & varPDate & "')"
    
    DoEvents
        CurrentProject.Connection.Execute STRSQL, dbFailOnError
        adoRS.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    adoRS.Close
    Set adoRS = Nothing
    
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT Date"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE Date"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column PURCHASE_DATE Date"
         CurrentProject.Connection.Execute STRSQL
    DoCmd.Close acTable, "TL_FTEM_TEMP", acSaveYes
   On Error GoTo 0
   Exit Function
 
FTCSConnection_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FTCSConnection of Module Functions"
     
End Function

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091215
Another way to add a record, which will definitely make life easier for you, is to create a recordset to add. I say this due to the large number of fields you have

dim rsAdd as new adodb.recordset

set rsAdd = currentdb.openrecordset("TL_FTEM_Temp")
rsAdd.AddNew

'Set fields
rsAdd!FirstofEquipment_ID = adoRS!FirstofEquipment_ID
rs!NOMENCLATURE = adoRS!NOMENCLATURE
rs!Nomenclature_Modifier = varNM
'etc for all your other values

rsAdd.Update

0
 

Author Comment

by:Karen Schaefer
ID: 24091258
How would I handle the conversion of the data types - I would prefer not to use the Alter Table statements if I don't have to.  - especially on the Service_Due_Date_CMt field - that comes across as a number ie. 090824  needs to be converted and stored as a Short date - Problem I am having is the conversion from Oracle to Access.
0
 

Author Comment

by:Karen Schaefer
ID: 24091316
wouldn't I need to include the conversion strings used in the Values section of the Sql statement?

K
0
 

Author Comment

by:Karen Schaefer
ID: 24091328
I mean to handle the Null values?

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091396
looking at your sql

you got so many payments, yet just one payment in the field list

        strSQL = strSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091466
yes, put your NZ in as usual
Im not sure if a date conversion will be done but the good thing about using recordsets is you dont have to worry about datatype.

In your sql, I split it up (not runnable) so you can verify if the value is right for the field

        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & ", '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
       
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "',"
       
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
       
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE"
        strSQL = strSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "',"

        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
       

If all looks right, put it in the right order
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091490
here it is merged
        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & ", '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "',"
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"


question, is the firstequipmentid a number or text?
same question to payment amount  (this has been put into quotes, if number, do u really want quotes?)

with the dates, not sure if you have to use to_date on it
0
 

Author Comment

by:Karen Schaefer
ID: 24091500
However, I need to convert Date for Service_Due_Date_cmt - this is not formatted as s date in the Oracle data - it is formatted ie.  090824 or not a Date Serial - hence the need to convert the date and format as a short date.
0
 

Author Comment

by:Karen Schaefer
ID: 24091503
would you recommend I use the strsql string or convert my code to and Update recordset Like you first suggested?
0
 

Author Comment

by:Karen Schaefer
ID: 24091538
EquipId = Text, PAmt is Number - Last serv date = date
tl-ftemview.png
0
 

Author Comment

by:Karen Schaefer
ID: 24091654
I am getting an error:

Error -2147217913 (Data type mismatch in criteria expression.)

got any ideas?

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091806
ok, this handles the numbers. We have removd single quotes


strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', " & adoRS("CountOfEQUIPMENT_ID") & ","
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & varPAmt & ", '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"


date is in YYMMDD format

recommend - well either would do, sql insert or recordset, for large number of fields, I prefer the lattr just cos it makes maintenence easier

as a test, can you hardcode the dates, at the very least we know all we got left ot sort out is the dates
0
 

Author Comment

by:Karen Schaefer
ID: 24091857
Still getting data type mismatch. - I even hard coded the SDD date - no luck
0
 

Author Comment

by:Karen Schaefer
ID: 24091866
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE)  VALUES ('BC 336493', 'BENCH,PWRD,TEST', 'WORK BENCH','RG TRLR', '4212', 1,'12/1/2009', '', 'DUPAR DYN','BOEING SPEC', '', '',658, '3/1/1967')
0
 

Author Comment

by:Karen Schaefer
ID: 24091885
just notice that some of the text contains commas - could this be cause the issue?

'BENCH,PWRD,TEST',
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092019
no because it is in quotes
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092073
can u try this, wrap dates with to_date
dunno if it will work with date mask YYMMDD, might need to change it around

in your hardcoded test, change the date to be like htis

from

12/1/2009

to

TO_DATE('12/1/2009','MM/DD/YYYY')




if it works, try running it with this updated query

        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', " & adoRS("CountOfEQUIPMENT_ID") & ","
        strSQL = strSQL & "to_date('" & varSDD & "','YYMMDD'), '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & varPAmt & ", to_date('" & adoRS("PURCHASE_DATE") & "','YYMMDD')"


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092156
back online tomorrow, late now so signing off, e-speak tomorrow, good nite
0
 

Author Comment

by:Karen Schaefer
ID: 24092177
goodnite- I am stepping thru each field to see which one is causeing the problem - so far not finding where the issue lies - thanks for the assist.

k
0
 

Author Comment

by:Karen Schaefer
ID: 24092471
After stepping thru the sql string - it is definitely the dates that are causing the problems - when I remove them from the string - the code works correctly.  Need to be able to input the dates either as is and convert before updating the final table(linked) or find a way to convert the dates.

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24094392
did u try using to_date around it? that it the way in oracle sql to insert dates
0
 

Author Comment

by:Karen Schaefer
ID: 24097724
Yes, I did, however, it error - Access did not recognize it.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24099060
its gotta be inside the string, not outside as it is not a access function. is that what u did?

so in your hardcoded test
where you have in quotes

"12/1/2009"

change to

"TO_DATE('12/1/2009','MM/DD/YYYY')"

if u having difficulties with your hardcoded test, paste it here then I will change it for you. I need to confirm if this is what we need to do

0
 

Author Comment

by:Karen Schaefer
ID: 24099312
got the following error:

Error -2147217900 (Syntax error (missing operator) in query expression ''TO_DATE('12/1/2009','MM/DD/YYYY')''.)

debug results:  TO_DATE('12/1/2009','MM/DD/YYYY')

I even tried to just past the dates as is into the temp table then using an Access query try to convert the text into dates using the following syntax - however got #Error in the empty cells - I played with the ISEmpty, ISNull - both still return the #Error on the what appears to be empty fields.

vcDueDate: IIf(IsNull([SERVICE_DUE_DATE_CMT]),"Hello",Format(DateSerial(Left([SERVICE_DUE_DATE_CMT],2),Mid([SERVICE_DUE_DATE_CMT],3,2),Right([SERVICE_DUE_DATE_CMT],2)),"Short Date"))

Or

Test: CDate([SERVICE_DUE_DATE_CMT])

    adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly
    adoRS.MoveFirst
    Do Until adoRS.EOF
        If Not IsNull(adoRS("Nomenclature_Modifier")) And adoRS("Nomenclature_Modifier") <> "" Then
            varNM = Replace(adoRS("Nomenclature_Modifier"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varNM = Replace(adoRS("Nomenclature_Modifier"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") <> "" Then
            varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''") 'double up single quotes
        End If
        'If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS("SERVICE_DUE_DATE_CMT") <> "" Then
            varSDD = "TO_DATE('12/1/2009','MM/DD/YYYY')"
          '  varSDD = Format(DateSerial(Left(adoRS("SERVICE_DUE_DATE_CMT"), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3, 2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
       Debug.Print varSDD
       ' Else
            'varSDD = adoRS("SERVICE_DUE_DATE_CMT")
        'End If
        If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS("LAST_SERVICE_DATE") <> "" Then
           ' varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short Date")
        'Else
            varLSD = adoRS("LAST_SERVICE_DATE")
        End If
        If Nz(adoRS("Range"), "") <> "" Then
            varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
            varRNG = LTrim(varRNG)
        End If
        If Not IsNull(adoRS("PURCHASE_AMT")) And adoRS("PURCHASE_AMT") <> "" Then
            varPAmt = Replace(adoRS("PURCHASE_AMT"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varPAmt = Replace(adoRS("PURCHASE_AMT"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("PURCHASE_DATE")) And adoRS("PURCHASE_DATE") <> "" Then
            varPDate = adoRS("PURCHASE_DATE")
        End If
      
        STRSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,"
        STRSQL = STRSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        STRSQL = STRSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, "
        STRSQL = STRSQL & "Manufacturer, "
        STRSQL = STRSQL & "Model, VendorPart, RANGE, "
        STRSQL = STRSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        STRSQL = STRSQL & " VALUES ('"
        STRSQL = STRSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "'," ',"
        STRSQL = STRSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & adoRS("CountOfEQUIPMENT_ID") & "',"
        STRSQL = STRSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        STRSQL = STRSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        STRSQL = STRSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"
    
    DoEvents
        CurrentProject.Connection.Execute STRSQL, dbFailOnError
        adoRS.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    adoRS.Close
    Set adoRS = Nothing
 
'         CurrentProject.Connection.Execute STRSQL
    DoCmd.Close acTable, "TL_FTEM_TEMP", acSaveYes

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 24099468
also getting on the SvcDueDateCmt field when there s an actual data - 2/19/1999

Error -2147217833 (The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24099848
sorry, got busy with somthing

right

I see what u did with the date, you still got the payment date to change but if varsDD aint working then thats an issue. What is the datatype of this in the oracle table? fact that it doesnt take a date is worrying. I know its a date in Access but just confirm the field datatype in oracle.

I dont know if u got any tools, like TOAD or SQL*Plus to do it.
0
 

Author Comment

by:Karen Schaefer
ID: 24102321
solved the problem by using the Alter table function to change the field types to varchar then changing it back on the end of running the update/append queries.  the changes where made to the temp table, then used another append query to update the linked table source.  and this seems to work great.

thanks for all your input.

Karen
0
 

Author Closing Comment

by:Karen Schaefer
ID: 31567703
help point me into the right direction - see my last comment for my workaround.

thanks for your assistance
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question