Access front end/SQL back end. Can''t update SQL db

Question

Access front end/SQL back end. Can''t update SQL db

Jim Heltmach

SSC-Addicted

Points: 463
More actions
June 8, 2007 at 7:48 am

#179115

Hi all; hoping someone can help me out with this issue I'm having. I have an 2003 Access db that I use to create a bunch of financial spreadsheets with on a daily basis. What I want to be able to do is archive the daily data to history tables in SQL Server, (2000). Problem is, when I get to the rs.Add command I get a runtime error; 3027. Cannot update. Database or object is Read-only. I'm stumped. I've checked the database in SQL, permissions arenot set to read only. I've checked users and roles to ensure that I've got access. I've checked my ODBC dsn, there aren't any properties set to read only. I don't know what else to check? I'm positive that it's a SQL issue and not Access. But, I've been wrong in the past. Is it possible that there's a registry setting wrong/missing, I'm not using the correct reference, etc???
Here's a sample of my code:
This is my DSN call.....
Sub mySetGlobalConnect()
        gstrDataSource = "jh_Sharepoint"
        gstrProvider = "SQLOLEDB"
        gstrServer = "MBF491"
        strLogInType = "UID=JimConnect" (not using a password)
gstrSQLServerConnect_ADO = "PROVIDER=SQLNCLI;SERVER=MBF491;DSN=Attorney Parsing Database;Database=" & gstrDataSource & ";" & strLogInType & ""
END SUB
This is where I connect to SQL....
Public Sub ConnectToSQL()
Dim cnnSP As New ADODB.Connection
    Dim cmdSP As New ADODB.Command
    Call mySetGlobalConnect
    cnnSP.ConnectionString = gstrSQLServerConnect_ADO
    cnnSP.Open
    DoEvents

    cmdSP.ActiveConnection = cnnSP
End Sub
Here's a bit of my write code, (after I've opened the recordset):
rst.AddNew
rst("[DepartmentName]") = rs("head1")
rst("[DeptID]") = rs("ivalue")
rst("[ARFees]") = rs("ar_fees")
rst("[ARCosts]") = rs("ar_cost")
rst("[AROther]") = rs("ar_other")
Hope this is enough to give a shaper set of eyes some input. Any help would be greatly appreciated!!!!
thanks!
jim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

That error message suggests that it's not an issue with any users being set for read-only permissions, but that the database itself is in read-only mode. Did you check that?

(From Enterprise Manager, right-click on the DB in question and select 'properties'...under the 'options' tab there is a 'read-only' check box.&nbsp

The Redneck DBA

Jim Heltmach SSC-Addicted Points: 463 More actions · Answer 2

Hey Jason, yep. It's not set to read only.....

jim

Saji Antony SSC Journeyman Points: 83 More actions · Answer 3

hi

did you check that the table has a primary key defined

TeddyBear SSC Enthusiast Points: 103 More actions · Answer 4

Hello,

You definitely need to have a primary key to update but sometimes you may also need to add a timestamp field to the SQL table you are linked to. I read this somewhere or got it from upsizing MS Access tables to SQL Server.

After you add the primary key or timestamp field you will have to drop the link and relink to the table to refresh the fields in the linked table on the MS Access side.

If this doesn't work, would you post the code you are using to open the table?

HTH

-TB

Jim Heltmach SSC-Addicted Points: 463 More actions · Answer 5

Hi, I think I figured it out. In my Access code, when I was opening the recordset I was setting it as dbOpenDynaset. I tried dbPessimistic and dbOptimistic and it seems to have worked.

jim