Which is quicker/more efficient? Lots of 'If Not IsNull()'s or setting Default Values = ""?

[ClimoC]

In my access, I've had to set a lot of fields to have default properties to avoid runtime errors (and since it's Excel based ADO objects, I have no Nz() function)

But I also have a bunch of text and memo fields, which when I just wrote a 'duplicate record' function, was returning the 'Invalid use of Null' errors.

Which would be quicker? Going into Access and setting the fields to have a default value of '"" (or even " "), or putting in a 'If Not IsNull([fieldvalue]) Then' for every use of the field in VBA?

Thanks
C

 

[xenou]

Using a default value of "" would be quicker, I imagine. Memo fields as a general rule aren't really appropriate for indexing or using to distinguish records, and will slow down your query a lot. If you have to use these to find duplicates you probably want to consider what keys you have in the table and how data is inserted. Perhaps even the same is true of the other text fields - aren't there keys in the table?

 

[SydneyGeek]

Another thing to consider with Memo fields; not only are they slow to search, but Access only indexes the first 255 characters.

Denis

 

[ClimoC]

Another thing to consider with Memo fields; not only are they slow to search, but Access only indexes the first 255 characters.

Denis

Nah I'm indexing/finding on a shared primary key for each table. I just want to duplicate the record but increment the version number. That's easy. It's the lines that say:

Newfieldval = ES.fields("memo field").value
Rs.addnew "memo field", newfieldval

Its obviously more fields than that, and the fields and values are arrayed - its that when I set the variable values, if the existing record have a default value when the record was first created, the value is null. As its only one record duplicated at a time, I only need 6 "if not isnullrs(fields("blah").value) then"'s, but I'm trying to minimise runtime wherever I can, because the dB sits on a shared drive so network speed comes into it. If I had the chance to start again, as I will learn for next time, I should/would provide default values of ""
for text fields, not just 0's for numerical ffields and 01/01/74 for date fields.

Live and learn I guess!

 

[SydneyGeek]

You can put the defaults in now if you want; all new records will acquire them.
Obviously, you'll need to log into the back end database to make the changes -- design changes to tables cannot be made from the front end db if it's split.

Denis