how to facilitate multiple users.

[PeterNZ]

Hi I have developed a speedsheet that uses a form for data entry and has another worksheet in same workbook used to import data from first sheet. (as a master log) and it works really well for just one user at a time. Problem is now I need to have up to 10 people able to access it at same time.

I'm thinking that i should give each person a copy of workbook and let each enter their data in their own sheet and then they could click command buton to send(and append ) that data to a Central(common) summary sheet located on the network.

How to do this ? given network location to be say K:\engshare\BreakinLog

Please assist.

[Bob Phillips]

Use a database, yes even Access, and update that from a distributed addin.

[PeterNZ]

Great I have have already created an Access db with all the fields from my form in excel. As was thinking that access db may be better way to go with multiple users. However I'm not sure what you mean by ADDIN
Do Use excel form to add to access?
or Create Access form and split db somehow.
In either case not sure how to set up path to db to save to.

PS: Have never used access before.

[Bob Phillips]

Not exactly. I mean a special type of workbook, essentially a workbook that is just code, and is not visible. The addin has all of the user input forms, captures the data, updates the database and so on.

It is late here now, but tomorrow we can expand this further if you wish. How do you envisage the users creating the data, in a spreadsheet, or via a classic form?

[Bob Phillips]

BTW, don't worry about Access, you don't have to go anyhwre near it, you access (little a') it via ADO, which is something we can cover tomorrow.

[PeterNZ]

Ok sounds cool look forward to learning that. I want the users to use the form only for data entry. Just to maintain integrity of the data.

Thanks XLD.

[PeterNZ]

Wow Ok I found out how to do it. No need to show me anymore thanks anyway XLD

[Aussiebear]

Others may be still interested in a solution Peter. Would you care to post yours?

[PeterNZ]

Sure I will give you the code I found here it is

Found it on Ron De Bruin's website all credit to him!

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

a few minor changes and bingo works like a charm.