Add record to front end linked table.

[hapnendad]

The code below attempts to create a record in the tables listed and populating the PAR field (Primary Key) in each when a new project is added. The code adds rows to the tables but, the number of records does not increase.

Each of the tables a record is added to is linked to a backend access database.

Must I open the backend database and tables to perform these record additions or is there a way to accomplish this in the front end linked tables?

I have tried leaving options blank and as seen below.

Again rows are added to the tables but the number of records does not increase.

Any help you can provide is appreciated.

[code=vb]Sub TBLApprovalAddR ecord(Parqry)
Dim dbs As Database: Dim rst As Recordset: Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("Originator" )
rst.MoveLast
Parqry = rst("PAR").Valu e
rst.Close: Set rst = Nothing
Set rst = dbs.OpenRecords et("Approvals" , dbOpenDynaset)
If rst.EOF And rst.BOF Then
With rst
.AddNew: .Fields!PAR = Parqry: .Update
End With
Else
With rst
.MoveLast
.AddNew
.Fields!PAR = Parqry
.Update
End With
End If
rst.Close: Set rst = Nothing
Set rst = dbs.OpenRecords et("PARTeam", dbOpenDynaset)
If rst.EOF And rst.BOF Then
With rst
.AddNew
.Fields!PAR = Parqry
.Update
End With
Else
With rst
.MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
End With
End If
rst.Close: Set rst = Nothing
Set rst = dbs.OpenRecords et("TimeLine", dbOpenDynaset)
If rst.EOF And rst.BOF Then
With rst
.AddNew: .Fields!PAR = Parqry: .Update
End With
Else
With rst
.MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
End With
End If
Set rst = dbs.OpenRecords et("CRAP", dbOpenDynaset)
If rst.EOF And rst.BOF Then
With rst
.AddNew
.Fields!PAR = Parqry
.Update
End With
Else
With rst
.MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
End With
End If
rst.Close: Set dbs = Nothing
End Sub[/code]

[Stewart Ross]

Hi. In normal circumstances you don't need to open a back-end database to update a table from a front-end database; the front-end database just needs a link to the tables concerned, or suitable connections established at run-time.

Some clarifications which would help in establishing what is going on:

* Are there any permission settings in the back-end database restricting front-end users to read-only views of the tables concerned?

* Have you checked that the PAR value being retrieved from the last record in the Originators table is an appropriate value to add to all the other tables (i.e. that it is not null, it is of the correct type, and it matches the size and any constraints for the fields in which it is being added)?

* Could you tell us what you mean when you say the code adds rows but the number of records does not increase - does this mean that Addnew is doing what it should in all cases but the rows are not being stored on Update?

* Have you stepped through the code by setting break points and testing what is going on as you go from Addnew to setting the PAR field value to Update in each case?

* Can you manually add a row with the PAR value to the back-end tables?

* Are there any relational constraints on the back-end tables such that adding the PAR field value on its own results in a relational error?

By the way, I notice in the code listed that there are several statements placed on a single line in the WITH statements. This makes it more difficult to read and maintain the code, and is not a practice I would encourage for the sake of saving a few lines.

-Stewart

[hapnendad]

Stewart,

All the things you ask below were checked and working properly. I stumbled onto the answer today looking at the help files for the recordcount property.
I discovered the recordcount property only returns the number of records that have been accessed in an open recordset object (did not know that). The error was occurring when I was trying to move to an absolute position in one of the tables I mentioned in my question post.

I have since modified the code to add a movelast then move first statement in the code that access the tables and the problem is has gone away.

What threw me was when I tried to move to an absolute postion of 2 with two rows (records) in the table and an error poped up indicating invalid argument. in the absolute position statement. Some test code I wrote to check the number of records in the target table always returned the number of records as 1 even though there were two rows in the tables. What further confused me was when I did the same thing in the back end database and the test code indicated there were 2 records.

Thanks for the help! Next time I will be sure to read more throughly into the Access help files for the solution.

Jeff

Hi. In normal circumstances you don't need to open a back-end database to update a table from a front-end database; the front-end database just needs a link to the tables concerned, or suitable connections established at run-time.

Some clarifications which would help in establishing what is going on:

* Are there any permission settings in the back-end database restricting front-end users to read-only views of the tables concerned?

* Have you checked that the PAR value being retrieved from the last record in the Originators table is an appropriate value to add to all the other tables (i.e. that it is not null, it is of the correct type, and it matches the size and any constraints for the fields in which it is being added)?

* Could you tell us what you mean when you say the code adds rows but the number of records does not increase - does this mean that Addnew is doing what it should in all cases but the rows are not being stored on Update?

* Have you stepped through the code by setting break points and testing what is going on as you go from Addnew to setting the PAR field value to Update in each case?

* Can you manually add a row with the PAR value to the back-end tables?

* Are there any relational constraints on the back-end tables such that adding the PAR field value on its own results in a relational error?

By the way, I notice in the code listed that there are several statements placed on a single line in the WITH statements. This makes it more difficult to read and maintain the code, and is not a practice I would encourage for the sake of saving a few lines.

-Stewart