Cannot update linked Paradox table field with DAO recordset?

[DHofer]

What is the method to update a field with vba in a linked Paradox 7.0 table
in Access 2007? DAO.recordset will let me read a value, but not update it,
linked tables cannot be updated with DAO.recordset.

 

[Dennis]

I would think it would. You could try an ADO-code approach and see if that
does it.

 

[DHofer]

I've tried DAO and ADO as follows:

Private Sub AddNewBidADO_Click()

Dim rs As New ADODB.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

rs.Open "_IdNums", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'fails on next line Run-Time error '-2147217911 (80070e09)': Cannot
update. Database or object is read-only.
rs("Bid No") = LastBidNo + 1
rs.Update
End If

End Sub

Private Sub AddNewBidDAO_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

Set db = CurrentDb
b = CurrentDb.Updatable 'gives True
Set rst = db.OpenRecordset("_IDNUMS")
b = rst.Updatable 'gives False

Do
rst.Edit 'Run-time error 3027. Cannot update. Database or object is
read-only.
rst![Bid No] = LastBidNo + 1

'only 1 record in table
Exit Do
Loop Until rst![Bid No] = LastBidNo
rst.Update
rst.Close
End If

End Sub

 

[DHofer]

Answer:

The Paradox 7.0 _IDNUMS.DB table was a single record table with several
fields to record the last id number used in a number of other tables. It did
not have a primary key. Linked tables must have a primary key to be able to
write to.

I've tried DAO and ADO as follows:

Private Sub AddNewBidADO_Click()

Dim rs As New ADODB.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

rs.Open "_IdNums", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'fails on next line Run-Time error '-2147217911 (80070e09)': Cannot
update. Database or object is read-only.
rs("Bid No") = LastBidNo + 1
rs.Update
End If

End Sub

Private Sub AddNewBidDAO_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

Set db = CurrentDb
b = CurrentDb.Updatable 'gives True
Set rst = db.OpenRecordset("_IDNUMS")
b = rst.Updatable 'gives False

Do
rst.Edit 'Run-time error 3027. Cannot update. Database or object is
read-only.
rst![Bid No] = LastBidNo + 1

'only 1 record in table
Exit Do
Loop Until rst![Bid No] = LastBidNo
rst.Update
rst.Close
End If

End Sub

I would think it would. You could try an ADO-code approach and see if that
does it.

 

[Pieter Wijnen]

You can create "dummy" unique indexes in Access for The Paradox Tables
ie Write in a Query (SQL View)

Create Unique Index MyIndexName On MyLinkedTable (MyField1 [,..,MyFieldN])

Hth

Pieter

Answer:

The Paradox 7.0 _IDNUMS.DB table was a single record table with several
fields to record the last id number used in a number of other tables. It
did
not have a primary key. Linked tables must have a primary key to be able
to
write to.

I've tried DAO and ADO as follows:

Private Sub AddNewBidADO_Click()

Dim rs As New ADODB.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

rs.Open "_IdNums", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'fails on next line Run-Time error '-2147217911 (80070e09)': Cannot
update. Database or object is read-only.
rs("Bid No") = LastBidNo + 1
rs.Update
End If

End Sub

Private Sub AddNewBidDAO_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LastBidNo As Long
Dim b As Boolean

If (Me.NewRecord = True) Then
LastBidNo = DFirst("[Bid No]", "_IdNums", "[Bid No]>0")
[Bid No] = LastBidNo + 1

Set db = CurrentDb
b = CurrentDb.Updatable 'gives True
Set rst = db.OpenRecordset("_IDNUMS")
b = rst.Updatable 'gives False

Do
rst.Edit 'Run-time error 3027. Cannot update. Database or object
is
read-only.
rst![Bid No] = LastBidNo + 1

'only 1 record in table
Exit Do
Loop Until rst![Bid No] = LastBidNo
rst.Update
rst.Close
End If

End Sub

I would think it would. You could try an ADO-code approach and see if
that
does it.

:

What is the method to update a field with vba in a linked Paradox 7.0
table
in Access 2007? DAO.recordset will let me read a value, but not
update it,
linked tables cannot be updated with DAO.recordset.