Hi
I've not tried to do this before and it's not currently working, so may be not possible, but here's what I'm trying to achieve:
I've got 2 tables - an address list and a restaurant list. I've got longitude and latitude values for all records, and I'm trying to flag each address with it's nearest restaurant. I can do this by using a couple of recordsets, but there are 5 million addresses and 3000 facilities and it takes about 10 hours to run, so I'm looking to use queries instead to see if it speeds things up.
So here's the query for my address list:
... and here's the 'update_address' function:
The function returns the nearest restaurant ID and the distance as a string separated by a colon - I thought it would probably be quicker to do this and then run an update query later to separate the ID and distance.
The 'distance' function is just something to get the distance between 2 sets of longitude and latitudes and the 'nearest_rest' bit just looks up the lowest distance from the restaurant table and returns it so I can flag the address with the right site.
The problem is that the function won't let me do the Run.SQL statement. I get the error:
You can't carry out this action at the present time
So it seems I can't run an SQL statement from within a function called by my query.
Can anyone suggest a way around this please?
Thanks!
I've not tried to do this before and it's not currently working, so may be not possible, but here's what I'm trying to achieve:
I've got 2 tables - an address list and a restaurant list. I've got longitude and latitude values for all records, and I'm trying to flag each address with it's nearest restaurant. I can do this by using a couple of recordsets, but there are 5 million addresses and 3000 facilities and it takes about 10 hours to run, so I'm looking to use queries instead to see if it speeds things up.
So here's the query for my address list:
Code:
UPDATE [DATA 1] SET [DATA 1].Id_and_distance = update_address([lat],[lon]);
... and here's the 'update_address' function:
Code:
Function update_address(add_lat As Double, add_lon As Double) As String
Dim nearest_rest As String
Dim nearest_rest_dist As Double
Dim rest_tab As String
rest_tab = "Restaurants"
DoCmd.RunSQL "UPDATE [" & rest_tab & "] SET [" & rest_tab & "].[temp distance] = distance(" & add_lat & "," & add_lon & ",[lat],[lon]);"
nearest_rest = DLookup("[Nearest]", "Nearest Restaurant")
nearest_rest_dist = DLookup("[Nearest Dist]", "Nearest Restaurant")
update_address = nearest_rest & ":" & nearest_rest_dist
End Function
The function returns the nearest restaurant ID and the distance as a string separated by a colon - I thought it would probably be quicker to do this and then run an update query later to separate the ID and distance.
The 'distance' function is just something to get the distance between 2 sets of longitude and latitudes and the 'nearest_rest' bit just looks up the lowest distance from the restaurant table and returns it so I can flag the address with the right site.
The problem is that the function won't let me do the Run.SQL statement. I get the error:
You can't carry out this action at the present time
So it seems I can't run an SQL statement from within a function called by my query.
Can anyone suggest a way around this please?
Thanks!