Help! ADO and editing

[Guest]

I am very new to programing (learning as i need it). Basically I want to make
sure that all fields have a non-null value. The code I have written below
works fine for the table I am generating the record set from but I have 15
other tables I want to do the same thing with. My questions are:

1. Is there a simple way to loop through all tables in the current project
instead of having to generate a recordset for each table, one at a time?
2. I am thinking of splitting the database. I assume that the connection
string needs to be modified. How is this done?
3. Is there a way to use a zero length string with numbers instead of having
to put a 0 or dummy date so that the field is not null, as i did below?

Many thanks!

Dim cnn As New adodb.Connection
Dim rst As New adodb.Recordset
Dim fld As adodb.Field

Set cnn = CurrentProject.Connection

rst.open " select * from health_patient", cnn, adOpenStatic, adLockPessimistic

With rst

Do Until .EOF

'check each field in the table and, depending on data type, if null change
to a value
For Each fld In .Fields
If fld.Type = 203 Or fld.Type = 202 Then
If IsNull(fld.Value) Then
fld.Value = "No data"
End If

ElseIf fld.Type = 3 Then
If IsNull(fld.Value) Then
fld.Value = 0
End If

ElseIf fld.Type = 7 Then
If IsNull(fld.Value) Then
fld.Value = #1/1/1111#
End If

End If
Next

.MoveNext
Loop
End With
Set cnn = Nothing
Set rst = Nothing

 

[Douglas J Steele]

I don't understand why you're doing what you are are in the recordset. It
would be far simpler to create queries that use the Nz function to set the
null values as you're doing, then use the queries rather than the tables.

Even if you want the changes to be made permanently, using Update queries is
going to be far more efficient than using recordsets and looking at each
value in each field programmatically.

For your last question, the answer's no, numeric and date fields cannot
contain ZLS.

Why can't you work with Null values anyhow?

 

[Guest]

Thanks, I sort of thought this from a response from someone else but I am
just learning so I wanted to do a little with ADO.

To answer your other question about nulls. I have a report that uses a
parameter query as a record source and draws the parameter values from a LOT
of combo boxes. If any records are null in fields that I have set a parameter
"criteria" then that record will not be returned by the query.

I think I see what you are getting at. I will give it a try.

many thanks!

P.S- I looked to see who you were and saw your message. I also did a little
wine making. Really don't have the time for it anymore. I have a yeast (I
think, i still have some of the brew) that would not grow on artificial media
such as nutrient agar but grew well in certain brews (could see active
budding etc. under microscope) thus i am assuming that it used the
metabolites of other organisms as an energy source. Interestingly it gave a
brew with tast very like sherry without all the trouble. I use to use it in
cooking. This (the use of consortia or non-typical yeasts) was something that
i never saw mention with brewers but, for me, a fun topic.

thanks again!