|
 |
access thread: Read Only when I try and do an RS.Edut
Message #1 by Ben Greenhouse <b.greenhouse@u...> on Thu, 08 Mar 2001 13:21:26 -0500
|
|
Hi all
I'm getting a 3027 "Cannot Update: the database or object is read-only"
when I run this code. I can't figure out why...Any suggestions?
Option Compare Database
Option Explicit
Sub Main()
Dim DB As Database, TB As TableDef, FD As Field, SQL As String, RS As
Recordset, RS2 As Recordset
Dim RS3 As Recordset, i As Integer, tblname As String, strfields As
String
Set DB = CurrentDb
SQL$ = "Select CardNumber, FirstName, LastName from NewData where
IsNull(FirstName) = False"
Set RS = DB.OpenRecordset(SQL$)
While Not RS.EOF
SQL$ = "Select * from Card where CardNumber = '"
SQL$ = SQL$ & RS!Cardnumber & "'"
Set RS2 = DB.OpenRecordset(SQL$)
'** first the cards that already have a customerid assigned to them
If RS2!CustomerID <> -1 Then
RS2.Edit
RS2!PreferredNameOnCard = RS!FirstName & " " & RS!LastName
RS2.Update
For i = 1 To 3
'Select Case i
'Case 1:
If i = 1 Then
tblname$ = "Address"
strfields$ = "Street1, Street2, AttentionCareOf,
ApartmentSuite, City, ProvinceState, "
strfields$ = strfields$ & "PostalZIPCode, Country,
AddressType, "
strfields$ = "a.DateonFile, "
strfields$ = "a.DateAmended"
End If
'Case 2:
If i = 2 Then
tblname$ = "Email"
strfields$ = "UserID, ServerID, a.DateOnFile,
a.DateAmended"
End If
'Case 3:
If i = 3 Then
tblname$ = "Telephone"
strfields$ = "AreaCode, Exchange, Line, Extension,
TelephoneType, FaxDataIndicator, "
strfields$ = strfields$ & "a.DateonFile,
a.DateAmended"
End If
'End Select
SQL$ = "Select " & strfields$ & " From "
SQL$ = SQL$ & tblname$
SQL$ = SQL$ & " a, CustomerContactPoint b where b.CustomerID
= "
SQL$ = SQL$ & RS2!CustomerID
SQL$ = SQL$ & " AND b.ContactPointType = '"
SQL$ = SQL$ & tblname$
SQL$ = SQL$ & "' AND b.ContactPointID = a.ContactPointID"
Set RS3 = DB.OpenRecordset(SQL$)
If RS3.RecordCount <> 0 Then
'Select Case i
'Case 1:
If i = 1 Then
'**Reset all the address values
RS3.Edit
RS3!Street1 = ""
RS3!Street2 = ""
RS3!AttentionCareOf = ""
RS3!ApartmentSuite = ""
RS3!City = ""
RS3!ProvinceState = ""
RS3!PostalZIPCode = "X#X #X#"
RS3!Country = "Canada"
RS3!AddressType = ""
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'Case 2:
If i = 2 Then
'** Reset all the email values
RS3.Edit
RS3!UserID = ""
RS3!ServerID = ""
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'Case 3:
If i = 3 Then
'** Reset all the telephone values
RS3.Edit
RS3!AreaCode = " "
RS3!Exchange = " "
RS3!Line = " "
RS3!Extension = " "
RS3!TelephoneType = " "
RS3!FaxDataIndicator = " "
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'End Select
RS3.Close
End If
Next
End If
RS.MoveNext
Wend
End Sub
Thanks
Ben
Message #2 by "Brian" <bgmst5@y...> on Fri, 9 Mar 2001 17:49:44
|
|
Hi,
You have to specify the LockType property of the recordset to something.
I believe you set it to adLockOptimistic (which i'm not sure of the
numerical value; it might be 4). Try that and see if it works.
Brian
Message #3 by Ben Greenhouse <b.greenhouse@u...> on Fri, 09 Mar 2001 13:05:27 -0500
|
|
Brian
Thanks for the advice...How do you go about doing that? I know when I
write asps I do RS.Open SQL, DB, adlockOptimistic but I don't know how to do
it using the syntax I'm using for this VBA code (Set RS = DB.OpenRecordSet
(SQL$))
Thanks
Ben
Brian wrote:
> Hi,
>
> You have to specify the LockType property of the recordset to something.
> I believe you set it to adLockOptimistic (which i'm not sure of the
> numerical value; it might be 4). Try that and see if it works.
>
> Brian
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Fri, 09 Mar 2001 10:07:16 -0800
|
|
Debug.Print your final SQL string before you open the rst that's
complaining, and then copy-paste it into the SQL view of a new query. See
what Access complains of (if anything) when you try to do manually what
you're trying to do programmatically below.
If the query comes up read-only, have a look at the help file topic "When
can I update data from a query?" & see if any of those issues apply.
HTH,
-Roy
-----Original Message-----
From: Ben Greenhouse [mailto:b.greenhouse@u...]
Sent: Thursday, March 08, 2001 10:21 AM
To: Access
Subject: [access] Read Only when I try and do an RS.Edut
Hi all
I'm getting a 3027 "Cannot Update: the database or object is read-only"
when I run this code. I can't figure out why...Any suggestions?
Option Compare Database
Option Explicit
Sub Main()
Dim DB As Database, TB As TableDef, FD As Field, SQL As String, RS As
Recordset, RS2 As Recordset
Dim RS3 As Recordset, i As Integer, tblname As String, strfields As
String
Set DB = CurrentDb
SQL$ = "Select CardNumber, FirstName, LastName from NewData where
IsNull(FirstName) = False"
Set RS = DB.OpenRecordset(SQL$)
While Not RS.EOF
SQL$ = "Select * from Card where CardNumber = '"
SQL$ = SQL$ & RS!Cardnumber & "'"
Set RS2 = DB.OpenRecordset(SQL$)
'** first the cards that already have a customerid assigned to them
If RS2!CustomerID <> -1 Then
RS2.Edit
RS2!PreferredNameOnCard = RS!FirstName & " " & RS!LastName
RS2.Update
For i = 1 To 3
'Select Case i
'Case 1:
If i = 1 Then
tblname$ = "Address"
strfields$ = "Street1, Street2, AttentionCareOf,
ApartmentSuite, City, ProvinceState, "
strfields$ = strfields$ & "PostalZIPCode, Country,
AddressType, "
strfields$ = "a.DateonFile, "
strfields$ = "a.DateAmended"
End If
'Case 2:
If i = 2 Then
tblname$ = "Email"
strfields$ = "UserID, ServerID, a.DateOnFile,
a.DateAmended"
End If
'Case 3:
If i = 3 Then
tblname$ = "Telephone"
strfields$ = "AreaCode, Exchange, Line, Extension,
TelephoneType, FaxDataIndicator, "
strfields$ = strfields$ & "a.DateonFile,
a.DateAmended"
End If
'End Select
SQL$ = "Select " & strfields$ & " From "
SQL$ = SQL$ & tblname$
SQL$ = SQL$ & " a, CustomerContactPoint b where b.CustomerID
= "
SQL$ = SQL$ & RS2!CustomerID
SQL$ = SQL$ & " AND b.ContactPointType = '"
SQL$ = SQL$ & tblname$
SQL$ = SQL$ & "' AND b.ContactPointID = a.ContactPointID"
Set RS3 = DB.OpenRecordset(SQL$)
If RS3.RecordCount <> 0 Then
'Select Case i
'Case 1:
If i = 1 Then
'**Reset all the address values
RS3.Edit
RS3!Street1 = ""
RS3!Street2 = ""
RS3!AttentionCareOf = ""
RS3!ApartmentSuite = ""
RS3!City = ""
RS3!ProvinceState = ""
RS3!PostalZIPCode = "X#X #X#"
RS3!Country = "Canada"
RS3!AddressType = ""
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'Case 2:
If i = 2 Then
'** Reset all the email values
RS3.Edit
RS3!UserID = ""
RS3!ServerID = ""
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'Case 3:
If i = 3 Then
'** Reset all the telephone values
RS3.Edit
RS3!AreaCode = " "
RS3!Exchange = " "
RS3!Line = " "
RS3!Extension = " "
RS3!TelephoneType = " "
RS3!FaxDataIndicator = " "
RS3!DateOnFile = Date
RS3!DateAmended = Date
RS3.Update
End If
'End Select
RS3.Close
End If
Next
End If
RS.MoveNext
Wend
End Sub
Thanks
Ben
Message #5 by RPCABQ@a... on Mon, 12 Mar 2001 14:51:55 EST
|
|
One thing I have found that gives me problems like this is a table assoc. with another table containing
primary keys.
Message #6 by Ben Greenhouse <b.greenhouse@u...> on Mon, 12 Mar 2001 15:18:16 -0500
|
|
Yeah...I think I figured it out, it seems that you can't edit a recordset that is drawn from more than
one table (i.e.
using a join). Seems weird, but I couldn't figure out a way to specify which table in the RS.Edit statements either,
(i.e. it wouldn't take RS!tablea.fieldb = blablabla, it would only take RS!fieldb). That might have solved the problem I
suppose...
Ben
RPCABQ@a... wrote:
> One thing I have found that gives me problems like this is a table assoc. with another table containing primary keys.
>
|
|
|
View Cart
