Updating data from excel as frontEnd to Database

[pedie]

Hi, I am using Bill's code from ebook. I want to confirm and seek expert/expirienced users advice on how Excel and Access works.

Users updates information from excel using it as front end and access as Database to store all DATAS.
I want to make sure if 2 or more users updates at the same time from thier respective computer to database in network drive will there be any error? Do i have to do any special type of settings for this?


Please advice.

Thanks alot in advance.

[/FONT]
[FONT=Courier New]Sub CallAddTransfer()
' Used to call the code from page 480
Dim WS As Worksheet
Dim Qty As Integer
Set WS = Worksheets("AddRecords")
FinalRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
Ctr = 0
For i = 7 To FinalRow
Style = Cells(i, 1).Value
FromStore = Cells(i, 2).Value
ToStore = Cells(i, 3).Value
Qty = Cells(i, 4).Value
Ctr = Ctr + 1
Application.StatusBar = "Adding Record " & Ctr
AddTransfer Style, FromStore, ToStore, Qty
Next i
Application.StatusBar = False
MsgBox Ctr & " records added."
End Sub
Sub AddTransfer(Style As Variant, FromStore As Variant, ToStore As Variant, Qty As Integer)
' Page 480
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
 
MyConn = ThisWorkbook.Path & Application.PathSeparator & "Transfers.mdb"
MyConn = "Driver=Microsoft Access Driver (*.mdb);DBQ=" & MyConn
 
' open the connection
Set cnn = New ADODB.Connection
With cnn
'.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
 
' Define the Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
 
' open the table
rst.Open Source:="tblTransfer", _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
 
' Add a record
rst.AddNew
 
' Set up the values for the fields. The first four fields
' are passed from the calling userform. The date field
' is filled with the current date.
rst("Style") = Style
rst("FromStore") = FromStore
rst("ToStore") = ToStore
rst("Qty") = Qty
rst("tDate") = Date
rst("Sent") = False
rst("Receive") = False
 
' Write the values to this record
rst.Update
 
' Close
rst.Close
cnn.Close
End Sub

 

[SydneyGeek]

Usually, if one user is updating and another tries to connect, they will be told that they can't. Excel and Access tend to lock each other exclusively.

If your users get such a message, tell them to wait a few seconds and try again. Given the speed with which records can be updated, it's unlikely to take more than a second or two for someone to connect, update and disconnect.

Denis

 

[pedie]

Thanks alot for clarification.

So is it safe to now think that there is no other way?

 

[SydneyGeek]

I don't think so; at least, I haven't come across it.

There are a couple things to note from this:
1. Users can't simultaneously edit the same record because they can't simultaneously connect, that I know of.
2. Make sure that you connect, update, and disconnect. I think your code is already doing that.
3. Users need to be aware that they may need to try more than once. How big a problem this is, will depend on how often they are submitting changes and how many simultaneous users you have on the system.

Denis

 

[pedie]

Denis, thanks once again. I did tried in real time and it does conflict but does solve the problem by itself i mean I dont know how but event though users are updating at the same time it does not error so I think Access it much much better using a shared workbook.


Thanks again.

 

[SydneyGeek]

I agree. I will never use a shared workbook.

Denis