Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Melbourne, Victoria, Australia
    Thanked 0 Times in 0 Posts

    DB Read Only (3027) midway through VB sub (A97 sr2)

    Can anybody say why the following code generates error 3027 (read only access to database)? The table is actually linked to a DBIII file on our server, but the preceeding code uses myDB.Execute strSQL (update queries) repeatedly without any problems, so I know I can write to the database. I then need to loop through and increment an address field, so I start using a recordset. The error arises at the rst.Edit instruction

    I dumped the SQL shown below into a brand new query and I didn't get a message indicating the recordset can't be updated. As per usual, I have hi-jacked the code from another sub which I know does work, although in that case the query is a very simple "SELECT * FROM tbl ORDER BY a couple of fields;"

    Things that might cause problems
    The inner join.. if so, why?
    The alias (surely not)

    The code that doesn't work:
    <pre>strSQL = "SELECT dbf_variable.ADDR AS Address "
    strSQL = strSQL & "FROM dbf_variable INNER JOIN tblFixVariableSiteList "
    strSQL = strSQL & "ON LEFT(dbf_variable.NAME, InSTR(dbf_variable.NAME, ""_"")-1) "
    strSQL = strSQL & "= tblFixVariableSiteList.SiteName "
    strSQL = strSQL & "WHERE (dbf_variable.NAME Like ""*_FL?_TW"" "
    strSQL = strSQL & "OR dbf_variable.NAME Like ""*_FL?_TM"") "
    strSQL = strSQL & "ORDER BY VAL(MID(dbf_variable.ADDR,3,5));"
    Set rst = myDB.OpenRecordset(strSQL)
    If (rst.EOF) Then
    MsgBox "No Address Tags to Update.", , "Oh Well"
    i = 0
    While Not rst.EOF
    i = i + 1
    rst![Address] = "AI" & Format(AddressNumber + i) & ".Val"
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: DB Read Only (3027) midway through VB sub (A97 sr2)

    Assuming that the missing declarations dimmed MyDB as a DAO.Database and rst as a DAO.Recordset, your problem is that you haven't opened a dynaset. By not specifying the type option in the OpenRecordset statement, you wound up with a non-updatable recordset. Try specifying dbOpenDynaset.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts