Syntax of rs.Open Statement

[Philosophaie]

I just put in "SELECT * FROM [Checking] ORDER BY [Index];". I am not sure of the syntax.



My connection String works fine.

I rarely update this. It may be once a week or two.

The error code I am getting is:

Error #:-2147217887
Field cannot be updated.

It does not specify which field. Here is an excerpt from my code:

    rs.Open "SELECT * FROM [Checking] ORDER BY [Index];", ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
        Options:=adCmdText
    rs.MoveFirst
    For k = 1 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row - 4
        If rs!Index <> k Or rs!total <> Sheets("2013").Cells(k + 4, 5).Value Then
            rs!Index = Sheets("2013").Cells(k + 4, 1).Value
            rs!DatePaid = Sheets("2013").Cells(k + 4, 2).Value
            rs!WhatPaid = Sheets("2013").Cells(k + 4, 3).Value
            rs!AmtPaid = Sheets("2013").Cells(k + 4, 4).Value
            rs!total = Sheets("2013").Cells(k + 4, 5).Value
            rs!AmtRec = Sheets("2013").Cells(k + 4, 6).Value
            rs!WhatRec = Sheets("2013").Cells(k + 4, 7).Value
            rs!DateRec = Sheets("2013").Cells(k + 4, 8).Value
            rs.Update
        End If
        
        rs.MoveNext
    Next k

[Dal Jeanis]

First, review the [Checking] table and see if "Index" is, as it claims, an Index on the table. If so, then you can't just change it.

Can you explain what you think the code is supposed to be doing? Are you really changing the checking records on the table, including their indexes, based on your spreadsheet?

[Philosophaie]

[Index] is a Column of Access Table [Checking] as are DatePaid, WhatPaid, AmtPaid, total, AmtRec, WhatRec, and DateRec.

These are columns in the Access table with the correct spelling.

The code is first, load the data into the recordset, rs, sorting by the [Index] as it goes:

rs.Open "SELECT * FROM [Checking] ORDER BY [Index];", ActiveConnection:=cn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
Options:=adCmdText

Next it wiil take the whole Excel file and compare it to the rs data and if it is different update the Access row.

This probable will only be updated every other week.

[Dal Jeanis]

WHAT THE ERROR MESSAGE SAYS

Look at the Index field on the table and see if it is a primary key. The error message is saying Access is refusing to update a field, and updating a key is problematic at best.

WHAT YOUR CODE DOES:

Okay, so RS.Movefirst moves to the first record. Then k gets set to 1.

if the Index of RS record 1 is not = 1, or if the Total on RS record 1 is not equal to the value of sheet("2013").Cells(5,5), then whatever record we have in RS will be overwritten with the data on line 5 of sheet("2013").

Okay, I get it. You have four lines of headers, which accounts for the difference and all the +4s and -4s.

WHAT I'M NOT UNDERSTANDING:

What does the Index field really mean? How is it used? Is it really just 4 less than the row number on the excel spreadsheet?

And what happens if there are more lines on the spreadsheet than are on the RS?

What seems odd to me is that, if a row gets added or deleted in the the excel spreadsheet, then you aren't trying to figure out what happened to that data.

POSSIBLE FIXES:

First, if you're just trying to overwrite the table's information with whatever is in excel, then you can just delete everything and insert the new data and don't do any compares at all. It's easier and accomplishes the same thing.

Second, you could add a unique primary key to [Checking] that wasn't the Index field, make the Index field an indexed non-PK field that allows duplicates, and you should be able to get past your issue without changing the logic of the import routine. You still have to figure out what to do if there are more lines on the spreadsheet than there are in the RS recordset, though. That situation would probably present a very different error, though.

Third -- and here I'm just kibbitzing like a busybody -- if you're going to fix anything, you could update your table once to add 4 to all the indexes, change the code to start k at 5, and then get rid of all the +4s and -4s to make the code more readable. Sorry - the urge to mess with other people's code is one of the driving forces of geek nature.

[Philosophaie]

I have no Primary key in Access Columns. I want to number in [Index] straight from Excel. I have my own renumbering program in there for deletes and inserts.

First, in one of my attempts at this I tried deleting all the rows and inserting straight from Excel. The "rs.AddNew" got stuck on all the deleted cells "#Deleted" and gave me an error. If there was a way to delete the whole rows or whole table without leaving any trace of the cell. Yes I would consider using this method.

Second, a Primary Key would put Access in a dominate role (like it should be) but how would you sync it up with Excel?

Third, Thanks for taking the time!

[Dal Jeanis]

If there's no index on the [Index] field, then Access may be complaining that you are attempting to change the field that it is using in the ORDER BY or the record set.

The VBA code for deleting everything in a table is:

   Dim strSQL As String 
   Dim db As DAO.Database
   Set db = CurrentDb

   ' kill all current records
   strSQL = "DELETE FROM [tablename];"
   db.Execute (strSQL)

On another thread today, there's some code that deletes a table called tblMonths and then inserts records one at a time into the empty table. You can use that code (in Post #6) for reference. https://www.accessforums.net/queries...nge-36173.html

After the delete, you might want to .requery whatever was displaying the records beforehand, just to clear all those #deleted# messages.

PRIMARY KEY

If you don't care what changed, then using a key to match up anything isn't needed. On the other hand, if you added a primary key that was the line number, you could use the key to decide which line to look at, and to be sure you were looking at the right information.

Since I have no idea how the excel spreadsheet index number in column 1 are being used, I really can't speak to what the best way might be.