Trying to get an update query to work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a database that has these fields:
TableArea has SiteID and Status and StatusDate fields.
I get Excel files with updates that I need to add the new status and
statusdate from. I am trying to link the Excel file and do an update query
to replace the current TableArea status and statusdate with the Excel Status
and Statusdate. the Excel link works fine, but I need to find the correct
code to make the query pull the status and statusdate fields form the Excel
into the Access TableArea.
The excel files come several times weekly so I don't want to just keep
adding records for all these status lines. Is there some expression I can
use in the update query that will replace the access table status with the
excel file status?
Thanks, I can use a little help. Or a lot... :-)
 
I usually use the TransferSpreadsheet method in VBA. I would probably create
a table that is used as a temporary repository for the incoming Excel info.
Then, at runtime, I would:

1. Flush the contents of the table (using a Delete query) to ensure that it
has no old info left in it.
2. Use the TransferSpreadsheet method (DoCmd.TransferSpreadsheet...) to
import from Excel into that table
3. Run a query that updates the permanent table to match the newly-imported
data
4. Flush the temp table again to remove the temporary data.

Look in the VBA help for the syntax on TransferSpreadsheet or post back for
more specifics.
 
Hi,
I have a database that has these fields:
TableArea has SiteID and Status and StatusDate fields.
I get Excel files with updates that I need to add the new status and
statusdate from. I am trying to link the Excel file and do an update query
to replace the current TableArea status and statusdate with the Excel Status
and Statusdate. the Excel link works fine, but I need to find the correct
code to make the query pull the status and statusdate fields form the Excel
into the Access TableArea.
The excel files come several times weekly so I don't want to just keep
adding records for all these status lines. Is there some expression I can
use in the update query that will replace the access table status with the
excel file status?
Thanks, I can use a little help. Or a lot... :-)
Click to expand...

You need to Join the (linked or imported) Excel sheet to your local
table, joining by SiteID. Make this query into an Update query and
update [tablename].[status] to [linkedsheet].[status], and
[tablename].[statusdate] to [linkedsheet].[statusdate] - the brackets
are essential, they tell Access to look for a field value rather than
treating it as a text string.

John W. Vinson[MVP]
 
Hi John,
Thanks that worked! I was able to get the data to import. I gave up on the
Excel linked table though and just imported to a new Access table. That
ended up being faster. I used the expression builder to add the fileds. It
put them in as

[Sheet1]![Status] with a bang or exclamation point in between.

So my query fields looked like this:

Field: destination table field (field the data went into)
Table: desination table (table the data went into)
Update to: source table field (table field the data came from)
Criteria: blank
or: Blank

Good heavens, that took me long enough!
Brian,. thanks, but I don't think I am ready for the Excel VBA thing yet.
That sounds pretty scary right now. Maybe a little later though!

Thanks Brian and John, I may actually get somewhere now!
John Vinson said:
Hi,
I have a database that has these fields:
TableArea has SiteID and Status and StatusDate fields.
I get Excel files with updates that I need to add the new status and
statusdate from. I am trying to link the Excel file and do an update query
to replace the current TableArea status and statusdate with the Excel Status
and Statusdate. the Excel link works fine, but I need to find the correct
code to make the query pull the status and statusdate fields form the Excel
into the Access TableArea.
The excel files come several times weekly so I don't want to just keep
adding records for all these status lines. Is there some expression I can
use in the update query that will replace the access table status with the
excel file status?
Thanks, I can use a little help. Or a lot... :-)
Click to expand...

You need to Join the (linked or imported) Excel sheet to your local
table, joining by SiteID. Make this query into an Update query and
update [tablename].[status] to [linkedsheet].[status], and
[tablename].[statusdate] to [linkedsheet].[statusdate] - the brackets
are essential, they tell Access to look for a field value rather than
treating it as a text string.

John W. Vinson[MVP]
Click to expand...
 
Back
Top