Excel behave like a multi-user database?

[Sir Babydum GBE]

Hi,

Currently I'm using excel as a database (we don't have Access).

I've set up sheets where the user completes a form and clicks "submit". This moves that info into a single row on a hidden worksheet within the same workbook. Each agent has his own w/book, and information is consolidated by myself at the end of each week from the many w/books containing records.

Can Excel behave like a database in that multiple users can open one w/book simultaneosly, complete forms where the records are submitted to a datasheet without risk of one persons records being overwritten by another.

I can only uuse excel (and VBA) - no third party apps.

Thanks

[Sir Babydum GBE]

All users open a shared workbook and enter records onto a form. The form has a field that auto-populates with the user-id of the logged-on user. When the user submits a record, the code looks at the user ID and checks to see if there is a sheet named afte that ID. If not it creates one and that users records only ever go to the sheet bearing his ID - thus no conflicts.

As I write I seem to remember you can't add sheets to a shared workbook - not even with code?

If not, not the end of the world if I have to create all the sheets first (it's just that users get added and deleted from time to time.

Either way, easier to consolidate from one workbook than many...

Any ideas

[Bob Phillips]

Shared workbooks are evil personnified, don't do it.

Why not use an Access database with an Excel addin front-end. Using ADO it is simple to maintain the database.

[Sir Babydum GBE]

Shared workbooks are evil personnified, don't do it.

Why not use an Access database with an Excel addin front-end. Using ADO it is simple to maintain the database.Because the company I work for declared Access databases illegal about two years ago - we can't access Access!

What's so smelly about sharing?

[Bob Phillips]

You don't need Access to access Access, even accessively.

You can read and write to an .mdb using ADO without ever installing Access.

[Bob Phillips]

apart from the fact that shared workbooks are boke, they are also severely crippled. Look at 'Features that are unavailable in shared workbooks' in help.

[Sir Babydum GBE]

You don't need Access to access Access, even accessively.

You can read and write to an .mdb using ADO without ever installing Access.
The systems here are locked down - we don't have administrator rights and cannot install extras (unless ADO is part of ordinary Excel?). Only the IS guys have rights to use database-type systems and I work within a department that doesn't have, and can't get those rights. The problem with the IS guys is that there is a long turn-araound to get anything done, and we're running quick-fire trials where time isn't a luxury we have.

[Sir Babydum GBE]

apart from the fact that shared workbooks are boke, they are also severely crippled. Look at 'Features that are unavailable in shared workbooks' in help.Point taken.

i can't "Use a data form to add new data" - so that pretty much puts a stop to my idea.

It's a shame that MS don't work on an Excel/Access hybrid.

[Bob Phillips]

No its not. An Excel/Access hybrid would be a monstrosity, the worst bits of both that would not work properly.

Try this code to see if it works at your place.



Option Explicit

Const DBPath As String = "C:\Test.mdb"

Const adBigInt As Long = 20
Const adBinary As Long = 128
Const adBoolean As Long = 11
Const adChar As Long = 129
Const adCurrency As Long = 6
Const adDate As Long = 7
Const adDBTimeStamp As Long = 135
Const adDecimal As Long = 14
Const adDouble As Long = 5
Const adGUID As Long = 72 'ReplicationID
Const adIDispatch As Long = 9
Const adInteger As Long = 3
Const adLongVarBinary As Long = 205
Const adLongVarChar As Long = 201 'Memo (Access 97)/Hyperlink (Access 97)
Const adLongVarWChar As Long = 203 'Memo (Access 2000 (OLEDB))/Hyperlink (Access 2000 (OLEDB))
Const adNumeric As Long = 131 'Decimal (Access 2000 (OLEDB))
Const adSingle As Long = 4
Const adSmallInt As Long = 2
Const adUnsignedTinyInt As Long = 17 'Byte
Const adVarBinary As Long = 204 'ReplicationID (Access 97)
Const adVarChar As Long = 200 'Text (Access 97)
Const adVariant As Long = 12
Const adVarWChar As Long = 202 'Text (Access 2000 (OLEDB))
Const adWChar As Long = 130

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Sub BDTest()
Call CreateAccessDatabase
Call CreateAccessTable
Call AddData
MsgBox GetData
End Sub

'----------------------------------------------------------------
Sub CreateAccessDatabase()
'----------------------------------------------------------------
Dim oADOCat As Object

Set oADOCat = CreateObject("ADOX.Catalog")

oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath

Set oADOCat = Nothing
End Sub



'----------------------------------------------------------------
Sub CreateAccessTable()
'----------------------------------------------------------------
Dim oADOCat As Object
Dim oTable As Object

Set oADOCat = CreateObject("ADOX.Catalog")
oADOCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath
Set oTable = CreateObject("ADOX.Table")

' Create a new Table object.
With oTable
.Name = "Contacts"
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append "FirstName", adVarWChar
.Append "LastName", adVarWChar
.Append "Phone", adVarWChar
.Append "Notes", adLongVarWChar
End With
End With

' Add the new Table to the Tables collection of the database.
oADOCat.Tables.Append oTable

Set oTable = Nothing
Set oADOCat = Nothing
End Sub

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

Function GetData() As String
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBPath

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
GetData = ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
GetData = "No records returned."
End If

oRS.Close
Set oRS = Nothing
End Function

[Sir Babydum GBE]

Thanks for your help on this, but none of the macros work. I get a "Can't access C:Test.mdb" error on a couple, and a "The MS Jet database engine cannot open Test MDB..." message on the others.

[lucas]

Works fine here on a local machine....no network.

[unmarkedhelicopter]

There is a simple Excel - Access test set up over at JMT (it may be you just can not create .mdb's and a ready made one may work)
Try the test database and code at : http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1143633690/#9