Solved

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

Posted on 2009-04-07
33
472 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now