Help for coordinates

[Newo]

Hello,

I'm quite unfamiliar with access vba and i'm actually facing some trouble:
I got a VBA code on Excel that help me find the shortest road between sets of coordinates, but since I'm going to have hundred of thousand of data I wish to do it on Access, the problem is I don't know how to translate the code I got on Excel to Access.
Here's the code I use in Excel:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Function distance(A As Range, listeB As Range) Dim datas, ptA, lig As Long Dim b As Double, c As Double, d As Double ptA = A.Value datas = listeB.Value distance = 99999 '=SI((ACOS(SIN(RADIANS(A2))*SIN(RADIANS($D$2))+COS(RADIANS(A2))*COS(RADIANS($D$2))*COS(RADIANS(B2-$E$2)))*6371)>1;0;-1) With Application.WorksheetFunction b = Sin(.Radians(ptA(1, 1))) c = Cos(.Radians(ptA(1, 1))) For lig = 1 To UBound(datas) d = (.Acos(b * Sin(.Radians(datas(lig, 1))) + c * Cos(.Radians(datas(lig, 1))) * Cos(.Radians(ptA(1, 2) - datas(lig, 2)))) * 6371) If d < distance Then distance = d Next lig End With End Function

Thanks for your time

 

[June7]

See if this helps Get Distance (msaccessgurus.com)

 

[The_Doc_Man]

In general, the only difference between Access VBA and Excel VBA is (a) how you trigger it and (b) from where you get/put the data. The math syntax is the same.

Where you have $D$2, for example, that would have to come EITHER from fields in a recordset opened for step-wise operation, or as elements of a query if you were trying to deal with this in bulk.