VBS 999999 Error

[Liam_]

Hi,

I have written a script for the model builder's calculate field function in VBScript but I keep getting the 999999 error.

I have gone over my code several times but cannot seem to find a reason for it not to be able to run.


INPUT TABLE: JOIN_CarData_RoadLinks
FIELD NAME: JOIN_CarData_RoadLinks.OrientationOfCar
EXPRESSION: CarOrientation
[PHP]
Dim CarOrientation

Dim RoadOrientation
RoadOrientation = [JOIN_CarData_RoadLinks.Orientation]

Dim RoadCompass
RoadCompass = [JOIN_CarData_RoadLinks.CompassA]

Dim RoadCompassMax
RoadCompassMax = RoadCompass + 170

If RoadCompassMax > 360 Then
RoadCompassMax = RoadCompassMax - 360
ElseIf RoadCompassMax < 0 Then
RoadCompassMax = RoadCompassMax + 360
End If

Dim RoadCompassMin
RoadCompassMin = RoadCompass - 170

If RoadCompassMin > 360 Then
RoadCompassMin = RoadCompassMin - 360
ElseIf RoadCompassMax < 0 Then
RoadCompassMin = RoadCompassMin + 360
End If

Dim PointBearing
PointBearing = [JOIN_CarData_RoadLinks.Bearing]

If PointBearing > RoadCompassMin And PointBearing < RoadCompassMax Then
CarOrientation = RoadOrientation
End if

If PointBearing < RoadCompassMin Or PointBearing > RoadCompassMax Then
If RoadOrientation = "-" Then
  CarOrientation = "+"
End if

If RoadOrientation = "+" Then
  CarOrientation = "-"
End if
End if
[/PHP]
I have tried including and excluding the "JOIN_CarData_RoadLinks." in my field allocations and replacing the declared variables used in the body of my code with the actual table's fields. I have copied my code into a VBScript IDE to check the syntax and it couldn't find anything wrong.

Any help would be appreciated.

Thanks,


Liam.

[Liam_]

Hi,
Thank you again for your assistance, I am really grateful for your time and efforts in helping me on my project.

The earliest version of ArcGIS I have used was 9.3. When I was transferred to 10.1 and they had changed over from VBA to VBScript it ruined all my models and scripts! Still, they are useful to keep for reference :). I was not sure if the linear referencing tool was appropriate for a road network as I had only used it previously on a river project on the Norfolk Broads tracking the salt water that was brought in with each tide. However, the results from the analysis seem to be correct.

I have had a rummage through my project and collected the information you requested: -

1)

The first table, the target of the Join is the ITN_Network Geodatabase: -

Data Type:
File Geodatabase Feature Class
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\FinalMethod.gdb
Feature Dataset:
ITN_Network
Feature Class:
ITN_RoadLinks
Feature Type:
Simple
Geometry Type:
Line
Coordinates have Z values:
No
Coordinates have measures:
No
Projected Coordinate System:
British_National_Grid

The second table is the CarData_RoadLinks_Statistics standalone table:-

Data Type:
Standalone Table
Table Name:
CarData_RoadLinks_Statistics
Table Type:
File Geodatabase Table
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\CarDataProcessed.gdb
Has Object-ID Field:
Yes

2)

The joined field data types: -

Target Field:
[ITN_RoadLinks.RoadLinkTOID]

Type:
String
Allow NULL Values:
Yes
Length:
16

Although NULL values are allowed for this field, there are no records with NULL in this field.

Join Field:
[CarData_RoadLinks_Statistics.RoadLinkTOID]

Type:
String
Allow NULL Values:
Yes
Length:
16

Again, although NULL values are allowed for this field, there are no records with NULL in this field.

3)

The tables are joined with these settings: -

Target Table:
ITN_RoadLinks
Target Field:
RoadLinkTOID

Join Table:
CarData_RoadLinks_Statistics
Join Field:
RoadLinkTOID
Join Type:
Keep only matching records
Data Type:
Standalone Table
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\CarDataProcessed.gdb

4)

ITN_RoadLinks

Number of records:
729,157

CarData_RoadLinks_Statistics

Number of records:
1,328

Example of the join with some data: -
Here

I am trying to copy the ToFromTotalStats & FromToTotalStats from CarData_RoadLinks_Statistics into CarDataToFromTOTAL & CarDataFromToTOTAL in ITN_RoadLinks. All of the fields are double data types and allow null values.

I agree with not using Excel spreadsheets as a direct data source, they are just not designed for that and have advised many people not to. Even in MS Access, they can cause trouble as they have no record (or row) level read/write locking or efficient data structure past about 5000 rows. I have made specialist bespoke relational databases in MS Access for work, that�??s possibly where I get my bad terminology from :). When I said an ordinary table, I meant a table without any spatial data i.e. like within an MS Access database. Sorry for causing the confusion.

It is only my intention to export to excel so I can perform statistical analysis for my results. This will be the last step in the project and Arc will not be required to then read anything resulting from the statistical analysis. I was intending to do the statistics within Arc, however I need the Route Network to be fixed in order to do so. That would have been the best solution, but I guess the productivity suite has trouble processing the whole of South-East England's roads 🙂

I think I have covered all of the data you requested in your post, please let me know if I have missed anything out.

Thanks again,

Liam.

[RichardFairhurst]

Thank you. That information is a much better starting point to work out your problem.  I am assuming that this information is relevant to the original calculation you proposed in your very first post.  My comments are based on that assumption.  However, I am concerned that you may have resolved that problem and are giving me information that relates to a different problem that you are now having.  So please let me know if your original calculation is still the problem you are trying to solve.

My tests showed that your original calculation worked fine when it was applied to a feature class and table that matched the prefix and field names used in the calculation.  However, in your very first post you proposed a calculation that used a prefix to the fields called JOIN_CarData_RoadLinks.  That prefix makes no sense with the data you have described in your last post and definitely would result in an error.  So lets start there.

Since both of these data sources mentioned in your last post are within File Geodatabases, after the join is created, the calculation field prefixes that would reference fields within the Feature Class would have the form: ITN_RoadLinks.field.  Calculation field prefixes that would references fields within the Table would have the form: CarData_RoadLinks_Statistics.field.

For now I will assume that everywhere you wrote JOIN_CarData_RoadLinks you really meant to reference fields in the CarData_RoadLinks_Statistics table.  If that is true than the original calculation should have been written:

Dim CarOrientation

Dim RoadOrientation
RoadOrientation = [CarData_RoadLinks_Statistics.Orientation]

Dim RoadCompass
RoadCompass = [CarData_RoadLinks_Statistics.CompassA]

Dim RoadCompassMax
RoadCompassMax = RoadCompass + 170

    If RoadCompassMax > 360 Then
    RoadCompassMax = RoadCompassMax - 360
    ElseIf RoadCompassMax < 0 Then
    RoadCompassMax = RoadCompassMax + 360
    End If

Dim RoadCompassMin 
RoadCompassMin = RoadCompass - 170

    If RoadCompassMin > 360 Then
    RoadCompassMin = RoadCompassMin - 360
    ElseIf RoadCompassMax < 0 Then
    RoadCompassMin = RoadCompassMin + 360
    End If

Dim PointBearing
PointBearing = [CarData_RoadLinks_Statistics.Bearing]

If PointBearing > RoadCompassMin And PointBearing < RoadCompassMax Then
    CarOrientation = RoadOrientation
End if

If PointBearing < RoadCompassMin Or PointBearing > RoadCompassMax Then
    If RoadOrientation = "-" Then
        CarOrientation = "+"
    End if

    If RoadOrientation = "+" Then
        CarOrientation = "-"
    End if
End if

So which data source actually contains the fields named Orientation, CompassA and Bearing?  Are these fields all in the CarData_RoadLinks_Statistics table?  If not than any field in the calculation above that actually is found in the ITN_RoadLinks feature class should have the CarData_RoadLinks_Statistics prefix changed to the ITN_RoadLinks prefix.

===========

I am concerned that you said in one of your posts that ArcMap is crashing when you do this manually.  Your data is local to your machine, which is good, so you don't need to consider network communications as a factor for the crash.

I have a parcel database with approximately the same number of records you mentioned and ArcMap crashes frequently when I do joins or relates to it as well, usually during record selection or calculation through the join.  This is probably because joins and relates are built in memory, and if memory resources hit a wall, ArcMap crashes.  Some peoples seem to be able to work with even larger data sets, but the differences could be due to differences in the resources of the workstations processing the data.

The crash could also be due to the fact your data sources reside in separate geodatabases and the resources needed to deal with that are managed differently than resources used when both are within a single geodatabase.  I would suggest manual tests in Desktop where the feature class and table are in the same geodatabase, just to see if that makes a difference.

One way to make sure the problem is with resource management only is to do all of your tests on a dummy data base that uses a small fraction of your actual data first before scaling it up to your full dataset.  If errors trigger on the small scale data it is a user fault that needs to be resolved first, but if problems only arise when you attempt to go to a larger scale than it is likely a resource issue.

When does ArcMap crash?  As soon as you attempt the join?  That is a definite resource issue.

Does it crash only after beginning a field calculation?  Did you try a simple calculation on the data first or have you only attempted a complex calculation on the joined data?  You should try a field calculation after the join is created using a simple value like " " or 0 into a dummy field, just to see if ArcMap would let you do that.  If you could not do that on your full dataset, than resource management is the issue.  However, if you can get past these basic steps and crashes only occur when you do more complex calculations, then more small scale testing followed by large scale experimentation is required to determine what actually triggers the crash.

When crashes due to resource use come into play, instability is difficult to predict, since you really don't know what is happening in the background and what the trigger point for the crash is.  It could indicate a memory leak on the part of ESRI in some cases.  You may actually have to provide ESRI with your actual data to determine if the problem you are having is reproducible or resolvable if you get to a point where you know it should work and every step works with a much smaller subset of data.

[Liam_]

Hi,

Thank you for your quick reply 🙂

This is a different problem from the original post, I kept it in the same thread as it is the same project I didn't want to clog up the forum with all my problems.

Your solution for the first problem should be correct but it didn't work for me. I had to export the join by saving it as one single feature class. But this could point to a system resource issue or a deeper problem with my installation.

In my current problem, the join executes successfully and I am able to view the attributes table (after a long wait) but I cannot run any field calculations via model builder, toolbox or the attributes table: calculate field without ArcMap crashing. I also always run Arc in administrator mode, as I have found that read/write errors occur frequently without Arc having admin rights.

I suspect you could be right about the resources though, but despite my computer being old; it has had a few upgrades. Here are my specs: -

Processor: Intel Core 2 CPU X6800 @ 2.93GHz Overclocked to 3190 Mhz.
Installed Physical Memory (RAM): 4.00 GB (32-bit Windows 7 though)

Although my system is overclocked, it is very stable. The CPU takes a beating from some of my other applications quite regularly. The RAM has been in a while and shows 100 faults per second which is normal for the amount I have. The GPU is powerful, not that it will have much to do with Arc and my hard drive seems to be healthy. I also compact the database regularly but I'm not sure if it performs the same function as 'Compact and Repair' in MS Access. On a side note, the Compact and Repair function in MS Access usually fixes most issues like this.

I will try to perform some manual edits on the table, cutting it down in size and your suggestion on combining the geodatabases tomorrow and report back.

Thanks again for your help,

Liam.

[RichardFairhurst]

I looked at the hyperlink of the join with some data in Number 4 of your post that responded to my questions.  It did not answer some of the questions I need answered.

1.  I could not tell what fields were coming from what data source in the example.  If you used aliases (which you almost certainly did) convert the example to show the actual full field names so I can see how they would have to be used in a calculation.  Also you mentioned fields that I do not see in the example data:  ToFromTotalStats & FromToTotalStats from CarData_RoadLinks_Statistics and CarDataToFromTOTAL & CarDataFromToTOTAL in ITN_RoadLinks.  What field in your example data corresponds to the RoadLinkTOID field?

2.  You also said you only Keep Matching Records using the join.  How many features actually appear once the join is in place?  The answer to this question would partly help answer the next question.

3.  You also did not answer another critical question:  What is the relationship between the ITN_RoadLinks feature class and the CarData_RoadLinks_Statistics table?

One-to-One - the RoadLinkTOID values are unique for each feature and each table record and only one matched table view record is possible for any given RoadLinkTOID value.
Many-to-One - the features do not have unique RoadLinkTOID values, but every table record has a unique RoadLinkTOID value.
One-To-Many - every feature has a unique RoadLinkTOID value, but every table record is not unique for the RoadLinkTOID values.
Many-To-Many - the RoadLinkTOID values are not unique for the features or the table records.

If the relationship is One-to-One, then the ITN_RoadLinks feature class only contains a small subset of data related to the CarData_RoadLinks_Statistics table.  At most 1,328 features are matched in the table.  The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features.  This relationship is unlikely.

If the relationship is Many-to-One, then CarData_RoadLinks_Statistics could potentially relate to all or any subportion of the ITN_RoadLinks feature class and the matches would most likely greatly exceed 1,328 features.  Potentially every record in the ITN_RoadLinks feature class could be matched by the CarData_RoadLinks_Statistics table.  The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features.  The effect of the join is that in memory you are presented in essence with a table view that shows a one-to-one version of the feature and table records combined.  This relationship is the most likely and can contribute to memory instability.

A One-to-Many relationship would mean that less than 1,328 features are involved in the join and potentially many of the records in the CarData_RoadLinks_Statistics table are not seen through the join.  This relationship is the least likely.

A Many-to-Many relationship would involve a multiplication of features if fully joined that could be in the millions of features if the relationship was converted to a One-To-One relationship.  Many of the records in the CarData_RoadLinks_Statistics table associated with the features are not actually seen through the join.  This relationship is the second most likely, but the worst to deal with and the most likely to result in memory instability.

--------

If the relationship type is Many-to-One then a python script that uses two cursors can be fairly easily written that would convert the CarData_RoadLinks_Statistics table to a dictionary to transfer the data.  This script should have a much lower memory footprint than a join if written properly.  A Many-to_many relationship would be almost impossible to work out without blowing out the memory.

[Liam_]

Hi,

Thanks for taking a look, sorry I didn't add in enough detail; I have updated the document and added samples of the two datasets as well. They can be found here. There was also only 1,117 records remaining after the join - which is odd.

The relationship between the roads and the stats is one to many as one road TOID in ITN_RoadLinks can have 2 entries in the CarData_RoadLinks_Statistics that contain ToFrom and FromTo car data.

Example: -
Road x is 1km long and is on a hill, therefore there will be a big increase in the fuel used going up the hill (FromTo) than down the hill (ToFrom). Therefore, Road x can have an entry in the statistics table twice, each representing the cost associated with the direction of travel:

Road x going FromTo @ 10l/km = Total Fuel Used: 10 litres
Road x going ToFrom @ 1l/km = Total Fuel Used: 1 litre.

The FromTo and ToFrom directions are calculated by Ordnance Survey and represented by a '+' for FromTo and a '-' for ToFrom in the Orientation field in ITN_RoadLinks.

Therefore the following scenarios can arise: -

Road x may have 2 entries in the statistics table as it was travelled upon in both directions.
Road y may have just one entry in the statistics table as it is a one way carriageway on a motorway.
Road z may have no entries in the statistics table as it was not travelled on in either direction.

However, the entry in ITN_RoadLinks for Road z cannot be deleted because the theorised l/km value attached to it may be used later on.

Roads x, y & z all have theoretical l/km stored in the FromToTotal and ToFromTotal fields - these are independant from the real world l/km values stored in the statistics table.

Therefore, to compare the statistical correlation between the theoretical and real world observations - the real world values (held in the statistics table) need to be copied to the correct fields in the ITN_RoadLinks table: CarDataToFromTOTAL & CarDataFromToTOTAL.

I have tried manually editing the fields, but it won't allow me to type anything into the cells. I tried placing the tables in the same geodatabase, but I still couldn't edit at all - not even just a small selection of records.

Thanks again,

Liam.

[RichardFairhurst]

I am not surprised by the records loss you see if the relationship is in fact a One-to-Many relationship.

Stop joining the data together and stop introducing new names for your objects like "JOIN SAMPLE DATA:" and "INDIVIDUAL TABLE SAMPLE DATA:".  Get me down to the raw data before the join.

A joined table cannot be edited while it is joined to another table.  That is an intentional behavior and is especially required with a One-to-Many relationship since ArcGIS cannot interpret that kind of relationship through a join.  One-to-Many relationships are not supported by joins.

Since there are usually 2 table records you must convert it to a single record to use it through a join.  Either they must be split into two tables to use a join to transfer the data, with one containing the first instance of a RoadLinkTOID value and the second containing the second instance of the same RoadLinkTOID value.  Then you would do two different joins and make a choice if any overwrites would occur as a result of the second join.  Otherwise you need to summarize the table records again so that there is only one record in the join table.  You can never see both records through the join without exporting the join while both are in the same geodatabase.  But the effect of the export would be to duplicate the feature shape into two overlapping shapes, which you do not want.

I advise you to collapse the Summary table into a one-to-one relationship and get rid of the Null values.  If I understand your data correctly this can be done with the Min and Max operator in a summary when only two records need to be collapsed.  What I currently imagine your data to look like in reality is shown below:

ITN_RoadLinks:
RoadLinkTOID CarDataToFromTOTAL CarDataFromToTOTAL
1 Null Null
2 Null Null
3 Null Null

CarData_RoadLinks_Statistics
RoadLinkTOID ToFromTotalStats FromToTotalStats
1 10 Null
1 Null 1
2 5 Null
3 Null 7
(2 records with the same RoadLinkTOID value matching the one above)

What I want to do is convert the CarData_RoadLinks_Statistics arrangement using another Summary to:

CarData_RoadLinks_Statistics_Statistics
RoadLinkTOID Min_ToFromTotalStats Max_ToFromTotalStats Min_FromToTotalStats Max_FromToTotalStats
1 Null 10 Null 1
2 5 5 Null Null
3 Null Null 7 7

This new table structure can be joined to the features as a One-to-One relationship join and unlike a One-to-Many relationship no records will fail to show up in the join.  Now, tell me why I cannot do the additional summary I am proposing with your CarData_RoadLinks_Statistics table to convert your existing One-to-Many relationship into a One-to-One relationship.

[Liam_]

Hi,

Thank you, I now understand why I cannot see both the entries for each TOID with a join.

I think I have the answer to your question: the summary cannot work with the null values as null is not automatically assumed to be 0 in a numeric field? - Therefore it would be looking for the min or the max of nothing?

Thanks again,

Liam.

[RichardFairhurst · Accepted solution]

Hi,

Thank you, I now understand why I cannot see both the entries for each TOID with a join.

I think I have the answer to your question: the summary cannot work with the null values as null is not automatically assumed to be 0 in a numeric field? - Therefore it would be looking for the min or the max of nothing?

Thanks again,

Liam.

Try the summary.  You are wrong about the assumption of the Summary.  0 is not used in the place of Null.  Null is preserved for Min and Max in a Geodatabase table if Null is the only choice.  It is disregarded if there is any non-Null choice.

So, I lied a little about the output.  If there is a Null in all input entries then the output of both Min and Max is Null, as I showed.  However, if one of the two entries is not Null and the other is Null, the Null entry is ignored and the Min and Max of the two records would just be the non-Null value.  That is what you want anyway, since if you have a non-Null value you don't care about the Null value.  You really only need the Max of FromTo and ToFrom, since you don't care about the Null if a non-Null value exists.  The only time you might care about a Min and Max is if you somehow had two different non-Null values for a single direction, which as I understand it, makes no sense for your model.

So if this was the input:

CarData_RoadLinks_Statistics
RoadLinkTOID ToFromTotalStats FromToTotalStats
1 10 Null
1 Null 1
2 5 Null
3 Null 7

This would actually be the output to a geodatabase table.

CarData_RoadLinks_Statistics_Statistics
RoadLinkTOID Min_ToFromTotalStats Max_ToFromTotalStats Min_FromToTotalStats Max_FromToTotalStats
1 10 10 1 1
2 5 5 Null Null
3 Null Null 7 7

You only have one slot to fill for each direction per ITN_RoadLinks feature and it is impossible to fill a single directional slot with two different values.  You must make a choice if Min and Max are different or take their sum or their average.  However, you should always disregard the Null value if there is any non-Null value.  You would only preserve the Null output if all the entries are Null.  That is exactly the behavior of the Summary with Min and Max when Null values are involved. 

Even if you requested the Mean, the Null values are disregarded when you do a Mean of two records where one has a Null value and the other record has a non-Null value.  In that situation, the Mean will just be the non-Null value for those two records.  The Mean will not be half of the non-Null value, which is what the assumption of 0 in the place of Null would produce.

Anyway, how would you make a single ITN_RoadLinks feature hold both a Null and a non-Null value at the same time for the ToFromTotalStats?  How would you make a single ITN_RoadLinks feature hold both a Null and a non-Null value at the same time for the FromToTotalStats?  You can't.  You would have choose one or the other.  The summary makes the choice the same way you would by only considering Null values when no non-Null values exist.  If any non-Null values exist the Nulls are disregarded entirely.  Null and non-Null values are never mixed together by the Summary tool.

Just in case you ever have two different non-Null values for a specific direction on a specific segment, how would you make a single ITN_RoadLinks feature hold two different non-Null values at the same time for either of those directional fields?  You can't.  You would have choose the Sum, the Min, the Max, or the Mean.  Those summary values are what the Summary Statistics tool gives to you to translate two or more values into a single value for a single slot.

If this finally answers your question, be sure to mark the thread as answered for the benefit of others.

[Liam_]

Hi,

Thanks it worked and I now have a one-to-one relationship with my ITN_RoadLinks table with the correct amount of records (1,117). What is also really useful is the frequency field as I can now easily identify roads that I travelled on in both directions, which maybe useful later on 🙂

As for my answer to your question, I did get confused as to what the problem may have been with what you were suggesting as when I quickly ran it it appeared to work. You got me looking for a problem that was not there 😛

You really only need the Max of FromTo and ToFrom, since you don't care about the Null if a non-Null value exists. The only time you might care about a Min and Max is if you somehow had two different non-Null values for a single direction, which as I understand it, makes no sense for your model.

- That is correct, my model and methodology would not allow two real world costs to be associated with a single direction at this point in the process. When I had all my points spatially joined to the roads that they were attached to, I had run the summary tool to get the mean averages.

Thanks again for all your help, I can now transfer my values to the other fields the same way as before 🙂