Hi All,
I'm looking for advice (I'm sure what this does is bad) on if there is some flag/option I'm missing or if forms need to be rewritten/changed to unbound forms.
I create a simple table in SQL server and also an Access database.
ID : Number (Unique Index)
Short Code : Text
(and for SQL backend rowversion/timestamp)
I set up linked tables via ODBC to the SQL version and normal linked table to the access database.
So I create a front end MDB (lets say TEST.MDB)
If I use a system generated form which shows the 2 fields and has navigation buttons at the bottom I can move through and edit data. No problems. If I copy this simple mdb (with the form and linked tables) and call it TEST2.MDB.
So I run both up so I can test multi-user and see the effects. If I edit (and change) a record in the linked access table and move off the record the 2nd session (TEST2.MDB) will reflect that if I move onto that record. However if I do the same thing with the linked SQL table it does not reflect the changes (unless I attempt to edit the record then I get a message saying it had changed), or I do F5 to force a refresh of the entire recordset.
Now whilst I know the above is a simple test (and no doubt extremely bad practise), it was something I wanted to check because an application I work on has a number of forms like this (that load a full recordset of say customers, suppliers etc.) and then display a record for editing but has manual navigation buttons (for next/prev record) and we need to move to SQL server backend.
I'm sure if its Access --> Access it does something clever (but no doubt bad for multi-user / scaling), that the SQL can't do or shouldn't do.
So what would be the best plan for these forms (I used the simple example above just as a proof of 'problem')
Cheers
I'm looking for advice (I'm sure what this does is bad) on if there is some flag/option I'm missing or if forms need to be rewritten/changed to unbound forms.
I create a simple table in SQL server and also an Access database.
ID : Number (Unique Index)
Short Code : Text
(and for SQL backend rowversion/timestamp)
I set up linked tables via ODBC to the SQL version and normal linked table to the access database.
So I create a front end MDB (lets say TEST.MDB)
If I use a system generated form which shows the 2 fields and has navigation buttons at the bottom I can move through and edit data. No problems. If I copy this simple mdb (with the form and linked tables) and call it TEST2.MDB.
So I run both up so I can test multi-user and see the effects. If I edit (and change) a record in the linked access table and move off the record the 2nd session (TEST2.MDB) will reflect that if I move onto that record. However if I do the same thing with the linked SQL table it does not reflect the changes (unless I attempt to edit the record then I get a message saying it had changed), or I do F5 to force a refresh of the entire recordset.
Now whilst I know the above is a simple test (and no doubt extremely bad practise), it was something I wanted to check because an application I work on has a number of forms like this (that load a full recordset of say customers, suppliers etc.) and then display a record for editing but has manual navigation buttons (for next/prev record) and we need to move to SQL server backend.
I'm sure if its Access --> Access it does something clever (but no doubt bad for multi-user / scaling), that the SQL can't do or shouldn't do.
So what would be the best plan for these forms (I used the simple example above just as a proof of 'problem')
Cheers