Access 2003 <=> SQL 2005 Best Practices (1 Viewer)

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.

Hi there


SQL SERVER Transactions and rollbacks are called within the sql code, mostly they will be within a stored procedure.

See here for a reference and examples

http://msdn.microsoft.com/en-us/library/ms188929.aspx

The concurrency issues you are seeing are probably because you have a BIT column (TRUE OR FALSE) in your table. Access true or false columns are either true or false but however sql server BIT columns are either true,false or NULL this confuses access because it cannot handle the tri-state. If any BIT columns in your table are NULL then update them to 0 and it should fix the issue.

If this doesn't fix it then there are a few other causes of this error.

Hope this helps

Thanks for your suggestions about the transact-sql commands.
Then, I will have to manage the transaction using sql code or workspaces
depending on the type of connection, because my application can connect
and work on either an .mdb file (as done until now) or an SQL database.

About the other error, as you suggested to check, I effectively have a field
which is boolean, and I was not updating it, because I am using the Deafult value
property of the table to a False value... that eventually the SQL table cannot set.
I added a row to force this field to false, but the error still pops up.
This is the code that triggers the error:

Public Function SetMyTabRow(Sig As String, Des As String, Orig As String, _
Tip As String, ArT As String, Util As Single, _
Ris As Single, Pes As Single, Note As String, _
Form As String, VerF As Byte) As Integer
Dim Qd As QueryDef
Dim Rs As Recordset

On Error GoTo Err_SetMyTabRow
Set Qd = db.QueryDefs("qm_GP_IsTabRow")
Qd!Prev = P_prev
Qd!Sig = Sig
Set Rs = Qd.OpenRecordset(dbOpenDynaset)

If Rs.RecordCount > 0 Then
Rs.Edit
Rs!VARIANTE = P_Var
Rs!OPZIONE = P_Opz
If Des <> "" Then Rs!DES = Des
If Orig <> "=" Then Rs!ORIG = Orig
If Tip <> "" Then Rs!TIPO = Tip
If ArT <> "" Then Rs!AREAT = ArT
If Util <> -1 Then Rs!UTILE = Util
If Ris <> -1 Then Rs!RISCHIO = Ris
If Pes <> -1 Then Rs!PESO = Pes
If Note <> "=" Then Rs!NOTE = Note
If Form <> "=" Then
Rs!NOME_F = Form
Rs!VERFOR = VerF
End If
''' THE FOLLOWING ROW HAS BEEN ADDED TO SET TO FALSE THE ONLY BOOLEN VALUE IN THIS TABLE
Rs!SELECTED = False

Rs.Update

Else

Qd.Close
Set Qd = db.QueryDefs("qm_GP_AddMyTabRow")
Qd!Prev = P_prev
Qd!Sig = Sig
Qd!VAR = (P_Var)
Qd!Opz = (P_Opz)
Qd!Des = Des
Qd!Orig = Orig
Qd!Tip = Tip
Qd!ArT = ArT
Qd!Util = Util
Qd!Ris = Ris
Qd!PESO = Pes
Qd!NOTE = Note
Qd!NomeF = Form
Qd!VerF = VerF
Qd.Execute dbFailOnError
End If

SetMyTabRow = True

Exit_SetMyTabRow:
Exit Function

Err_SetMyTabRow:
MsgBox "Error " & Err & " in funcion SetMyTabRow" & fnCR(1) & Error$, _
vbExclamation, "Application - Procedure"
SetMyTabRow = False
Resume Exit_SetMyTabRow
End Function

The scope of this function is easy: with the first query I look for a corrispondence:
if there is corrispondence, I modify the record (editing the same recordset used as query), if there is no corrispondence, I insert a record (using an append query).
Note that when the procedure inserts the new record, I get no errors,
which instead I get if I the procedure try to update the record.
The error is triggered by the Rs.Update statement (marked above).

Thanks for any suggestion you may give.

PS: sorry but this post editor cleard all my indentations to make more readable the code

Hi again,

Ok firstly you should know that SQL SERVER does not actually store it's boolean values as true,false or null they are actually stored as 1,0 or null.

Can you run the following query against the table you are updating and post the results please? I don't know what your table is called so I will call it beer


Does your table have a field with a timestamp datatype in it?

Here is a KB article about the BIT field NULL issue

http://support.microsoft.com/default.aspx?scid=kb;en-us;318882&Product=acc

Thank you again for the support
I was watching my code and I wanted to give a try
to a different approach:
as you can see, the second part of the code uses an append query
to add a record, but in the first part, it uses the same recordset
used to find the record as a base to start an edit - update operation.
I then created a new "updare query" with the same set of parameters,
and now the procedure works fine.
I post the new code here, just for reference:

Public Function SetMyTabRow(Sig As String, Des As String, Orig As String, _
Tip As String, ArT As String, Util As Single, _
Ris As Single, Pes As Single, Note As String, _
Form As String, VerF As Byte) As Integer

Dim Qd As QueryDef
Dim Rs As Recordset

On Error GoTo Err_SetMyTabRow
Set Qd = db.QueryDefs("qm_GP_IsTabRow")
Qd!Prev = P_prev
Qd!Sig = Sig
Set Rs = Qd.OpenRecordset(dbOpenDynaset)
If Rs.RecordCount > 0 Then
Qd.Close
Set Qd = db.QueryDefs("qm_GP_UpdateMyTabRow")
Qd!uPrev = P_preventivo
Qd!uSig = Sig
Qd!uOrig = IIf(Orig = "=", NtoS(Rs!ORIG), Orig)
Qd!uTipA = IIf(Tip = "", NtoS(Rs!TIPO), Tip)
Qd!uVar = (P_Var)
Qd!uOpz = (P_Opz)
Qd!uDes = IIf(Des = "", NtoS(Rs!DES), Des)
Qd!uUti = IIf(Util = -1, NtoZ(Rs!UTILE), Util)
Qd!uRis = IIf(Ris = -1, NtoZ(Rs!RISCHIO), Ris)
Qd!uPes = IIf(Pes = -1, NtoZ(Rs!PESO), Pes)
Qd!uArT = IIf(ArT = "", NtoS(Rs!AREAT), ArT)
Qd!uNote = IIf(Note = "=", NtoS(Rs!NOTE), Note)
Qd!uSel = False
Qd!uNoF = IIf(Form = "=", NtoS(Rs!NOME_F), Form)
Qd!uVerF = IIf(Form = "=", NtoZ(Rs!VERFOR), VerF)
Qd.Execute dbFailOnError
Else
Qd.Close
Set Qd = db.QueryDefs("qm_GP_AddMyTabRow")
Qd!Prev = P_prev
Qd!Sig = Sig
Qd!VAR = (P_Var)
Qd!Opz = (P_Opz)
Qd!Des = Des
Qd!Orig = Orig
Qd!Tip = Tip
Qd!ArT = ArT
Qd!Util = Util
Qd!Ris = Ris
Qd!PESO = Pes
Qd!NOTE = Note
Qd!NomeF = Form
Qd!VerF = VerF
Qd.Execute dbFailOnError
End If
SetMyTabRow = True

Exit_SetMyTabRow:
Exit Function

Err_SetMyTabRow:
MsgBox "Error " & Err & " in funcion SetMyTabRow" & fnCR(1) & Error$, _
vbExclamation, "Application - Procedure"
SetMyTabRow = False
Resume Exit_SetMyTabRow
End Function

Il Blu color i left the forced change of that boolean field, just to avoid problems elsewhere.
So at the end it seemed that using the query to search AND update
is somehow difficult (or impossible) to handle...
Now I will have to correct just a quantity of functions, and everything will be fine, hopefully.

Anyway, I executed your query, and the result was 2 rows
0 2204
-1 650
and was executed without errors.
I will however pay attention to those "pesky" boolean fiels

PS: MyTable is not called beer, but I sure owe you one.
thanks again for the support.

No problems, you are welcome.

As I mentioned in my first post there are a few reasons for that " you and another user are attempting to change the same data" error / bug. The most common one is the BIT field problem but yours was indeed using the same recordset for inserts and updates.

Well done for having a look at your code and trying it through another method and good luck with updating the rest of your functions