1 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Empty cells and null values bugging export

Trying to export any data to zipped access throws up an error sugggesting that the process is encountering empty cells.

Im doing what I can by checking the tables in turn and cobnverting any truly empty cells to NULL, but this is long winded.

Can I search for empty cells on the database globally? (search and replace?)

Can I ascertain in any other way where the process is encountering the trouble?

(Ive been watching the progress of the tables at the bottom of the screen - but in all honesty it doesnt seem to tally with the null values I have found).
Are null values the default for all cells in a table in the absence of other information?

Are their any other clues in the error dialogue displayed that I am not noticing?

As ever.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

2 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Empty cells and null values bugging export

Hi Matt,

Can you post your error message please - there may be some clues in it.

Thanks,
Lynn

3 Reply by Matt_tullie (edited by Matt_tullie 16-12-2008 10:00:41)

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Empty cells and null values bugging export

As below;


date/time         : 2008-12-16, 09:58:58, 663ms
computer name     : PC3041
user name         : matthewg
operating system  : Windows XP Service Pack 2 build 2600
system language   : English
system up time    : 1 hour 6 minutes 
program up time   : 40 minutes 49 seconds 
processors        : 4x Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
physical memory   : 1457/2012 MB (free/total)
free disk space   : (C:) 214.86 GB 
display mode      : 1680x1050, 32 bit
process id        : $8fc
allocated memory  : 41.64 MB
command line      : RecorderApp.exe /197262
executable        : RecorderApp.exe
exec. date/time   : 2008-11-04 08:45
version           : 6.13.3.182
madExcept version : 2.7h
exception class   : EOleException
exception message : Cannot use empty object or column names. Use a single space if necessary.

main thread ($d78):
00000000 ???                              
00450bc1 RecorderApp.exe Classes          CheckSynchronize
004d484e RecorderApp.exe Forms            TApplication.WndProc
004528d8 RecorderApp.exe Classes          StdWndProc
77d4bcc7 user32.dll                       DispatchMessageA
004d4de3 RecorderApp.exe Forms            TApplication.ProcessMessage
004d4e1a RecorderApp.exe Forms            TApplication.HandleMessage
004d1989 RecorderApp.exe Forms            TCustomForm.ShowModal
009155b5 RecorderApp.exe FormActions 1077 TdmFormActions.actExportExecute
00451c23 RecorderApp.exe Classes          TBasicAction.Execute
004bf301 RecorderApp.exe ActnList         TContainedAction.Execute
004bffa3 RecorderApp.exe ActnList         TCustomAction.Execute
00451af7 RecorderApp.exe Classes          TBasicActionLink.Execute
004c5a7f RecorderApp.exe Menus            TMenuItem.Click
004c6d33 RecorderApp.exe Menus            TMenu.DispatchCommand
004c7aba RecorderApp.exe Menus            TPopupList.WndProc
004c7a09 RecorderApp.exe Menus            TPopupList.MainWndProc
004528d8 RecorderApp.exe Classes          StdWndProc
77d4bcc7 user32.dll                       DispatchMessageA
004d4de3 RecorderApp.exe Forms            TApplication.ProcessMessage
004d4e1a RecorderApp.exe Forms            TApplication.HandleMessage
004d503a RecorderApp.exe Forms            TApplication.Run
0099f832 RecorderApp.exe RecorderApp  317 initialization

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

4 Reply by SimonWood

  • SimonWood
  • Veteran
  • Offline
  • Registered: 09-09-2008
  • Posts: 63

Re: Empty cells and null values bugging export

Hi Matt

In addition tro the error message can you let us know which table and field you are setting the null values into, as it might be an error that we could fix generically. Null is the normal method of setting a missing value, whereas a value of “” normally means a value has been supplied, but it was empty. For the purposes of Recorder we’d normally set a null value though as this distinction isn’t really defined.

If you have access to a tool that can run an SQL query (e.g. via the linked access MDB file, Query Analyser or SQL Server Management Studio Express), you can run a query like the following to change empty values to null:

UPDATE table SET field=NULL WHERE field=’’

Make sure you replace table and field with the table name and field name.

Thanks,

Simon Wood
[url=http://www.dorsetsoftware.com/]Dorset Software Services[/url]

SimonWood's Website

5 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Empty cells and null values bugging export

The problem would be not knowing where the empty value problem is.

Can I run the query against the entire database or an entire table?

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

6 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Empty cells and null values bugging export

Thanks Matt (and Simon),
To the untrained eye have you checked that there are no strange symbols in the column names (the headings) as the message is saying cannot use empty cells OR column names. Also there is a hint about single spaces - perhaps you have some strange formatting which is not immediately obvious that is causing your headache - a doublespace perhaps?

I'm sure Simon will be able to help on the more technical side of things.
Lynn

7 Reply by Matt_tullie (edited by Matt_tullie 17-12-2008 13:07:53)

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Empty cells and null values bugging export

Ive been through many of the tables trying to remove empty cells - replacing with Null.
To no avail.

However, I had an empty survey - this allowed itself to be exported.
I added an observation with no Location just a location name - this allowed itself to be exported.
I added an observation with a location - the export crashed again.


I think that narrows it down.

M

Update - next day.

Im scuppered.
I can narrow it down to the LOCATION, L_Des, L_Name, L_Type tables. But there is nothing in there that would relate directly to the  

exception class   : EOleException
exception message : Cannot use empty object or column names. Use a single space if necessary.

message. And even then I cant be sure.

Simon /Dorset - is there a fixed pattern for building an export - i.e. the list of tables and the order they are scanned? Is the Export module within the system tables somewhere?

I dont have access to a profiler to log the transaction so cant dig any further.

Dont know, need help.

Insert despairing emoticon here.

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

8 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Empty cells and null values bugging export

Just for completeness.

This was solved by rebuilding the database.

The cause of the corruption... Still in question, and may never be answered.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website