SQL Syntax with CurrentProject.Connection.Execute when variables are empty

[colette]

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

 

[DB7]

No, outside of using Nz()?

Maybe your data won't "allow" default values? Meaning, it's not an option?

sqlstring = "INSERT INTO SystemData (MS_Key,Period,StartTime,EndTime,ProcessType,Notes ) VALUES (" _
& Nz(nKey,1) & "," & Nz(gblcurrentperiod,"1/1/05") & "," & Nz(psdstarttime ,"#12/12/06#")& "," & psdendtime & "," & psdprocesstype & "," & psdnotes & ")"

CurrentProject.Connection.Execute sqlstring

you get the idea...

 

[WayneRyan]

Colette,

Personally, I'd use the ADO Recordset method.

Interacting with a table via SQL can be very powerful, but in a "data entry"
context, I'd stay with recordsets. The punctuation required in SQL leads
to some serious problems like:

1) Null dates: SQL hates --> ##
2) Embedded quotes: --> 'O'Henry'
3) Mixed quotes: Strings with single AND double quotes in them

You're going to write a lot of code to handle the above and similar problems.

The recordset approach needs no punctuation and the only disadvantage
that I know of is the potential for database bloat on bulk data inserts.

Wayne