Hi Eric,
I downloaded the spreadsheet file, but there are no latitude and longitude
values shown in cells A6:B8, for your three test records. I attempted to
click on "Geocode all rows", but I get prompted for a Yahoo ID that I do not
have.
However, if you are seeing valid latitude and longitude values in your copy
of the spreadsheet, then you should be able to easily get these values into
your Access table. I highly recommend that when you export your query in
Access, to create the spreadsheet that needs the latitude and longitude
values looked up, that you include the primary key field for the table in
question. If your table does not currently include a primary key field, then
add a new field (perhaps name it pkCustomer or whatever looks reasonable).
This can simply be an autonumber data type.
After retrieving your latitude and longitude data, I would create a named
range within the Excel spreadsheet, which includes the four fields: Latitude,
Longitude, Precision (if you care about this field), and pkCustomer. Are you
familiar with the process of selecting rectangular blocks of data within
Excel, in order to create named ranges? Working with named ranges helps to
ensure that you only get exactly what you want to get, and nothing more.
Now, you simply need an UPDATE query, to update your existing records.
Create a linked table to the named range in your Excel spreadsheet. Create a
new query. Select your existing table that needs to be updated, along with
the linked Excel file. Drag a join line between the two primary key fields,
to prevent a cartesian product query result. Add appropriate criteria for the
table in your database, so that only records that need to be updated are
included in the resulting recordset. This might include, for example, using
an Is Null criteria for the Latitude or Longitude fields (make it an OR so
that records with either null Latitude or null Longitude [or both] are
included in your recordset. When you get this SELECT query returning the
desired records, convert it to an UPDATE query (Query | Update Query, if you
are using Access 2003 or lesser version). You should see a new Update To row
in the QBE (Query By Example) grid. Type in the fully qualified names of the
fields from your linked table (a fully qualified name includes the name of
the table plus the name of the field, so try to use a short name for your
linked table). For example:
Field Name: Latitude
Update To: [MyExcel].[Latitude]
Criteria: Is Null
Field Name: Longitude
Update To: [MyExcel].[Longitude]
Criteria: Is Null
Does this help?
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________