Using excel as a database

[SQUIDD]

Hi All

So i have quite a complicated project already built in excel, so i really would like to stick with excel.

I want to have 1 workbook that harbours all of my data, and then simply an my app to modify the data, my app will have all the code etc.

Now i understand the above is easy and i could do this without any issues,

however, i want a multi user app, or several apps that can copy data from and modify the data file.

Before i start this fairly large project, i wnt to make sure im heading off in the right direction.

I guess my real issue is if 2 people try to modify the the data file at the same time.

what are the issues surrounding this, or, should i be considering building something that pre-checks if anyone already has the data file open and put the last change in a que.

the changes will always be extremly small and most of the time it will just be grabbing reports to view.

my plan to view reports from the data was to simply open the data file, copy the data into the app so it can sort and filter each report, so thats a quick open and, grab and close of the data file.
my plan to amend/add new data(only ever one row at a time) was top open the data file, find the correct row of data and simply update with the new info, save and close.

Any help would be great since i want to start off in the right direction.
Im thinking right now i can delfinently put a check if file is already being modified thing in. Then update at the 1st oppertunity.

OR MAYBE

I need to consider time stamping all changes and removing the older data?

Dave

 

[fadee2]

Hi Squidd,
Will your application be designed on vba or any other language?
if it is VBA you are talking about then may be...

lets suppose your database workbook will be available on a shared resource on a network.
for the reporting part, I would suggest using powerquery to bring in the data from database to the report worksheet.

as for updating the records, I would suggest keeping a copy of database for each user, and once they are done with the edit/save then your "something that pre-checks if anyone already has the data file open and put the last change in a que." procedures comes into play.

 

[SQUIDD]

Hi Fadee2

Thanks for that, this makes sense to me, also says that i am at least on the correct thinking path, yes it will be VBA. And yes, the data file is in shared location.

Thanks

Dave

 

[dmt32]

Hi,

What you want to do is quite achievable albeit in a limited way.

If only want to use Excel as your database then you would create a workbook that has one table (sometimes referred to as a flat file database) to hold all your data. This workbook is then placed on a shared network drive where data entry workbook(s) containing all necessary VBA read/write the data.

I did such a project for a charity my daughter worked for about 10 years ago – she had 250 staff submitting timesheets each week so I created an AddIn that enabled staff to enter their weekly data and this would write to a central workbook (A database engine like Access was not an option).

The Application also allowed staff to submit holiday requests & view their annual holiday card. All worked ok without any conflicts but to make it work involved extensive coding so before you go down this road, I would suggest it may be worth your while heading over to the Access forum to get so insight there.

Dave

 

[fadee2]

You are welcome.
Actually this is something that I did myself quite sometime ago, the difference is, I only had 1 user to update/edit/save the data on a single pc and then update the master data file on the network. So I provided my viewers with workbooks that would look for a master data file on the network and bring in the data to their individual workbooks, for viewing.
But as @dmt32 mentioned, yes, it will require extensive coding to work.
OR
you can use Microsoft ActiveX Data Objects 6.1 Library and bring in SQL commands to do all the work.

 

[SQUIDD]

Hi Fadee2 and DMT32

Thankyou both for your insight.

I have started to simply work out the general updating of the data, checking out what errors i will get.
I have some basic code below. I have tried this with 2 users updating the file just as a test.

I do however get quite a nasty error sometimes. I believe the error is that after my code has checked if the file is open, if it isnt open, the code runs to open the file, but within that split second, the file has already opened by the second user. This causes a windows message asking information about saving a copy of the data.

It very nearly works, but not sure how to deal with this?

Any more help here would be great, if possible i want to contine in excel for a few reasons.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub db()
c = InputBox("enter name lowercase, alan Or dave")

For A = 1 To 25
goround:
    If IsFileOpen("S:\Files\DATA\data.xlsx") Then
        Application.Wait (Now + TimeValue("00:00:01"))
        MsgBox "file open, press enter to try again"
        GoTo goround
    Else
        b = InputBox("enter some data")
        Workbooks.Open FileName:=Application.ActiveWorkbook.Path & "\data.xlsx"
        If c = "dave" Then Range("A" & A) = b
        If c = "alan" Then Range("b" & A) = b
        Workbooks("data.xlsx").Close SaveChanges:=True
    End If
Next A

MsgBox ("done")
End Sub

 

[dmt32]

Hi,
Try this updated version of code I have used in number of applications that have a workbook as central database

Place in standard module

Function DataBaseOpen(ByVal FileName As String, Optional ByVal ReadOnly As Boolean, Optional ByVal Password As String = "") As Workbook
    Dim Response As VbMsgBoxResult
    Dim FileInUse As Boolean
'dmt32 Nov 2020
OpenFile:
    If Not Dir(FileName, vbDirectory) = vbNullString Then
        If Not ReadOnly Then
'check if file already open read/write
            On Error Resume Next
            Open FileName For Binary Access Read Lock Read As #1
            Close #1
            FileInUse = CBool(Err.Number > 0)
            On Error GoTo 0
            If FileInUse Then
'read / write file in use
                Response = MsgBox("File Is Open For Editing By Another User." & Chr(10) & _
                "Do You Want To Try Again?", 37, "File In Use")
                If Response = vbRetry Then
                    GoTo OpenFile
                Else
                    Set DataBaseOpen = Nothing
                    Exit Function
                End If
            End If
        End If
        Set DataBaseOpen = Workbooks.Open(FileName, ReadOnly:=ReadOnly, Password:=Password)
    Else
        MsgBox FileName & Chr(10) & "File / Folder Not Found", 16, "Not Found"
        Set DataBaseOpen = Nothing
    End If
End Function

and to use it in your code

Sub Test()
    Dim wbDatabase As Workbook
    Dim strFileName As String
    Const DatabasePassword As String = "open1234"
    
    strFileName = "C:\MyFolder\MyFile.xlsx"
    
    Set wbDatabase = DataBaseOpen(strFileName, False, DatabasePassword)
    If wbDatabase Is Nothing Then Exit Sub
    
    'rest of code
    
End Sub

If you only need to open the database workbook to read data from it then set the second argument (ReadOnly) to True as this will reduce potential multi-user conflicts.

Do be mindful though that you cannot have the database workbook open read / write mode whilst users need to write data to it.


Hope Helpful

Dave

 

[SQUIDD]

hi all

thanks for the last post dmt, i did try this but was recieving the same errors as before.

However, i had another thought, and found a completly different solution.
I have tested my solution with multi users, i built loop code to add data to the file to really push my solution hard accross multi users all running data to my file at the same time.
i have not had any errors or data loss.

my solution was as follows, i have added the ADD_DATA as a pure example for anyone to see how i excecuted this. In reality, i will have lost of macros simply using the LOCK_FILE_AND_OPEN and UNLOCK_FILE_SAVE_AND_CLOSE macros.

thanks all again for your help

one last thing, this works for me, since the file will always only take a short time to add/amend the data. so i havent added any messages to tell users the file is locked etc.

Sub ADD_DATA()
    Application.ScreenUpdating = False: Application.EnableEvents = False: Application.DisplayAlerts = False: Application.Calculation = xlCalculationManual
    C = InputBox("enter data")
    LOCK_FILE_AND_OPEN
    Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = C
    UNLOCK_FILE_SAVE_AND_CLOSE
    MsgBox ("ADDED")
    Application.ScreenUpdating = True: Application.EnableEvents = True: Application.DisplayAlerts = True: Application.Calculation = xlCalculationAutomatic
End Sub
Sub LOCK_FILE_AND_OPEN()
TRYAGAIN:
If Dir(Application.ActiveWorkbook.Path & "\DB", vbDirectory) <> "" Then GoTo TRYAGAIN
    MkDir Application.ActiveWorkbook.Path & "\DB"
    Workbooks.Open FileName:=Application.ActiveWorkbook.Path & "\data.xlsx"
End Sub
Sub UNLOCK_FILE_SAVE_AND_CLOSE()
    Workbooks("data.xlsx").Close SaveChanges:=True
    RmDir Application.ActiveWorkbook.Path & "\DB"
End Sub