Importing and updating Tables?

[rlp89]

I am trying to import a table from another database and then update a table in the current database. To do this I am using the transfer database function in a macro into a new table. I am then using an update query to update the records from the imported table to the local table. The problem is, when there is a field in the imported table that contains a null value, I don't want to update that field. It is not feasible to make all fields have a required entry, by setting the field properties. I tried to use a iif statement but all I can do is input a space if it is null and that erases the current data in that field. This is what I want to avoid. Sorry this is so long winded. Thanks in advance for any help.

 

[Transcend]

Hi there,

what does your update query look like? You could edit the update query to only extract fields where the value is not null (with a sub select).

Transcend

 

[rlp89]

Not sure how to do sub select?

Example Update Query:

Table 1: Right Joined to - Table 2:
ENLPERS1 ENLPERS

Field: Status
Table: ENLPERS
Update To: iif(IsNull[ENLPERS1].[Status]),"(I would like to skip here)","[ENLPERS1].[Status]&quot
Criteria:
Or:

 

[PSUIVERSON]

SOME VBA in a module if you like - I know you are trying to do it a query but I just did the same thing this morning so I thought I'd give you a head's up...

Public Sub tableNameXNULLS()
Dim dbs As Database
Dim rst As Recordset

Dim i As Single

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tableName", dbOpenDynaset)

rst.MoveFirst

Do While Not rst.EOF
For i = 0 To rst.Fields.count - 1
If IsNull(rst.Fields(i)) Then
rst.Edit
rst.Fields(i) = 0
rst.Update
End If
Next i
rst.MoveNext
Loop

Set rst = Nothing
dbs.Close

End Sub

 

[rlp89]

Oaky. The VBA looks good, however not sure if it is practical for my application. It looks to me like your code cycles through each field in a recordset, and if it has a null value, it places a zero in that field. What I want to do, is if it does not contain a null value, then I want it to update another table that does not have the same field names. It is not feasible for me to go through and change all of the field names so that they match. Thanks for the help. If you have any other ideals, or if I am wrong or missing something, keep it coming.