S
salmonella
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
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