Cannot update linked Paradox table field with DAO recordset?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DHofer - 28 Sep 2007 15:33 GMT

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.

Reply to this Message

Dennis - 28 Sep 2007 21:37 GMT

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.

Reply to this Message

DHofer - 28 Sep 2007 22:44 GMT

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.

Reply to this Message

DHofer - 09 Oct 2007 19:24 GMT

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:
>
[quoted text clipped - 54 lines]
> > > in Access 2007? DAO.recordset will let me read a value, but not update it,
> > > linked tables cannot be updated with DAO.recordset.

Reply to this Message

Pieter Wijnen - 15 Oct 2007 17:38 GMT

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:
>
[quoted text clipped - 67 lines]
>> > > update it,
>> > > linked tables cannot be updated with DAO.recordset.

Reply to this Message