By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,505 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,505 IT Pros & Developers. It's quick & easy.

Add record to front end linked table.

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1. Sub TBLApprovalAddRecord(Parqry)
  2. Dim dbs As Database: Dim rst As Recordset: Set dbs = CurrentDb
  3. Set rst = dbs.OpenRecordset("Originator")
  4. rst.MoveLast
  5. Parqry = rst("PAR").Value
  6. rst.Close: Set rst = Nothing
  7. Set rst = dbs.OpenRecordset("Approvals", dbOpenDynaset)
  8. If rst.EOF And rst.BOF Then
  9. With rst
  10. .AddNew: .Fields!PAR = Parqry: .Update
  11. End With
  12. Else
  13. With rst
  14. .MoveLast
  15. .AddNew
  16. .Fields!PAR = Parqry
  17. .Update
  18. End With
  19. End If
  20. rst.Close: Set rst = Nothing
  21. Set rst = dbs.OpenRecordset("PARTeam", dbOpenDynaset)
  22. If rst.EOF And rst.BOF Then
  23. With rst
  24. .AddNew
  25. .Fields!PAR = Parqry
  26. .Update
  27. End With
  28. Else
  29. With rst
  30. .MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
  31. End With
  32. End If
  33. rst.Close: Set rst = Nothing
  34. Set rst = dbs.OpenRecordset("TimeLine", dbOpenDynaset)
  35. If rst.EOF And rst.BOF Then
  36. With rst
  37. .AddNew: .Fields!PAR = Parqry: .Update
  38. End With
  39. Else
  40. With rst
  41. .MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
  42. End With
  43. End If
  44. Set rst = dbs.OpenRecordset("CRAP", dbOpenDynaset)
  45. If rst.EOF And rst.BOF Then
  46. With rst
  47. .AddNew
  48. .Fields!PAR = Parqry
  49. .Update
  50. End With
  51. Else
  52. With rst
  53. .MoveLast: .AddNew: .Fields!PAR = Parqry: .Update
  54. End With
  55. End If
  56. rst.Close: Set dbs = Nothing
  57. End Sub
Apr 1 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Apr 1 '08 #2

P: 7
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
Apr 2 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.