Karen Schaefer
asked on
Error -2147217900 (Syntax error in INSERT INTO statement.)
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
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')
ASKER
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
thanks for the assist.
K
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')
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
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
ASKER
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
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
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("T L_FTEM_Tem p")
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
dim rsAdd as new adodb.recordset
set rsAdd = currentdb.openrecordset("T
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
ASKER
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.
ASKER
wouldn't I need to include the conversion strings used in the Values section of the Sql statement?
K
K
ASKER
I mean to handle the Null values?
k
k
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 & "')"
you got so many payments, yet just one payment in the field list
strSQL = strSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
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_COD E"), "") & "', '" & 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
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
strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_COD
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
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_COD E"), "") & "', '" & 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
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
strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_COD
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
ASKER
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.
ASKER
would you recommend I use the strsql string or convert my code to and Update recordset Like you first suggested?
ASKER
EquipId = Text, PAmt is Number - Last serv date = date
tl-ftemview.png
tl-ftemview.png
ASKER
I am getting an error:
Error -2147217913 (Data type mismatch in criteria expression.)
got any ideas?
k
Error -2147217913 (Data type mismatch in criteria expression.)
got any ideas?
k
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_COD E"), "") & "', " & 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
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
strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_COD
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
ASKER
Still getting data type mismatch. - I even hard coded the SDD date - no luck
ASKER
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')
ASKER
just notice that some of the text contains commas - could this be cause the issue?
'BENCH,PWRD,TEST',
'BENCH,PWRD,TEST',
no because it is in quotes
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_COD E"), "") & "', " & 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')"
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
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
strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_COD
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')"
back online tomorrow, late now so signing off, e-speak tomorrow, good nite
ASKER
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
k
ASKER
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
k
did u try using to_date around it? that it the way in oracle sql to insert dates
ASKER
Yes, I did, however, it error - Access did not recognize it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_DA TE_CMT])," Hello",For mat(DateSe rial(Left( [SERVICE_D UE_DATE_CM T],2),Mid( [SERVICE_D UE_DATE_CM T],3,2),Ri ght([SERVI CE_DUE_DAT E_CMT],2)) ,"Short Date"))
Or
Test: CDate([SERVICE_DUE_DATE_CM T])
Error -2147217900 (Syntax error (missing operator) in query expression ''TO_DATE('12/1/2009','MM/
debug results: TO_DATE('12/1/2009','MM/DD
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_DA
Or
Test: CDate([SERVICE_DUE_DATE_CM
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
ASKER
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.)
Error -2147217833 (The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.)
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.
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.
ASKER
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
thanks for all your input.
Karen
ASKER
help point me into the right direction - see my last comment for my workaround.
thanks for your assistance
thanks for your assistance
jppinto
Open in new window