VBA to Find / Replace in all fields

Question

Hello,

I'm have a MS Access 2010 database on a Win7 PC. I receive a file from a SQL database that contains 93 fields which I import to a table named "CardsImport". Whenever a field has a null value, the word NULL appears. This occurs in all the fields.

Currently I open the file in a text editor and perform a match case find NULL and replace with "".

Is there a VBA that can inspect the entire table and find / replace the word NULL with a null value?

Thanks for your help. Kevin

Answer 1

Hi Kevin,

There is but maybe it's easier to start over. What type of file did you get from SQL Server? How did you import it into Access? Are those fields supposed to have empty strings ("") instead of Nulls (not the string "NULL")?

Answer 2

Hi DBguy.

It's a comma-delimited double quote text file. Currently I'm importing it manually into Access. In the application the SQL server supports, the fields are blank that contain the word NULL in the file I receive. I'm not sure, but I think where ever no information is entered into the application, the SQL table inserts the word NULL for any empty field when exporting the file.

Here's an example of the text file I receive after importing it into Access. Hope this helps. Thanks.

Answer 3

I see. Then you don't want to use "" to replace the "NULL" values. You probably want to use Null instead.

One possible approach is you could create a form and add a multi-select listbox to show all the fields in the table. You can then select all the fields you want to update and then loop through the selected items executing the following SQL statement.

strSQL = "UPDATE TableName 
    SET [" & Me.ListboxName.ItemData(x) & "] = Null 
    WHERE [" & Me.ListboxName.ItemData(x) & "] = 'NULL'"

Hope it helps...

Answer 4

That sounds like a good idea. However, I have over 90 fields. Is it possible to add a "select all" option to the list box?

Thanks.

Answer 5

That sounds like a good idea. However, I have over 90 fields. Is it possible to add a "select all" option to the list box?

Thanks.

Of course. Loop through the Listbox using the ListCount property and set the Selected or ListIndex property to select it.

Hope it helps...

Answer 6

Thanks for your help. But I think I will just stay with opening the file in a text editor and perform a Find & Replace.

Thanks again for your suggestions.

Kevin

Answer 7

Thanks for your help. But I think I will just stay with opening the file in a text editor and perform a Find & Replace.

Thanks again for your suggestions.

Kevin

Hi Kevin,

Good luck. Just remember, there is a difference between a Null and an empty string, in case it matters later on.

Cheers!

Answer 8

Is there a VBA that can inspect the entire table and find / replace the word NULL with a null value?

Hi Kevin,

It is very easy to make your own import-routine, especially for csv-files. This gives you the possibility to manage the data in the way you want, and not what MS default does for you.

You can open the csv-file, and read the file line by line.

Each line can be split using the Split function in the individual cell values using the comma or semicolon as separator.

Then you can assign the array value to the successive fields of a recordset. This is all standard.

Your specific part: Each time the array value has a "Null" value, you can assign a NULL value to that field.

In this way you can design almost any very specific import routine.

Imb.