Thursday Tip Day: Creating an access 97 compatible geodatabase in ArcGIS 9.2
Access 97 is still a popular choice for databases (especially in archaeology!) but the default ArcGIS 9.2 geodatabase is in Access 2000 format and cannot be opened in earlier versions of Microsoft Access. It is possible to create a geodatabase that can be used by both programmes.
- Create a blank database in Microsoft Access 97 and save it to the location where you want your geodatabase to be stored. Close Access
- In ArcCatalog, navigate to the database, and right-click to add a new object. Choose “table” rather than “feature class” or any of the other options.
- Create a simple table called”test”, or similar- you can delete in in a minute. This creates all the geodatabase-specific tables that you need in the database.
- Import your spatial data into the geodatabase, or create new data as required. You should now be able to access the data in both Microsoft Access and ArcGIS.
As always with geodatabases- do not delete geodatabase tables or feature classes in Access as they will remain visible in ArcCatalog. Only use ArcCatalog for deletions.
does this mean if you have already created a database in access with eastings/northings it will not work, unless i rebuild the database as you describe?
thanks
ok, sorry Im a fool. I have built it in access 2003 but do not know how to make it work in arcview. could you do a little piece on that please, plus may be how to convert a natgrid tile into a number.
Thanks
Hi Richie,
Thanks for stopping by- I don’t have much experience with Access 2003 because we’re actually trying to move away from it completely. However, I can try and help you figure it out- if you can give me some more information on the errors that you are getting. Regarding your other comment- do you mean how to convert the Ordnance Survey grid square into numbers for use in eastings and northings? I can maybe write something on that…
Cheers
Jo
Yes please. I hear you can use a formulae in excel to convert grid letters to numbers, but I am not sure how. I am mainly using SU tiles but to make it work in ArcGIS i assume i need to use only eastings and northings as numbers. If you know how to convert letters into numbers, that would be most helpful, thanks
Hi Richie,
I’ll see what I can do- it’s quite straightforward so I’ll try and get a post up about converting grid squares to eastings/northings asap.
Jo
Hi, Here is some old VBA code you maybe able to salvage. Put into Excel and set up some entry and result cells.
Jim
Function getEastNorthfromNGR(NGR As String) As String
Dim firstChar, secondChar As String
Dim TileRef As String
Dim numerics As String
Dim length As Long
Dim half As Long
Dim East As String
Dim North As String
Dim message As String
Dim BS As String
firstChar = Mid(NGR, 1, 1)
secondChar = Mid(NGR, 2, 1)
TileRef = Mid(NGR, 1, 2)
length = Len(NGR)
numerics = Mid(NGR, 3, length)
half = Len(numerics) / 2
East = Mid(NGR, 3, half)
North = Mid(NGR, 3 half, length)
‘ If (Not (IsNumeric(firstChar) And IsNumeric(secondChar))) Then
‘ MsgBox “The first two characters should be letters. Try again please.)”
‘ Exit Function
‘ End If
If (length = 6) Then
BS = “000″
Then
ElseIf (length =
BS = “00″
ElseIf (length = 10) Then
BS = “0″
Else
‘ MsgBox “The Tile Reference should be 6 or 8 or 10 characters long. Try again”
Exit Function
End If
If TileRef = “” Then
message = “No data in cell!”
ElseIf (TileRef = “NA”) Then
message = “0″ & East & BS & ” - ” & “9″ & North & BS
ElseIf (TileRef = “NB”) Then
message = “1″ & East & BS & ” - ” & “9″ & North & BS
ElseIf (TileRef = “NC”) Then
message = “2″ & East & BS & ” - ” & “9″ & North & BS
ElseIf (TileRef = “ND”) Then
message = “3″ & East & BS & ” - ” & “9″ & North & BS
ElseIf (TileRef = “NF”) Then
message = “0″ & East & BS & ” - ” & “8″ & North & BS
ElseIf (TileRef = “NG”) Then
message = “1″ & East & BS & ” - ” & “8″ & North & BS
ElseIf (TileRef = “NH”) Then
message = “2″ & East & BS & ” - ” & “8″ & North & BS
ElseIf (TileRef = “NJ”) Then
message = “3″ & East & BS & ” - ” & “8″ & North & BS
ElseIf (TileRef = “NK”) Then
message = “4″ & East & BS & ” - ” & “8″ & North & BS
ElseIf (TileRef = “NL”) Then
message = “0″ & East & BS & ” - ” & “7″ & North & BS
ElseIf (TileRef = “NM”) Then
message = “1″ & East & BS & ” - ” & “7″ & North & BS
ElseIf (TileRef = “NN”) Then
message = “2″ & East & BS & ” - ” & “7″ & North & BS
ElseIf (TileRef = “NO”) Then
message = “3″ & East & BS & ” - ” & “7″ & North & BS
ElseIf (TileRef = “NR”) Then
message = “1″ & East & BS & ” - ” & “6″ & North & BS
ElseIf (TileRef = “NS”) Then
message = “2″ & East & BS & ” - ” & “6″ & North & BS
ElseIf (TileRef = “NT”) Then
message = “3″ & East & BS & ” - ” & “6″ & North & BS
ElseIf (TileRef = “NU”) Then
message = “4″ & East & BS & ” - ” & “6″ & North & BS
ElseIf (TileRef = “NW”) Then
message = “1″ & East & BS & ” - ” & “5″ & North & BS
ElseIf (TileRef = “NX”) Then
message = “2″ & East & BS & ” - ” & “5″ & North & BS
ElseIf (TileRef = “NY”) Then
message = “3″ & East & BS & ” - ” & “5″ & North & BS
ElseIf (TileRef = “NZ”) Then
message = “4″ & East & BS & ” - ” & “5″ & North & BS
ElseIf (TileRef = “SC”) Then
message = “2″ & East & BS & ” - ” & “4″ & North & BS
ElseIf (TileRef = “SD”) Then
message = “3″ & East & BS & ” - ” & “4″ & North & BS
ElseIf (TileRef = “SE”) Then
message = “4″ & East & BS & ” - ” & “4″ & North & BS
ElseIf (TileRef = “TA”) Then
message = “5″ & East & BS & ” - ” & “4″ & North & BS
ElseIf (TileRef = “SH”) Then
message = “2″ & East & BS & ” - ” & “3″ & North & BS
ElseIf (TileRef = “SJ”) Then
message = “3″ & East & BS & ” - ” & “3″ & North & BS
ElseIf (TileRef = “SK”) Then
message = “4″ & East & BS & ” - ” & “3″ & North & BS
ElseIf (TileRef = “TF”) Then
message = “5″ & East & BS & ” - ” & “3″ & North & BS
ElseIf (TileRef = “TG”) Then
message = “6″ & East & BS & ” - ” & “3″ & North & BS
ElseIf (TileRef = “SM”) Then
message = “1″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “SN”) Then
message = “2″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “SO”) Then
message = “3″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “SP”) Then
message = “4″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “TL”) Then
message = “5″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “TM”) Then
message = “6″ & East & BS & ” - ” & “2″ & North & BS
ElseIf (TileRef = “SS”) Then
message = “2″ & East & BS & ” - ” & “1″ & North & BS
ElseIf (TileRef = “ST”) Then
message = “3″ & East & BS & ” - ” & “1″ & North & BS
ElseIf (TileRef = “SU”) Then
message = “4″ & East & BS & ” - ” & “1″ & North & BS
ElseIf (TileRef = “TQ”) Then
message = “5″ & East & BS & ” - ” & “1″ & North & BS
ElseIf (TileRef = “TR”) Then
message = “6″ & East & BS & ” - ” & “1″ & North & BS
ElseIf (TileRef = “SW”) Then
message = “1″ & East & BS & ” - ” & “0″ & North & BS
ElseIf (TileRef = “SX”) Then
message = “2″ & East & BS & ” - ” & “0″ & North & BS
ElseIf (TileRef = “SY”) Then
message = “3″ & East & BS & ” - ” & “0″ & North & BS
ElseIf (TileRef = “SZ”) Then
message = “4″ & East & BS & ” - ” & “0″ & North & BS
ElseIf (TileRef = “TV”) Then
message = “5″ & East & BS & ” - ” & “0″ & North & BS
End If
getEastNorthfromNGR = message
End Function
Sub splitEN()
Dim length As Integer
Dim easting As String
Dim northing As String
ActiveCell.Select
EN = ActiveCell.Offset(0, -1).Value
length = Len(EN)
If (length = 15) Then
easting = Left(EN, 6)
northing = Right(EN, 6)
End If
ActiveCell.Offset(0, 1).Value = easting
ActiveCell.Offset(0, 2).Value = northing
End Sub
Function splitEastings(EN As String)
Dim length As Integer
Dim easting As String
length = Len(EN)
If (length = 15) Then
easting = Left(EN, 6)
northing = Right(EN, 6)
End If
‘ ActiveCell.Offset(0, 1).Value = Easting
‘ ActiveCell.Offset(0, 2).Value = Northing
splitEastings = easting
End Function
Function splitNorthings(EN As String)
Dim length As Integer
Dim easting As String
length = Len(EN)
If (length = 15) Then
‘ Easting = Left(EN, 6)
northing = Right(EN, 6)
End If
‘ ActiveCell.Offset(0, 1).Value = Easting
‘ ActiveCell.Offset(0, 2).Value = Northing
splitNorthings = northing
End Function
Function compareGridReferences(firstRef As String, secondRef As String) As String
Dim indexDigit As Integer
Dim firstRefLength As Integer
Dim secondRefLength As Integer
Dim message As String
Dim BS As String
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1)) Then
message = Mid(firstRef, 1, 1) & “00000″
End If
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1) And Mid(firstRef, 2, 1) = Mid(secondRef, 2, 1)) Then
message = Mid(firstRef, 1, 2) & “0000″
End If
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1) And Mid(firstRef, 2, 1) = Mid(secondRef, 2, 1) _
And Mid(firstRef, 3, 1) = Mid(secondRef, 3, 1)) Then
message = Mid(firstRef, 1, 3) & “000″
End If
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1) And Mid(firstRef, 2, 1) = Mid(secondRef, 2, 1) _
And Mid(firstRef, 3, 1) = Mid(secondRef, 3, 1) And Mid(firstRef, 4, 1) = Mid(secondRef, 4, 1)) Then
message = Mid(firstRef, 1, 4) & “00″
End If
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1) And Mid(firstRef, 2, 1) = Mid(secondRef, 2, 1) _
And Mid(firstRef, 3, 1) = Mid(secondRef, 3, 1) And Mid(firstRef, 4, 1) = Mid(secondRef, 4, 1) _
And Mid(firstRef, 5, 1) = Mid(secondRef, 5, 1)) Then
message = Mid(firstRef, 1, 5) & “0″
End If
If (Mid(firstRef, 1, 1) = Mid(secondRef, 1, 1) And Mid(firstRef, 2, 1) = Mid(secondRef, 2, 1) _
And Mid(firstRef, 3, 1) = Mid(secondRef, 3, 1) And Mid(firstRef, 4, 1) = Mid(secondRef, 4, 1) _
And Mid(firstRef, 5, 1) = Mid(secondRef, 5, 1) And Mid(firstRef, 6, 1) = Mid(secondRef, 6, 1)) Then
message = Mid(firstRef, 1, 6)
End If
compareGridReferences = message
End Function
Function addSC(entry As String)
Dim Scotland As String
Scotland = “SC”
addSC = Scotland & entry
End Function
Function RemoveSpaces(ByVal TargetString As String) As String
On Error GoTo Err_RemoveSpaces
‘ ***** Removes all spaces from a string
‘ ***** SGJ 10/07/2000
‘ ***** Parameters expected:
‘ TargetString
‘ ***** Returned values:
‘ string on success
‘ empty string on fail
‘ ***** Called by:
‘ UNUSED!
RemoveSpaces = “”
Dim MyPosition As Long
TargetString = Trim(TargetString)
MyPosition = InStr(1, TargetString, ” “)
Do Until MyPosition < 1
TargetString = Left$(TargetString, MyPosition - 1) & Right$(TargetString, Len(TargetString) - MyPosition)
MyPosition = InStr(1, TargetString, ” “)
Loop
RemoveSpaces = TargetString
Exit_RemoveSpaces:
Exit Function
Err_RemoveSpaces:
MsgBox “Error In RemoveSpaces routine” & vbCrLf & “#” & Err.number & “: ” & Err.Description, vbCritical, ApplicationName
Resume Exit_RemoveSpaces
End Function
Sub computeRadiusArray()
Dim requiredRadius As Long
Dim targetReference As Range
Dim allReferences As Range
requiredRadius = Application.InputBox(”What radius do you want to check?”, “Radius Required”)
Set targetReference = Application.InputBox(prompt:=”Select center site to check”, Type:=8)
Set allReferences = Application.InputBox(prompt:=”Select ranges to check”, Type:=8)
End Sub
Function computeRadius(firstEast As String, firstNorth As String, secondEast As String, secondNorth As String) As String
Dim eastingDifference As Double
Dim northingDifference As Double
Dim rangeRadius As Double
Dim message As String
eastingDifference = Val(firstEast) - Val(secondEast)
northingDifference = Val(firstNorth - secondNorth)
rangeRadius = Sqr((eastingDifference * eastingDifference) (northingDifference * northingDifference))
computeRadius = Int(rangeRadius)
End Function
Function checkDistanceLimit(distance As Integer) As String
Dim message As String
Dim inputBoxResult As String
Dim limit As Integer
inputBoxResult = InputBox(”Enter the distance range”)
limit = Int(Val(inputBoxResult))
If (distance <= limit) Then
message = “Within ” & limit & ” radius.”
Else
message = “Not Within Radius”
End If
checkDistanceLimit = message
End Function
Thanks Jim- that’s really handy, and an improvement on my own version!