Can't update Access fields from linked Excel file

[Guest]

I have an Access table built with some relationships. It has an autonumber
as Primary Key. I have an Excel file with all (except Primary Key) fields
the same.

I want to update the records from the linked Excel file. I'll never know
what fields of updated Excel file will change. I can't delete all the
records in Access table due to relations.

I've tried update query's using criteria that if Acess <> Excel update
field, but can't get it to work -- errro message says it can't change the
'(expression)'.

Any help would be appreciated...........

Thanks,

Kim

 

[Douglas J. Steele]

What's the SQL of your query?

 

[Guest]

I was trying to use standard Update Query Design. The query field and table
contained the Excel file information. The Update To contained the Access
table/field with criteria <> [Excel table].[field]

Have I over simplified it?

Thanks,

Kim

 

[Douglas J. Steele]

Sorry, are you trying to update the Excel spreadsheet from the Access table,
or update the Access table from the Excel spreadsheet?

If you're trying to update Excel from Access using a query, I'm afraid
you're out of luck. Microsoft recently lost a lawsuit that required that
they remove that ability from Access 2002 and 2003.

If you are trying to update Access from Excel, it would help to see the
actual SQL of your query. To see this, select "SQL View " on the View menu
when you've got the query open. Copy the SQL, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was trying to use standard Update Query Design. The query field and
table
contained the Excel file information. The Update To contained the Access
table/field with criteria <> [Excel table].[field]

Have I over simplified it?

Thanks,

Kim

What's the SQL of your query?

 

[Guest]

Hope your're still out there Douglas,

I am trying to update Access from a linked Excel spreadsheet. In a nutshell
I have a table in Access that needs to be updated from a linked Excel
spreadsheet. The Excel file will be updated periodically and I won't know
which fields will be updated.
I need the query to locate the changed records (new ones may also be added)
and update the Access table.

The tables are indentical (14 fields) except for the additional Primary Key
(Autonumber) in the Access table.

My SQL, so far is below. I've trimmed most of the fields off for brevity.
I'm trying this just to see if I can get what I need. Updating
tblVolunteerName is next.

SELECT tblNewVolunteerName.[First Name], tblNewVolunteerName.[Last Name],
tblNewVolunteerName.[Street Address]
FROM tblNewVolunteerName LEFT JOIN tblVolunteerName ON
tblNewVolunteerName.[First Name]=tblVolunteerName.[First Name]
WHERE (((tblVolunteerName.[First Name]) Is Null))
Union
SELECT tblNewVolunteerName.[First Name], tblNewVolunteerName.[Last Name],
tblNewVolunteerName.[Street Address]
WHERE (((tblVolunteerName.[Last Name]) Is Null));

So far this is the only way I can get JUST the changed records.

Any help you can offer would be appreciated.

Thanks,

Kim

Sorry, are you trying to update the Excel spreadsheet from the Access table,
or update the Access table from the Excel spreadsheet?

If you're trying to update Excel from Access using a query, I'm afraid
you're out of luck. Microsoft recently lost a lawsuit that required that
they remove that ability from Access 2002 and 2003.

If you are trying to update Access from Excel, it would help to see the
actual SQL of your query. To see this, select "SQL View " on the View menu
when you've got the query open. Copy the SQL, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was trying to use standard Update Query Design. The query field and
table
contained the Excel file information. The Update To contained the Access
table/field with criteria <> [Excel table].[field]

Have I over simplified it?

Thanks,

Kim

What's the SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Access table built with some relationships. It has an
autonumber
as Primary Key. I have an Excel file with all (except Primary Key)
fields
the same.

I want to update the records from the linked Excel file. I'll never
know
what fields of updated Excel file will change. I can't delete all the
records in Access table due to relations.

I've tried update query's using criteria that if Acess <> Excel update
field, but can't get it to work -- errro message says it can't change
the
'(expression)'.

Any help would be appreciated...........

Thanks,

Kim

 

[Douglas J. Steele]

See whether my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hope your're still out there Douglas,

I am trying to update Access from a linked Excel spreadsheet. In a
nutshell
I have a table in Access that needs to be updated from a linked Excel
spreadsheet. The Excel file will be updated periodically and I won't know
which fields will be updated.
I need the query to locate the changed records (new ones may also be
added)
and update the Access table.

The tables are indentical (14 fields) except for the additional Primary
Key
(Autonumber) in the Access table.

My SQL, so far is below. I've trimmed most of the fields off for brevity.
I'm trying this just to see if I can get what I need. Updating
tblVolunteerName is next.

SELECT tblNewVolunteerName.[First Name], tblNewVolunteerName.[Last Name],
tblNewVolunteerName.[Street Address]
FROM tblNewVolunteerName LEFT JOIN tblVolunteerName ON
tblNewVolunteerName.[First Name]=tblVolunteerName.[First Name]
WHERE (((tblVolunteerName.[First Name]) Is Null))
Union
SELECT tblNewVolunteerName.[First Name], tblNewVolunteerName.[Last Name],
tblNewVolunteerName.[Street Address]
WHERE (((tblVolunteerName.[Last Name]) Is Null));

So far this is the only way I can get JUST the changed records.

Any help you can offer would be appreciated.

Thanks,

Kim

Sorry, are you trying to update the Excel spreadsheet from the Access
table,
or update the Access table from the Excel spreadsheet?

If you're trying to update Excel from Access using a query, I'm afraid
you're out of luck. Microsoft recently lost a lawsuit that required that
they remove that ability from Access 2002 and 2003.

If you are trying to update Access from Excel, it would help to see the
actual SQL of your query. To see this, select "SQL View " on the View
menu
when you've got the query open. Copy the SQL, and paste it into your
reply.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was trying to use standard Update Query Design. The query field and
table
contained the Excel file information. The Update To contained the
Access
table/field with criteria <> [Excel table].[field]

Have I over simplified it?

Thanks,

Kim

:

What's the SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Access table built with some relationships. It has an
autonumber
as Primary Key. I have an Excel file with all (except Primary Key)
fields
the same.

I want to update the records from the linked Excel file. I'll never
know
what fields of updated Excel file will change. I can't delete all
the
records in Access table due to relations.

I've tried update query's using criteria that if Acess <> Excel
update
field, but can't get it to work -- errro message says it can't
change
the
'(expression)'.

Any help would be appreciated...........

Thanks,

Kim