1 Topic by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Topic: Skip Surveys from import

Hello everyone,

I have just received for import a NBN Access Database with a polygon filtered subset of data from 106 surveys.  It turns out that we already have several of these surveys, and in the past some of the data has been exported/imported through Excel so I expect many of the records will have different unique keys - hence an import will cause duplication.

I was thinking that a quick and simple solution would be to just delete the duplicated surveys from the Survey table in the Access database.  Then they would be skipped by the import process.  Or would the import collapse in a gibbering heap?

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Skip Surveys from import

Might well end up in a heap, but worth a try, but if it does work, remember that Locations/Names etc. will still import.  Other option might be to set up a 'Dump' survey in your system and change the Survey keys in Surveys Event in the import file  to point to this, where you don't want the data. The only place the Survey key will be is in this table, so it is safe to change. You  could use an Access query linking Survey and Survey event to make the change.   You can then delete the Survey after import. Overall a bit more work, but more likely to work

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

Thanks Mike.  If I change the Survey Keys in Survey Event should I also add the 'Dump' survey to the Survey table?

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

4 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

To answer my own question: the relational integrity checks in Access are requiring a record in the Survey table.  Can I just copy one of the other Survey rows and change the key and name?  Or do the other fields have to match what is in Recorder too?

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

5 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

Solved my own problem (I hope).  Created a record in the 'Dump' survey in Recorder, then ran an export of that survey to copy over the row from the Survey table.

Also found out that running an export via our Citrix box causes a crash where the Recorder window disappears without trace.  But that is a challenge for another time maybe.  I'll be sure to import from the server.

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

6 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Skip Surveys from import

Mike, this may be a similar issue that I often have with terminal services. If a message box pops up it can appear 'behind' the Recorder window. You can't find it to click OK, and Recorder is hanging until it gets the OK. On exports it is probably the "There are invalid records in your export..." type box for me. As you say, not a problem if you are in the server.

Gordon Barker
Biological Survey Data Manager
National Trust

7 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

Thanks again Gordon.  You describe something that I have seen on occasion, however in the case above there are no windows left open for things to hide behind, not even the main Recorder window.  The application entirely disappears; I do not have access to task manager to confirm that but Recorder does not complain that it is already running when I open it again.

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

8 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

Well, the 'Dump' survey has worked, but only up to a point.  When I click the 'Dump' expand icon in the Surveys list the system crashes.  Here is the bug report:

date/time         : 2013-02-12, 14:33:17, 609ms
computer name     : NTSCTX03
wts client name   : NTS005914
user name         : mbeard
operating system  : Windows 2003 Service Pack 2 build 3790
system language   : English
system up time    : 245 days 6 hours
program up time   : 12 minutes 15 seconds
processors        : 2x Intel(R) Xeon(TM) CPU 3.20GHz
physical memory   : 941/2047 MB (free/total)
free disk space   : (C:) 7.87 GB
display mode      : 1280x968, 8 bit
process id        : $2098
allocated memory  : 20.81 MB
command line      : RecorderApp.exe /131370
executable        : RecorderApp.exe
exec. date/time   : 2011-01-18 13:50
version           : 6.16.2.241
madExcept version : 2.7k
exception class   : EVariantTypeCastError
exception message : Could not convert variant of type (Null) into type (String).

main thread ($2f90):
00438daf RecorderApp.exe Variants                   VarCastError
0043ccd3 RecorderApp.exe Variants                   @VarToLStr
008106c5 RecorderApp.exe Observations      2929 +10 TfrmObservations.GetLocationName
00810bb7 RecorderApp.exe Observations      2972  +1 TfrmObservations.GetEventName
00809b12 RecorderApp.exe Observations      1733  +8 TfrmObservations.PopulateEventLevel
0080b79d RecorderApp.exe Observations      1967 +16 TfrmObservations.tvObservationsExpanding
005ff8d5 RecorderApp.exe RapTree           4800  +2 TCustomRapidTree.CanExpand
00600ce2 RecorderApp.exe RapTree           5568  +5 TFlyNode.DoCanExpand
00601433 RecorderApp.exe RapTree           5764 +19 TFlyNode.ExpandItem
005fbdd9 RecorderApp.exe RapTree           2995 +33 TCustomRapidTree.MouseDown
0072c048 RecorderApp.exe KeyboardRapidTree  192  +1 TKeyboardRapidTree.MouseDown
004b449a RecorderApp.exe Controls                   TControl.DoMouseDown
004b44e6 RecorderApp.exe Controls                   TControl.WMLButtonDown
005ef9c6 RecorderApp.exe exgrid            4342  +1 TCustomExGrid.WMLButtonDown
005fa8dd RecorderApp.exe RapTree           2388  +4 TCustomRapidTree.WMLButtonDown
004b422c RecorderApp.exe Controls                   TControl.WndProc
004b728b RecorderApp.exe Controls                   TWinControl.WndProc
005fb4ba RecorderApp.exe RapTree           2714 +12 TCustomRapidTree.WndProc
004b6f08 RecorderApp.exe Controls                   TWinControl.MainWndProc
004529d0 RecorderApp.exe Classes                    StdWndProc
7739bf6f user32.dll                                 CallWindowProcW
7755770e comctl32.dll                               DefSubclassProc
773a16e0 user32.dll                                 DispatchMessageA
004d527b RecorderApp.exe Forms                      TApplication.ProcessMessage
004d52b2 RecorderApp.exe Forms                      TApplication.HandleMessage
004d54e2 RecorderApp.exe Forms                      TApplication.Run
00a32904 RecorderApp.exe RecorderApp        422 +83 initialization

thread $3434:
7c8285ec ntdll.dll                 KiFastSystemCallRet
7c827cf9 ntdll.dll                 NtWaitForMultipleObjects
77e62026 kernel32.dll              WaitForMultipleObjectsEx
7739bbcb user32.dll                MsgWaitForMultipleObjectsEx
7739ce31 user32.dll                MsgWaitForMultipleObjects
0042b939 RecorderApp.exe madExcept CallThreadProc
0042b97b RecorderApp.exe madExcept ThreadExceptFrame
>> created by main thread ($2f90) at:
4dd695ef gdiplus.dll               

thread $35b8:
7c8285ec ntdll.dll                 KiFastSystemCallRet
7c827839 ntdll.dll                 NtReplyWaitReceivePortEx
0042b939 RecorderApp.exe madExcept CallThreadProc
0042b97b RecorderApp.exe madExcept ThreadExceptFrame
>> created by main thread ($2f90) at:
77c7411a RPCRT4.dll               

thread $67cc:
7c8285ec ntdll.dll                 KiFastSystemCallRet
7c827cf9 ntdll.dll                 NtWaitForMultipleObjects
0042b939 RecorderApp.exe madExcept CallThreadProc
0042b97b RecorderApp.exe madExcept ThreadExceptFrame
>> created by main thread ($2f90) at:
71c50fe5 NETAPI32.dll              Netbios

thread $496c:
7c8285ec ntdll.dll     KiFastSystemCallRet
7c827d09 ntdll.dll     NtWaitForSingleObject
77e61d18 kernel32.dll  WaitForSingleObjectEx
77e61c88 kernel32.dll  WaitForSingleObject

thread $3c68:
7c8285ec ntdll.dll     KiFastSystemCallRet
7c8277d9 ntdll.dll     NtRemoveIoCompletion
77e5be9c kernel32.dll  GetQueuedCompletionStatus

thread $406c:
7c8285ec ntdll.dll     KiFastSystemCallRet
7c8277d9 ntdll.dll     NtRemoveIoCompletion
77e5be9c kernel32.dll  GetQueuedCompletionStatus

thread $468c:
7c8285ec ntdll.dll                 KiFastSystemCallRet
7c827839 ntdll.dll                 NtReplyWaitReceivePortEx
0042b939 RecorderApp.exe madExcept CallThreadProc
0042b97b RecorderApp.exe madExcept ThreadExceptFrame
>> created by thread $35b8 at:
77c7411a RPCRT4.dll               

modules:
00330000 C4DLL.DLL         1.0.0.1           \\Ntssvr01\Recorder 6 Server
003a0000 MS5User.Dll       5.0.0.4           \\Ntssvr01\Recorder 6 Server
00400000 RecorderApp.exe   6.16.2.241        \\Ntssvr01\Recorder 6 Server
01240000 Twnhook.dll       4.0.2900.1        C:\Program Files\Citrix\system32
01330000 cxinjime.dll      4.0.2198.1        C:\Program Files\Citrix\system32
018f0000 ms5cnv.dll        5.0.0.10          \\Ntssvr01\Recorder 6 Server
01ca0000 odbcint.dll       3.526.3959.0      C:\WINDOWS\system32
02ef0000 xpsp2res.dll      5.2.3790.3959     C:\WINDOWS\system32
032c0000 W03A2409.dll      5.2.3790.4043     C:\WINDOWS\system32
03fc0000 swi_ifslsp.dll    3.2.4.0           C:\Documents and Settings\All Users\Application Data\Sophos\Web Intelligence
04060000 SQLOLEDB.RLL      2000.86.3959.0    C:\Program Files\Common Files\System\Ole DB
04070000 msadcer.dll       2.82.3959.0       C:\Program Files\Common Files\System\msadc
04360000 GOTOGR~1.OCX      2.0.0.16          \\Ntssvr01\Recorder 6 Server\Addins
04510000 GoToKey.ocx       6.0.1.10          \\Ntssvr01\Recorder 6 Server\Addins
04740000 LCPROC~1.OCX                        \\Ntssvr01\Recorder 6 Server\Addins
04920000 NBNEXC~1.OCX      6.17.0.9          \\Ntssvr01\Recorder 6 Server\Addins
04d60000 SHOWUN~1.OCX      6.0.0.13          \\Ntssvr01\Recorder 6 Server\Addins
04f90000 STDVAL~1.DLL      6.14.3.208        \\Ntssvr01\Recorder 6 Server\Addins
10000000 MS5.Dll           5.0.0.12          \\Ntssvr01\Recorder 6 Server
48890000 odbc32.dll        3.526.3959.0      C:\WINDOWS\system32
4a710000 comsvcs.dll       2001.12.4720.3959 C:\WINDOWS\system32
4b3c0000 MSCTF.dll         5.2.3790.3959     C:\WINDOWS\system32
4b750000 HHCtrl.ocx        5.2.3790.3959     C:\WINDOWS\system32
4bbe0000 msado15.dll       2.82.3959.0       C:\Program Files\Common Files\System\ado
4c850000 oledb32.dll       2.82.3959.0       C:\Program Files\Common Files\System\Ole DB
4d140000 msadce.dll        2.82.3959.0       C:\Program Files\Common Files\System\msadc
4dbb0000 OLEDB32R.DLL      2.82.3959.0       C:\Program Files\Common Files\System\Ole DB
4dc30000 msctfime.ime      5.2.3790.3959     C:\WINDOWS\system32
4dd60000 gdiplus.dll       5.2.3790.4278     C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.3790.4278_x-ww_AD682293
4e250000 sqloledb.dll      2000.86.3959.0    C:\Program Files\Common Files\System\Ole DB
5e9a0000 olepro32.dll      5.2.3790.3959     C:\WINDOWS\system32
5f270000 hnetcfg.dll       5.2.3790.3959     C:\WINDOWS\system32
61200000 MSDATL3.dll       2.82.3959.0       C:\Program Files\Common Files\System\Ole DB
667c0000 tzhook.dll        4.0.2900.1        C:\Program Files\Citrix\system32
669f0000 wdmaudhook.dll    4.0.2198.1        C:\Program Files\Citrix\system32
66b50000 scardhook.dll     4.0.2900.1        C:\Program Files\Citrix\system32
66c80000 mmhook.dll        4.0.2900.1        C:\Program Files\Citrix\system32
66fb0000 mfaphook.dll      4.0.2900.1        C:\Program Files\Citrix\system32
67aa0000 CtxSbxHook.dll    4.0.2900.1        C:\Program Files\Citrix\system32
68000000 rsaenh.dll        5.2.3790.3959     C:\WINDOWS\system32
68100000 dssenh.dll        5.2.3790.3959     C:\WINDOWS\system32
69b10000 msxml4.dll        4.30.2100.0       C:\WINDOWS\system32
6d560000 DBNETLIB.DLL      2000.86.3959.0    C:\WINDOWS\system32
6e220000 RMProcessLink.dll 4.0.0.0           C:\Program Files\Citrix\system32
6fa00000 SOPHOS~1.DLL      10.0.9.7004       C:\PROGRA~1\Sophos\SOPHOS~1
6fa40000 swi_lsp.dll       1.0.6.0           C:\Documents and Settings\All Users\Application Data\Sophos Web Intelligence
71ae0000 wshtcpip.dll      5.2.3790.3959     C:\WINDOWS\System32
71b20000 mswsock.dll       5.2.3790.4318     C:\WINDOWS\system32
71b70000 uxtheme.dll       6.0.3790.3959     C:\WINDOWS\system32
71bb0000 wsock32.dll       5.2.3790.0        C:\WINDOWS\system32
71bd0000 mpr.dll           5.2.3790.3959     C:\WINDOWS\system32
71bf0000 WS2HELP.dll       5.2.3790.3959     C:\WINDOWS\system32
71c00000 WS2_32.dll        5.2.3790.3959     C:\WINDOWS\system32
71c20000 tsappcmp.dll      5.2.3790.3959     C:\WINDOWS\system32
71c40000 NETAPI32.dll      5.2.3790.4392     C:\WINDOWS\system32
71f60000 security.dll      5.2.3790.0        C:\WINDOWS\system32
73070000 winspool.drv      5.2.3790.3959     C:\WINDOWS\system32
73440000 RICHED32.DLL      5.2.3790.0        C:\WINDOWS\system32
745e0000 msi.dll           3.1.4000.4042     C:\WINDOWS\system32
74a50000 MSDART.DLL        2.82.3959.0       C:\WINDOWS\system32
74c40000 RICHED20.dll      5.31.23.1225      C:\WINDOWS\system32
75da0000 SXS.DLL           5.2.3790.3959     C:\WINDOWS\system32
75e60000 apphelp.dll       5.2.3790.3959     C:\WINDOWS\system32
76190000 MSASN1.dll        5.2.3790.3959     C:\WINDOWS\system32
761b0000 crypt32.dll       5.131.3790.3959   C:\WINDOWS\system32
76290000 IMM32.DLL         5.2.3790.3959     C:\WINDOWS\system32
762b0000 comdlg32.dll      6.0.3790.3959     C:\WINDOWS\system32
766d0000 SHFolder.dll      6.0.3790.3959     C:\WINDOWS\system32
766f0000 ntdsapi.dll       5.2.3790.3959     C:\WINDOWS\system32
76750000 schannel.dll      5.2.3790.4068     C:\WINDOWS\system32
76920000 USERENV.dll       5.2.3790.3959     C:\WINDOWS\system32
76aa0000 winmm.dll         5.2.3790.3959     C:\WINDOWS\system32
76b70000 PSAPI.DLL         5.2.3790.3959     C:\WINDOWS\system32
76c90000 msv1_0.dll        5.2.3790.3959     C:\WINDOWS\system32
76cf0000 iphlpapi.dll      5.2.3790.3959     C:\WINDOWS\system32
76ed0000 DNSAPI.dll        5.2.3790.4318     C:\WINDOWS\system32
76f00000 wtsapi32.dll      5.2.3790.3959     C:\WINDOWS\system32
76f10000 WLDAP32.dll       5.2.3790.3959     C:\WINDOWS\system32
76f50000 Secur32.dll       5.2.3790.3959     C:\WINDOWS\system32
76f70000 winrnr.dll        5.2.3790.3959     C:\WINDOWS\System32
76f80000 rasadhlp.dll      5.2.3790.3959     C:\WINDOWS\system32
77010000 COMRes.dll        2001.12.4720.3959 C:\WINDOWS\system32
771f0000 WINSTA.dll        5.2.3790.3959     C:\WINDOWS\system32
77380000 user32.dll        5.2.3790.4033     C:\WINDOWS\system32
77420000 comctl32.dll      6.0.3790.3959     C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.3790.3959_x-ww_D8713E55
77530000 comctl32.dll      5.82.3790.3959    C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_5.82.3790.3959_x-ww_78FCF8D0
77670000 ole32.dll         5.2.3790.3959     C:\WINDOWS\system32
777b0000 CLBCatQ.DLL       2001.12.4720.3959 C:\WINDOWS\system32
77b90000 version.dll       5.2.3790.3959     C:\WINDOWS\system32
77ba0000 msvcrt.dll        7.0.3790.3959     C:\WINDOWS\system32
77c00000 GDI32.dll         5.2.3790.4396     C:\WINDOWS\system32
77c50000 RPCRT4.dll        5.2.3790.4115     C:\WINDOWS\system32
77d00000 oleaut32.dll      5.2.3790.4202     C:\WINDOWS\system32
77da0000 SHLWAPI.dll       6.0.3790.3959     C:\WINDOWS\system32
77e40000 kernel32.dll      5.2.3790.4062     C:\WINDOWS\system32
77f50000 ADVAPI32.dll      5.2.3790.3959     C:\WINDOWS\system32
7c800000 ntdll.dll         5.2.3790.3959     C:\WINDOWS\system32
7c8d0000 shell32.dll       6.0.3790.4315     C:\WINDOWS\system32

If I continue with the application then the Survey is still in the list without an expand icon so conceivably I could delete it?  Maybe with somekind of batch update to clean up the orphaned survey events and related records?

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website

9 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Skip Surveys from import

The Delete Batch Update is fairly good at dealing with things which are not as they should be. Give that a try first and it will at least identify which tables have issues. It is available as a quick batch update by right clicking on the Survey  and choosing Batch Updates/Deletes. After running it you need to close down the Observation hierarachy and open it again.

Mike Weideli

MikeWeideli's Website

10 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Skip Surveys from import

Thanks for the quick response Mike. 

No batch updates appeared when I right clicked on the survey so I ran Del5 from the Tools menu.  It reported deleting 4090 records, the 'Dump' survey has gone, and the number of duplicate records reported has dropped by 6490, so it looks like it worked!

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website