Sorry, of course I meant records, the heat has been messing with my
head.

I've got the SQL to add a record with the correct combination of the
joint primary keys. But when I try to update the new record with my
array of default values (and after that specific values for the new
record at the same time other records are updated), nothing is updated
- I end up with a blank row except for the keys.
Also, I haven't mastered the art of deleting records yet either.
I'm sure I'm doing something stupid, maybe you can point it out?

Excerpt of my code (partially fudged from somewhere else) - THIS GETS
MESSY WITH THE ARRAY... the update assumption for existing records is
working fine, but the same statement doesn't work on the new record I
inserted:

With Sheet1
'Set default values for inserting new records
For J = Constants.startRow + 1 To 1000 'loop through each row
If (.Cells(J, 2) = "") Then Exit For 'exit at first blank
row
If (.Cells(J, 2) = 38 And .Cells(J, 3) = 2) Then
For K = 1 To 71
VariableArray(K) = .Cells(J, K + 1)
Next K
End If
Next J
End With
With Sheet11
'Set date and time stamps
DStamp = FormatDateTime(.Cells(6, 7), vbShortDate)
tstamp = FormatDateTime(.Cells(7, 7), vbLongTime)
For J = Constants.startRow + 1 To 1000 'loop through each row
NewInd = True
If (.Cells(J, 2) = "") Then Exit For 'exit at first blank
row
counter = counter + 1
batchno = Cells(J, 2)
Jobno = Cells(J, 3)
JobDesc = Cells(J, 4)
Wildcrdset = Cells(J, 5)
Prd_from = Cells(J, 6)
Prd_to = Cells(J, 7)
Inputfile = Cells(J, 8)
Cashfile = Cells(J, 9)
Indvfile = Cells(J, 10)
Odometer = Cells(J, 11)
Mininforce = Cells(J, 12)

Application.StatusBar = "Processing... " & JobDesc
'Add new fields
For K = Constants.startRow + 1 To 1000
If (Sheet1.Cells(K, 2) = batchno And Sheet1.Cells(K,
3) = Jobno) Then
NewInd = False
End If
Next K
If NewInd = True Then
sql_string = "INSERT INTO prjbat(batchno,jobno) VALUES
(" & VariableArray(1) & "," & Jobno & ")"
Call Utilities.executeSQL(sql_string)
Call Utilities.deleteExternalDataRanges
sql_string = "UPDATE prjbat SET jobdesc = '" &
VariableArray(3) & "', prodlist = '" & VariableArray(4) & "', purpose
= '" & VariableArray(5) & "', prd_from = " & VariableArray(6) & ",
prd_to = " & VariableArray(7) & ", cashflow = '" & VariableArray(8) &
"', parmset = '" & VariableArray(9) & "', inputfile = '" &
VariableArray(10) & " "
sql_string = sql_string & "WHERE (batchno = " &
batchno & " AND jobno = " & Jobno & ")"
Call Utilities.executeSQL(sql_string)
Call Utilities.deleteExternalDataRanges
sql_string = "UPDATE prjbat SET cashfile = '" &
VariableArray(11) & "', indvfile = '" & VariableArray(12) & "',
incl_var = '" & VariableArray(13) & "', inf_flag = '" &
VariableArray(14) & "', cash_flag = '" & VariableArray(15) & "',
indv_flag = '" & VariableArray(16) & "', trace_flag = '" &
VariableArray(17) & "', log_flag = '" & VariableArray(18) & "', "
sql_string = sql_string & "cprd_to = '" &
VariableArray(19) & "', layer_to = '" & VariableArray(20) & "',
discprdtyp = '" & VariableArray(21) & "', discprd = '" &
VariableArray(22) & "', discrates = '" & VariableArray(23) & "',
odometer = '" & VariableArray(24) & "', dumpfile = '" &
VariableArray(25) & "', dump_flag = '" & VariableArray(26) & "', "
sql_string = sql_string & "var_type = '" &
VariableArray(27) & "', varnum = '" & VariableArray(28) & "',
disptmptbl = '" & VariableArray(29) & "', dstamp = #" &
VariableArray(30) & "#, tstamp = '" & VariableArray(31) & "', ustamp =
'" & VariableArray(32) & "', layeroffs = '" & VariableArray(33) & "',
wrtsubmdl = '" & VariableArray(34) & "', "
sql_string = sql_string & "rebase_flg = '" &
VariableArray(35) & "', scenario = '" & VariableArray(36) & "',
restvar = '" & VariableArray(37) & "', modelcap = '" &
VariableArray(38) & "', rebaseind = '" & VariableArray(39) & "', tlow
= '" & VariableArray(40) & "', uniqueid = '" & VariableArray(41) & "',
intermfile = '" & VariableArray(42) & "', "
sql_string = sql_string & "autointerm = '" &
VariableArray(43) & "', periodrun = '" & VariableArray(44) & "',
cprd_from = '" & VariableArray(45) & "', mininforce = '" &
VariableArray(46) & "', irr_flag = '" & VariableArray(47) & "',
groupextra = '" & VariableArray(48) & "', slw_flag = '" &
VariableArray(49) & "', stripspc = '" & VariableArray(50) & "', "
sql_string = sql_string & "dump_type = '" &
VariableArray(51) & "', wrtsubmdl2 = " & VariableArray(52) & ", iterno
= " & VariableArray(53) & ", iter_from = '" & VariableArray(54) & "',
dbfformat = '" & VariableArray(55) & "', retainfile = '" &
VariableArray(56) & "', wildcrdset = '" & VariableArray(57) & "',
mdlprop = '', "
sql_string = sql_string & "cellsetid = " &
VariableArray(59) & ", rerungroup = '" & VariableArray(60) & "',
workertime = " & VariableArray(61) & ", delworkout = '" &
VariableArray(62) & "', "
sql_string = sql_string & "goalseek = '', gsflag = '"
& VariableArray(64) & "', gsupdval = '" & VariableArray(65) & "',
profiler = '" & VariableArray(66) & "', "
sql_string = sql_string & "statfile = '" &
VariableArray(67) & "', statf_flag = '" & VariableArray(68) & "',
prdparmset = '" & VariableArray(69) & "', groupiter = '" &
VariableArray(70) & "', mastertime = " & VariableArray(71) & " "
sql_string = sql_string & "WHERE (batchno = " &
batchno & " AND jobno = " & Jobno & ")"
Call Utilities.executeSQL(sql_string)
Call Utilities.deleteExternalDataRanges
End If
'Update changes to already existing fields (plus date and
time stamps).
sql_string = "UPDATE prjbat SET jobdesc = '" & JobDesc &
"', prd_from = '" & Prd_from & "', prd_to = '" & Prd_to & _
"', inputfile = '" & Inputfile & "', cashfile = '" &
Cashfile & "', indvfile = '" & Indvfile & _
"', odometer = '" & Odometer & "', mininforce = '" &
Mininforce & "', wildcrdset = '" & Wildcrdset & _
"', dstamp = #" & DStamp & "#, tstamp = #" & tstamp & _
"# WHERE (batchno = " & batchno & " AND jobno = " & Jobno
& ") OR jobdesc IS NULL"
Call Utilities.executeSQL(sql_string)
Call Utilities.deleteExternalDataRanges
Next J


As you can see, I've been moving bits and pieces about trying to see
if individual statements work. Here is the executeSQL function I keep
referencing - I haven't touched this from what I was given in case I
mess it up!

Sub executeSQL(ByVal sql_string As String)
Dim model_name As String
model_name = Range("model_name")
On Error Resume Next
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" & model_name
& ";SourceType=DBF;Exclusive=No;Background" _
), Array("Fetch=Yes;Collate=Machine;Null=Yes;Deleted= Yes;")),
Destination:=Cells(Constants.startRow, 2))
.Sql = sql_string
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub

I've been playing about some more - it looks like the SQL string is
too long - if I do it in chunks it works. Also, some of my syntax
wasn't quite right - I've changed it to allow for no single quotes
around numeric and boolean fields.

Now I have a date field (and a time field coming up) and I have no
idea what syntax to use there (am looking around but have no help
installed on work pc). I have tried # and both with and without single
quotes.

I'm not sure what a date (or time) entry into a .dbf file is supposed to look
like either. I did a quick Google search for some info and found where
someone had asked the question elsewhere - perhaps reading through this
discussion will reveal the answer
http://www.ibm.com/developerworks/fo...d=161629&cat=5


"Erasmus" wrote:

I've been playing about some more - it looks like the SQL string is
too long - if I do it in chunks it works. Also, some of my syntax
wasn't quite right - I've changed it to allow for no single quotes
around numeric and boolean fields.

Now I have a date field (and a time field coming up) and I have no
idea what syntax to use there (am looking around but have no help
installed on work pc). I have tried # and both with and without single
quotes.