Here is something I have been noticing and I thought someone might be able to shed some light on this and maybe offer a cleaner solution. I working with a front-end db in Access and linking to Oracle tables.
When writing SQL Insert and Update statements using dates and strings, if the variables are empty, Access errors because it won't accept ## or '' with nothing inside. It also insists on using those and not cdate() or cstr(). If I were doing pass-throughs, I could use oracle's TO_DATE(), but I am not using them here.
In an attempt to clean up the use of recordsets, I tried to write a sql statement instead. The recordset version doesn't care whether a variable has a value or not. It was like this...
Dim rst As ADODB.Recordset
Dim sqlString As String, tableName As String
sqlString = "SELECT * From SystemData;"
Set rst = New ADODB.Recordset
rst.Open sqlString, gblCNN, adOpenKeyset, adLockPessimistic
rst.AddNew
rst("MS_Key") = Oracle_GetNextKeyVal("SystemData")
rst("Period") = gblCurrentPeriod
rst("StartTime") = psdStartTime
rst("EndTime") = psdEndTime
rst("ProcessType") = psdProcessType
rst("Notes") = psdNotes
----
In attempting a rewrite, I am forced to do all the checks below and append the string together. i would have like to have one statement that didn't care whether the values are there are not. I could try doing IIF() for each, but that doesn't seem all that clean either.
Dim sqlstring As String
Dim nKey As Long
nKey = Oracle_GetNextKeyVal("systemdata")
sqlstring = "INSERT INTO SystemData (MS_Key,Period,StartTime,EndTime,ProcessType,Notes) VALUES (" & nKey _
If IsNull(gblcurrentperiod) Then
sqlstring = sqlstring & ",gblcurrentperiod"
Else
sqlstring = sqlstring & ",#" & gblcurrentperiod & "#"
End If
If IsNull(psdstarttime) Then
sqlstring = sqlstring & ",psdstarttime"
Else
sqlstring = sqlstring & ",#" & psdstarttime & "#"
End If
If IsNull(psdendtime) Then
sqlstring = sqlstring & ",psdendtime"
Else
sqlstring = sqlstring & ",#" & psdendtime & "#"
End If
If IsNull(psdprocesstype) Then
sqlstring = sqlstring & ",psdprocesstype"
Else
sqlstring = sqlstring & ",'" & psdprocesstype & "'"
End If
If IsNull(psdnotes) Then
sqlstring = sqlstring & ",null"
Else
sqlstring = sqlstring & ",'" & psdnotes & "')"
End If
sqlstring = "INSERT INTO SystemData (MS_Key,Period,StartTime,EndTime,ProcessType,Notes) VALUES (" _
& nKey & "," & gblcurrentperiod & "," & psdstarttime & "," & psdendtime & "," & psdprocesstype & "," & psdnotes & ")"
CurrentProject.Connection.Execute sqlstring
Any thoughts?
Thank you,
Colette
When writing SQL Insert and Update statements using dates and strings, if the variables are empty, Access errors because it won't accept ## or '' with nothing inside. It also insists on using those and not cdate() or cstr(). If I were doing pass-throughs, I could use oracle's TO_DATE(), but I am not using them here.
In an attempt to clean up the use of recordsets, I tried to write a sql statement instead. The recordset version doesn't care whether a variable has a value or not. It was like this...
Dim rst As ADODB.Recordset
Dim sqlString As String, tableName As String
sqlString = "SELECT * From SystemData;"
Set rst = New ADODB.Recordset
rst.Open sqlString, gblCNN, adOpenKeyset, adLockPessimistic
rst.AddNew
rst("MS_Key") = Oracle_GetNextKeyVal("SystemData")
rst("Period") = gblCurrentPeriod
rst("StartTime") = psdStartTime
rst("EndTime") = psdEndTime
rst("ProcessType") = psdProcessType
rst("Notes") = psdNotes
----
In attempting a rewrite, I am forced to do all the checks below and append the string together. i would have like to have one statement that didn't care whether the values are there are not. I could try doing IIF() for each, but that doesn't seem all that clean either.
Dim sqlstring As String
Dim nKey As Long
nKey = Oracle_GetNextKeyVal("systemdata")
sqlstring = "INSERT INTO SystemData (MS_Key,Period,StartTime,EndTime,ProcessType,Notes) VALUES (" & nKey _
If IsNull(gblcurrentperiod) Then
sqlstring = sqlstring & ",gblcurrentperiod"
Else
sqlstring = sqlstring & ",#" & gblcurrentperiod & "#"
End If
If IsNull(psdstarttime) Then
sqlstring = sqlstring & ",psdstarttime"
Else
sqlstring = sqlstring & ",#" & psdstarttime & "#"
End If
If IsNull(psdendtime) Then
sqlstring = sqlstring & ",psdendtime"
Else
sqlstring = sqlstring & ",#" & psdendtime & "#"
End If
If IsNull(psdprocesstype) Then
sqlstring = sqlstring & ",psdprocesstype"
Else
sqlstring = sqlstring & ",'" & psdprocesstype & "'"
End If
If IsNull(psdnotes) Then
sqlstring = sqlstring & ",null"
Else
sqlstring = sqlstring & ",'" & psdnotes & "')"
End If
sqlstring = "INSERT INTO SystemData (MS_Key,Period,StartTime,EndTime,ProcessType,Notes) VALUES (" _
& nKey & "," & gblcurrentperiod & "," & psdstarttime & "," & psdendtime & "," & psdprocesstype & "," & psdnotes & ")"
CurrentProject.Connection.Execute sqlstring
Any thoughts?
Thank you,
Colette