Results 1 to 2 of 2
2004-02-04, 02:48 #1
- 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"
2004-02-04, 03:10 #2
- 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.Charlotte