Help deleting fields in SQL table

[JEErasmus]

Bear with me here, I'm just learning to use SQL for the first time.
I have a spreadsheet that pulls in the data from a .dbf database file.
In the spreadsheet, users can rearrange these and export them back to
the database to be picked up by the application which uses them.

Initially I tried creating a new sheet with the updated values mixed
in with the original values (some of the original rows of data are
deleted), deleting everything in the table and then refilling the
table with the data from the new sheet. BUT one of the fields is a
memo which doesn't translate well to Excel and back again using VBA.
AND it's a stupid way of doing things.

UPDATING. Now I have used and UPDATE statement to update the fields
that already exist in the file.

DELETING FIELDS REMOVED IN EXCEL. I thought I might try deleting
excess records in the dbf file but by doing that while looping through
the (joint) primary keys in VBA, I'll end up deleting everything.
I could create a table listing just the primary keys I want to end up
with, and then link the two tables using some kind of "DELETE FROM
dbftable WHERE Not Exists (SELECT key FROM table2 WHERE
table1.key=table2.key)". The only problem is I don't know how to make
a table from my spreadsheet in VBA/SQL.

ADDING NEW FIELDS. And here I just have no idea what to do. When users
edit the information they want to, they play about with less than 10
fields (including the primary keys) in one sheet, and not the full 71
fields in the original file. I guess I could assign default values,
but not sure how I'd go about this without it becoming cumbersome and
sluggish.

Any help appreciated.
PS It's my birthday, please be nice, I'm getting old here.

 

[Guest]

Just a few general comments here without getting specific.

First, you don't have to pull all fields from any given table in the
database - your SELECT query can be written to pull only the fields you need
to work with into Excel. By the same logic, when you do an UPDATE, you do
not have to even update all the fields you have available or retrieved with
the original SELECT query; just update the ones changed using the keyfield
value as the WHERE portion of the sql.

As far as adding new fields (i.e. columns) to the database table, you
shouldn't have to do that - perhaps you meant adding new records (rows)? In
that case you'll either have provide default values for fields that the
database doesn't automatically provide defaults for and/or does not accept
null entries. If you're going to have to provide default values I wouldn't
even add any logic to figure out which ones: I'd hard code something to
provide default value to all values for each field and then overwrite the
individual fields you do have data for with that actual data and then add the
record to the database.

My opinion is that it's pretty much bad form to try to manage the database
structures (redefining tables, entity relationships, etc) from Excel - that
should be done by the database administrator through the tools of their
choice. Of course, if you're the DBA and Excel is your tool choice then I
should hush up.

P.S. Happy Birthday!

 

[Erasmus]

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(Array( _
"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

 

[Erasmus]

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.

 

[Guest]

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/forums/dw_thread.jsp?forum=292&thread=161629&cat=5