Update query problem with nulls.

[samcoinc]

I have a access97 database that pulls data out of Navision accounting software through odbc. We are upgrading to a newer version and a newer odbc. The old odbc driver - 'blank' fields were zero length. ("") The new odbc driver - 'blank' fields are Null.

The way the system is setup - is it runs 3 querys.
Add - this adds only records that don't exist in the access table vs Navision.
Delete - this deletes only the records in access that don't exist in Navision
Update - only updates the records that have any change in them

The update query is the issue. Now that the blank fields are null - if the field starts out as null in navision and then gets populated - these fields don't get updated in access. The criteria isn't true. (if that makes sense)

See the screen shot of the update query. Mainso is the access table and 'start' is the table from navison (actually a query combining 2 tables from nav) Is there an more elegent way do do this? The way it runs now works great because you are only adding,deleting and updating the records that are different from navision. (instead of say - deleting all the records and re-populating the access table - which takes a lot longer and exposes a blank table for a period of time during the re-population)

Thanks
sam

 

[namliam]

The update query is the issue. Now that the blank fields are null - if the field starts out as null in navision and then gets populated - these fields don't get updated in access. The criteria isn't true. (if that makes sense)

Yes that makes perfect sence

You can resolve this by using the NZ function in your where clause(s) to substitute the Null for a empty string "" which will then trigger the change where the Null didnt

 

[samcoinc]

Cool. Thanks

So - are you saying that when I insert the record (add query) - I should at that time convert all nulls to zero length strings using NZ.. Then during the update query - also use the NZ fuction in the criteria? (and it would have to be used in the 'update to' line also so the updated data was "" instead of null.

forgive my slowness.

thanks again
sam

 

[samcoinc]

BTW - is there a better/faster way to compare 2 tables for changes (and update only the records that have changes)

thanks
sam

 

[namliam]

Cool. Thanks

So - are you saying that when I insert the record (add query) - I should at that time convert all nulls to zero length strings using NZ.. Then during the update query - also use the NZ fuction in the criteria? (and it would have to be used in the 'update to' line also so the updated data was "" instead of null.

forgive my slowness.

thanks again
sam

That depends on how you want to solve it, this is one way...
Another is to substitute only the in the Where clause...
ie <> NZ([START].[SHIPMENT DATE, "")

If you have a number field though, you cannot fill it with "", you will need 0
NZ(yourfield,0)

And for a date you will probably want a default date like #12/31/1900# or something.

Unless there is someway of identifying changed records in the database, no...
Alternative might be to fetch the Navision table (s) first into access completely then do a compare only inside access, should speed you total process up considereably.

 

[samcoinc]

Thanks again!

The more I think about it - the more I probably want to convert the data initally. There are a lot of programs down stream that would have to be modified. (I want to try to make this as painless as possible )

it is funny what you say about the date field. The old navision odbc driver populated blank fields with 1/1/1756 or something close to that. I did do a one of the smaller query sets using nz and it looks like it should work.

Thanks
sam

That depends on how you want to solve it, this is one way...
Another is to substitute only the in the Where clause...
ie <> NZ([START].[SHIPMENT DATE, "")

If you have a number field though, you cannot fill it with "", you will need 0
NZ(yourfield,0)

And for a date you will probably want a default date like #12/31/1900# or something.

Unless there is someway of identifying changed records in the database, no...
Alternative might be to fetch the Navision table (s) first into access completely then do a compare only inside access, should speed you total process up considereably.

 

[samcoinc]

Thanks again - we are running with the new odbc now for a few weeks. Everything is working great (I am using the NZ solution)

With the new database and new server - it still runs a bit faster than the old system. (we run 50+ queries every 5 minutes or so adding, deleting and updating a good 2m records in about 10 different tables.

sam