CurrentDB.OpenRecordset on an access table linked to SQL

[Lost500]

I am trying to update a table in access using an excel file. I have an access db that uses tables linked to a sql db. when i try to open the table using the code: CurrentDB.OpenRecordset("TABLENAMEHERE", dbOpenTable, dbConsistent) I get the error 'invalid operation'. is this because the table i am trying to open is a linked table? is there a work around or is it easy to open the sql table and update it? (if so how) thank you in advance for any help.

 

[RoyVidar]

dbOpenTable I think, is only possible in a Jet Workspace. Don't specify anything. You might need dbSeeChanges, though.

Set TheRs = CurrentDB.OpenRecordset("TABLENAMEHERE",, dbSeeChanges)

Roy-Vidar

 

[Lost500]

thank you royVidar, now i am getting hung up with the code here is the module i think i need to adjust the code to work with sql like the above problem, any help would be greatly apreciated:

Sub Updatetable()
Dim CurrentDB As Database
Dim ExcelFile As Excel.Workbook
Dim TABLENAMEHERE As Recordset
Dim i As Integer

Set EXCELFILE = GetObject("FILEPATH.xlsx")
Set CurrentDB = DBEngine.Workspaces(0).Databases(0)
Set TABLENAMEHERE = CurrentDB.OpenRecordset("TABLENAMEHERE", , dbSeeChanges)

TABLENAMEHERE.Index = "INDEXEDFIELDNAMEHERE"
For i = 2 To 96
TABLENAMEHERE.Seek "=", EXCELFILE.Sheets(1).Cells(i, 1)
If TABLENAMEHERE.NoMatch Then
MsgBox EXCELFILE.Sheets(1).Cells(i, 1) & " " & EXCELFILE.Sheets(1).Cells(i, 2) & " " & i
Else
TABLENAMEHERE.Edit
TABLENAMEHERE("UPDATEDFIELD") = EXCELFILE.Sheets(1).Cells(i, 13)

TABLENAMEHERE.Update
End If
Next

So far TABLENAMEHERE.Index = "INDEXEDFIELDNAMEHERE" is not working, giving the error message 'operation is not supported for this data type'
Please help this will help me out greatly!!!

 

[PHV]

Why not simply use an UPDATE query on 2 linked tables ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lost500]

thank you for the input PHV but my problem is that the records already exists in the table but i have added new fields to the table so i need to add in the information from the excel file for the new fields using a id field to know which records are to be updated. if infact making the spreadsheet a temp table and then using an update query is still the best solution can you show me how? thank you for helping this is huge to me.