Add column to table and fill with data

[jprockbelly]

Hi all,

Bare with me I'm quite new to code in Access.

I have a table, "h_al_871_val", which contains a field, "deep_drain". This field contains doubles ranging from about 12 to -9. I would like some code which adds a field to the table and fills it with values. The values are the same as those in "deep_drain" except all negative numbers are replaced by a 0.

Any help is appreciated.
thanks
JP

 

[rb2006]

Hope this helps - this adds new field to tblData and updates the new field

It only updates those matching with the old field where the old field is more than zero
Any others become zero

Public Sub addfields() 'create new fields in table "tblData"
Dim MyDb As Database, mytbldef As TableDef, myf As Field
Dim myrs As Recordset


Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set mytbldef = MyDb!tblData


Set myf = mytbldef.CreateField
With myf
.Name = "deep_drain2"
.Type = 7
.DefaultValue = 0
End With
mytbldef.Fields.Append myf
Next

Set myf = Nothing
Set mytbldef = Nothing

Set myrs = myDb.OpenRecordset("tblData", DB_OPEN_DYNASET)
With myrs
.MoveFirst
Do until .EOF

.Edit
If !deep_drain > 0 Then
!deep_drain2 = !deep_drain
Else: !deep_drain = 0
End if

.Update

.MoveNext
Loop

.Close
End With


MyDb.Close

End Sub

Note: Types are:
Type: 1 = y/n, 2 = byte, 3 = integer, 4 = long, 5 = curr, 6 = single,
7 = double 8 = date, 9 = binary, 10 = text (set .default value as req), 11 = ole, 12 = memo

not sure but think 17 is an autonumber

 

[jprockbelly]

rb2006, thanks for the help. This should set me on the path

 

[boblarson]

rb2006, thanks for the help. This should set me on the path

It is only the right path if you are deleting the other column from which this is getting the values. If you are not, then you should just be displaying the values in a QUERY and not updating the table.

 

[rb2006]

Im currently lacking in some sleep at moment so forgot new fiel
would NOT be populated with zeros so have amended code above

good idea about using query instead; you could use an IIF() function
in a new column to filter it

eg newcol: IIF([deepdrain]>=0,[deepdrain],0)

so if deepdrain more than or equal to zero the query column displays
value, otherwise it displays a zero

 

[jprockbelly]

Thanks for the replies guys.

1.
So this works great for for a small sample of the data (thanks for the tip):

!deep_drain_filt = IIf(!deep_drain > 0, !deep_drain, 0)

Yet when I try to run it with the entire table I get an error. This seems to be because the table is very large >2 million records.

I have added this line on the advice of MS help

DBEngine.SetOption dbLocksPerFile, 2126018

but still get an error after about the 1200th record.

2.
Yes, I now see that a query would be a better way to go Is it much harder to code a query as the output?

 

[boblarson]

Your problem is almost assuredly a NULL value.

A query to look at it would simply be

DeepDrain:IIf(Nz([deep_drain],0)> 0,[deep_drain],0)

 

[jprockbelly]

DeepDrain:IIf(Nz([deep_drain],0)> 0,[deep_drain],0)

You will have to n00b this up for me. Is this implemented via VBA? How do I execute this code?

Sorry, I'm quite new to Access

 

[boblarson]

Let's try this again:

A query to look at it would simply be

DeepDrain:IIf(Nz([deep_drain],0)> 0,[deep_drain],0)


So, when you go into a query and have your table selected, in the QBE (query by example) grid you would create a field of your own by using what I showed.

NOT VBA. In fact, I have totally suggested NOT using VBA at all but only use a QUERY to DISPLAY the values as shown.

 

[jprockbelly]

Thank you, now I understand.

To look for null values I have used the query

 ErrChk: Nz([deep_drain], 9999)

However, this query does not return any null values in the field deep_drain.

 

[boblarson]

So, is deep_drain a numeric field?

 

[boblarson]

Also, when you ran that query did anything come back as 9999?

 

[jprockbelly]

Yes, deep_drain is a numeric field, doubles, ranging mostly from roughly 12 to -9. Hence 9999 as a flag.

No, this query did not return any 9999 values.

 

[boblarson]

How about just checking by putting

Is Null

in the criteria of the field and see what comes up.

 

[jprockbelly]

Is Null

Still no null values found.

I should also elaborate on the error generated by the above code. When I do get an error it is either, 3043 'Disk or network Error', 3218 'Could not update', or 3024 'Cannot find file'. This also happen after an arbitary number of records have been generated, but always occurs at the line

.Edit

This is why I speculated that the size of the database may be an issue. On the last attemp it took just under 6 min for the above code to run, then it produces an error, after createing 9,625 records (out of 2,126,017).

Apologies if the previous post was confusing.