I just upsized my access database to SQL server 2000. The only problem I am having is the "Cannot update. Database or object is read-only". I know its not the back end, I've given full permission to this account. Also tested it through query analyzer create, insert, update etc. to make sure permissions weren't the case.
I m getting the data off of an excel sheet. Then looping each row depending on the number of Quantity(each row must be 1 item), then updating into "barinv" table. The part I cannot get to work is the Update. The code works fine when I m not using Linked Tables to SQL Server.
Would greatly appreciate any help. Here is my code.
'Import the Excel file
filename = "C:\" & Me![filename]
If Right(filename, 4) <> ".xls" Then filename = filename & ".xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbllabels", filename
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset
Dim labelstd As DAO.TableDef
Dim addrs As DAO.Recordset
Set labelstd = db.TableDefs("tbllabels")
Set rs = labelstd.OpenRecordset
'skip the table headings row - remove this if tableheadings are removed from excel sheet
'DAO Hack to quickly add a record - barinv key will never be 0
Set qry = db.QueryDefs("qry_add_barinv")
qry.Parameters(0) = 0
Set addrs = qry.OpenRecordset
Dim loopqty As Long
While Not rs.EOF
loopqty = rs!F3
While Not loopqty = 0
addrs.AddNew <------------ this is where I get read-only error
addrs!dept = rs!f1
addrs!sku = rs!F2
addrs!qty = rs!F3
addrs!desc1 = rs!F4
addrs!desc2 = rs!F5
addrs!desc3 = rs!F6
addrs!desc4 = rs!F7
addrs!partno = rs!F8
addrs!loaddate = rs!F9
addrs!customer = rs!F10
addrs!custPO = rs!F11
addrs!NFRC = rs!F12
addrs!Wrap = rs!F13
addrs!printed = False
loopqty = loopqty - 1
'Close all dao objects we used for initial data population
Set rs = Nothing
Set addrs = Nothing
Set qry = Nothing
Set labelstd = Nothing
The easiest way to tell if there is Primary Key on the table (that Access can use) is to link to the table. If Access prompts you with a list of fields that are in the table then it doesn't know or can't use the current Primary Key.
If you have a Primary Key in SQL Server, what is the data type? When I started using SQL Server as a backend I selected BigInt as the data type (SQL 2005). I later found out that Access cannot handle a BigInt variable and therefore the table becomes Read Only becuase Access cannot tell SQL Server which record to update because the Primary Key field gets corrupted.
Another way to approach the problem is to use and ADO connection and recordset to connect directly with SQL Server. Are you familiar with ADO?
tbllabels does not have a primary key. That table is not linked to to SQL Server. It is created at the beginning of the function, then deleted. I've created a primary key in tbllabels table, but I m getting the same error. I would love to use ADO in this circumstance but I m afraid of starting over again.
OK, what is the query qry_add_barinv doing? Are joining any tables in the query. After looking at it more closely it seems to me that you shouldn't be able to add any records to qry_add_barinv. If you open it directly in Access can you add anything?