Put dates into Oracle table from MS Access

[greaseman]

--------------------------------------------------------------------------------

I had a project that was using SQL, and then had to modify it to put a date into a field, going from MS Access to Oracle. Here is what the code looked like:

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, Description, FSCM, "
strSQL = strSQL & "ATAPN, ESDS, SPN, "
strSQL = strSQL & "Keyword, CompVal, DimData, ModUser, ModDate) "
strSQL = strSQL & "VALUES ('" & rsTemp!PN & "', '" & strDescription & "', '" & rsTemp!Fscm & "', "
strSQL = strSQL & "'" & strATAPN & "', " & intESDS & ", '" & strSPN & "', "
strSQL = strSQL & "'" & rsTemp!Keyword & "', "
strSQL = strSQL & "'" & rsTemp!CompVal & "', '" & rsTemp!DimData & "', "
strSQL = strSQL & "'SYSTEM'" & Now()
conOracleDb.Execute strSQL, , adExecuteNoRecords

This did not place a date into the desired field. So, I deleted that piece of code and put in the following code:

strSQL = ""
strSQL = "SELECT * From MasterPN"
rsPart.Open strSQL, conOracleDb, adOpenStatic, adLockOptimistic, adCmdText
If Not (rsTemp.EOF = True And rsTemp.BOF = True) Then
rsPart.MoveLast
rsPart.AddNew
rsPart!PN = rsTemp!PN
rsPart!Description = rsTemp!Description
rsPart!Fscm = rsTemp!Fscm
rsPart!ATAPN = rsTemp!ATAPN
rsPart!ESDS = rsTemp!ESDS
rsPart!SPN = rsTemp!SPN
rsPart!Keyword = rsTemp!Keyword
rsPart!CompVal = rsTemp!CompVal
rsPart!DimData = rsTemp!DimData
rsPart!ModUser = "SYSTEM"
rsPart!ModDate = Now()
rsPart.Update
End If

And that code worked!! My question...... can anyone tell me why one way doesn't work but another way using the same Now() function does work? This one drove me nuts for the better part of a day.

Are there issues between MS Access date functions and date items in Oracle?

Any help and advice on this will be greatly appreciated. Thanks!!

 

[colette]

Now()

Now is an Access function that Oracle doesn't understand. In the INSERT statement the now is part of the statement and Oracle doesn't know how to interpret it. In the second example using a recordset, the value of now is written to the access recordset.

If you want to use the INSERT try using the TODATE function in oracle.

TO_DATE('" & now() & "', 'mm/dd/yyyy')"

If you were to use a literal date in Access like #12/1/05#, Oracle doesn't understand the #, thus using TO_DATE works.

The other thing to consider is whether Access is doing the processing or whether Oracle is. If you use a Pass-Through Query, Oracle processes it and would need the TODATE, if Access processes it, it may understand now() and #.

 

[FoFa]

Colette is correct, but you can also use the Oracle STD date format of DD-MMM-YYYY instead of todate (11-NOV-2005) if Oracle is processing the request.

 

[modest]

They're both correct, Access/JET and Oracle use very similar datatype configurations, however how the dates are inputted is a major difference between the two softwares. Normally, Oracle is not as user-friendly, BUT it is a MUCH faster database. Just be careful about how things are inputted. Additionally, you don't have a space between SYSTEM and Now(). I haven't used Oracle in a while, but is there any reason why you want these to be concatenated? Try performing the following change:

strSQL = strSQL & "'SYSTEM'" & Now()
to:
strSQL = strSQL & "'SYSTEM' " & Now()