Hello everyone.
After some years using just Access for my application (everithing going fine),
I am now testing the switch to SQL server 2005.
I already have an application divided in two parts (Data and Program),
so I used the "Microsoft SQL Server Migration Assistant for Access" to port my "Data part" to SQL Server, and I changed the link to SQL using an ODBC driver.
I didn't expect everithing to work perfectly at the first go, so now I am facing a couple of problems, that I hope you might help to solve.
The first thing is: how do I use transactions?
I mean: in access<->access all I needed to do was to start a transaction
with something like [workspace.begintrans] and end it with [workspace.commit] (or .rollback in case of an error)
I find that this is good also for access<->SQL, but only if I limit the operations under transaction...
To avoid getting the error, I just commented all instructions regarding the use of workspace transactions,
and the code just go without problems, but I thought this is not the "best solution" for this problem.
Is there a source for documentation regarding the best practices using transaction in this kind of connection?
The second thing is: How do I avoid facing "concurrent actions" errors?
That is: I am going to use SQL server because it's more suitable for a multiuser environment... but just in a simple test phase, i keep getting errors like this:
"3197 - The microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time".
I'm sure that might be caused by the way I put down my code, or by the way i'm connected to my SQL 2005 server...
so I am asking if there is some documentation to better put down my code
and avoid errors like that.
After some years using just Access for my application (everithing going fine),
I am now testing the switch to SQL server 2005.
I already have an application divided in two parts (Data and Program),
so I used the "Microsoft SQL Server Migration Assistant for Access" to port my "Data part" to SQL Server, and I changed the link to SQL using an ODBC driver.
I didn't expect everithing to work perfectly at the first go, so now I am facing a couple of problems, that I hope you might help to solve.
The first thing is: how do I use transactions?
I mean: in access<->access all I needed to do was to start a transaction
with something like [workspace.begintrans] and end it with [workspace.commit] (or .rollback in case of an error)
I find that this is good also for access<->SQL, but only if I limit the operations under transaction...
To avoid getting the error, I just commented all instructions regarding the use of workspace transactions,
and the code just go without problems, but I thought this is not the "best solution" for this problem.
Is there a source for documentation regarding the best practices using transaction in this kind of connection?
The second thing is: How do I avoid facing "concurrent actions" errors?
That is: I am going to use SQL server because it's more suitable for a multiuser environment... but just in a simple test phase, i keep getting errors like this:
"3197 - The microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time".
I'm sure that might be caused by the way I put down my code, or by the way i'm connected to my SQL 2005 server...
so I am asking if there is some documentation to better put down my code
and avoid errors like that.