pcc
Well-known Member
- Joined
- Jan 21, 2003
- Messages
- 1,353
I am creating have a multi-user application in Excel that writes data to, and reads data from, an Access database. There is a master data table in Access that can be updated by any of these users. However, I suspect that if one user is updating the table, then others will not be able to. In order to avoid problems, I would like to know how to tell if a table is 'locked', so that, if it is, I can put a delay in the updating code that waits for it to be free again - a bit like (in Excel)
MY VB code to update the table in Access is
Anyone advise on how to do this, please? I an fairly new to Access.
Cheers
Code:
1:
Workbooks.Open Filename:="blah blah.xls"
If ActiveWorkbook.ReadOnly = True Then
ActiveWorkbook.Close False
GoTo 1
End If
MY VB code to update the table in Access is
Code:
Sub add_data()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
strDB = Range("file_location").Value ' name of Acess databse
table_name = Range("Table").Value ' name of table in the database
Sheets("Data").Activate
ActiveSheet.AutoFilterMode = False
lr = [a1].CurrentRegion.Rows.Count
var1 = Cells(1, 1): var2 = Cells(1, 2): var3 = Cells(1, 3): var4 = Cells(1, 4)
'Open the connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDB
End With
'Define the recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
'**********************************************
'Open the table
rst.Open Source:=table_name, ActiveConnection:=cnn, CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, Options:=adCmdTable
'**********************************************
For r = 2 To lr
rst.AddNew
rst(var1) = Cells(r, 1)
rst(var2) = Cells(r, 2)
rst(var3) = Cells(r, 3)
rst(var4) = Cells(r, 4)
rst.Update
Next r
rst.Close
cnn.Close
End Sub
Anyone advise on how to do this, please? I an fairly new to Access.
Cheers