Problem with append query

[Paul3rd]

Hello, I'd like to use an append query in a On Click event to update my table
from
an Excel spreadsheet.
The table is named Air_Filters, and the spreadsheet resides on C: with full
permissions set for Everyone.
I'm using the following code for the query:

INSERT INTO [Air_Filters]
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\].AirFilters.xls;

The following code for the button:

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

Dim stDocName As String
Dim AFUP As Integer

stDocName = "AirFilters"
AFUP = MsgBox("Delete & Replace all air filters?", vbOKCancel, "Air
Filter Update")

If AFUP = 1 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
Else
MsgBox ("Update Cancelled")
End If
Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

The problem is that On Click I get the following error;
"Cannot update. Database or object is read-only."
Again my spreadsheet AirFilters.xls is not marked as read-only, and full
permissions are set for everyone.
Can anyone offer a suggestion?
Thanks in advance for any help.
Paul

 

[Bob Larson]

I think I remember Crystal saying something about Excel not being able to be
updated via a linked table (due to some of the Justice Dept settlement).
Although your FROM seems to be off if it was a linked table. If it was a
linked table all it would be is:

INSERT INTO [Air_Filters]
SELECT *
FROM YourLinkedTableNameHere;


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[bhicks11 via AccessMonster.com]

I ran into that too Bob. Found out you can't update a linked Excel
spreadsheet from Access.

From kb article:

Because of legal issues, Microsoft has disabled the functionality in
Access 2003 and in Access 2002 that let users change the data in
linked tables that point to a range in an Excel workbook. However,
when you make changes directly in the Excel workbook, the changes
appear in the linked table in Access.

It is possible he has an older version that was not updated with the "fix."

Bonnie
http://www.dataplus-svc.com

I think I remember Crystal saying something about Excel not being able to be
updated via a linked table (due to some of the Justice Dept settlement).
Although your FROM seems to be off if it was a linked table. If it was a
linked table all it would be is:

INSERT INTO [Air_Filters]
SELECT *
FROM YourLinkedTableNameHere;

Hello, I'd like to use an append query in a On Click event to update my
table

[quoted text clipped - 44 lines]

Thanks in advance for any help.
Paul