Excel as DB Advice: Keep Table on same or different WorkBook?

[judas]

Hello there!

I have been asked for help by a client in designing a simple data entry DB (if you need to know, to record some data about diagnostic imaging studies performed). I made a UserForm that asks for the data and copies it in a table, along with other useforms that allow the user to look for a patient, edit/delete a record, etc. They had done this in Access already but because of licensing it was not deployed.

The whole point was to prevent the user with touching the Data table itself. At the moment, the table is stored in a single sheet of the workbook, and there are other auxiliary sheets where I keep some lists needed for comboboxes, data validation, etc. My question is:

Should I keep the Data table in the same workbook or should I use another external workbook to keep it? What would be the pros/cons of doing it either way?

As the form will be deployed in 5 different branches, I am inclined to keep the DB in an external workbook...that way if I have to make any updates/changes to the userforms then deploying them would be easier, as the users would only need to update the DataEntry (Userforms) file and leave the DB alone...but I would really love to hear your advice and comments on this. What do you thing the best approach would be?

As usual, thank you very very much.

 

[Jon von der Heyden]

One approach I take is to use MS Access to hold data. Then I create an interface in Excel to control the data in that DB. The nice thing about using something like MS Access is that multiple users can write to it from different terminals. Though this involves reasonable ADO knowledge. I would do the same if I were to use a dedicated workbook for the data.

 

[judas]

Jon, thank you very much for your reply. Currently using Access is not possible (and maybe not really necessary, although I agree could be useful in the future) because of licensing (they dont have the full office, nor are willing to buy it, etc.), mantainance issues (there's no one that works there that can really solve problems with access) and also because this is a temporary (nothing is more permanent than temporary, I know) solution while they implement a full-fledge RIS (Radiology Information System) in late 2013. Oh, and most important of all: I don't really know my way around Access either, nor ADO, etc. so I'm trying to keep it simple.

It would be nice if many users could write at the same time, but currently there is only one computer where they input the data, so that would not be a problem (for the time being).

I would write to the external WB using simple VBA referencing techniques...do you think that's smart or do you think the probability of an error/failure outweighs the benefits?

 

[Jon von der Heyden]

My advice is:

If you don't have a need for multi-user (i.e. across multiple terminals) and the data volumes are not huge*, then I would keep it in one workbook. It's just more succinct and easier to manage.

If you do need to go down the route of keeping data in a dedicated workbook then ADO would be my preferred route. Maybe this is just the right opportunity for you to learn ADO too. There is a REALLY good ADO with Excel tutorial here: Using ADO with Excel files - Xtreme Visual Basic Talk We could also prove quite helpful here if you get stuck; there are plenty of peeps here that know their way around ADO.

If you don't feel that you have the time to learn ADO; or just don't feel comfortable with it; then sure use 'simple reference techniques'. Do I think there is high probability of error/failure? Well that depends on how thoroughly you test your code; but yes I do... One function I would like to share with you that may help you avoid some troubles is my SetOpenWorkbook function: Open and Set a Reference to a Workbook

* I can't really qualify 'huge'. Really it is up to you to work out. It depends on what is being done with the data...

 

[judas]

Jon, Hi!

So I have decided to follow your advice and keep everything on the same workbook. I've had no problems so far. I also checked out the function you mentioned and bookmarked it. It will be very useful in the future. Thank you very much for taking the time to post it and improving it.

When I update the code, I will use the following code I found in John Walkenbachs Excel 2010 Power Programming with VBA. It will update the code in the original file without the need to update or transfer the new data. (I hope it's ok to post this here)

<CODE>

Sub BeginUpdate()
Dim Filename As String
Dim Msg As String
Filename = "UserBook.xlsm"

' Activate workbook
On Error Resume Next
Workbooks(Filename).Activate
If Err <> 0 Then
MsgBox Filename & " must be open.", vbCritical
Exit Sub
End If

Msg = "This macro will replace Module1 in UserBook.xlsm "
Msg = Msg & "with an updated Module." & vbCrLf & vbCrLf
Msg = Msg & "Click OK to continue."
If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then
Call ReplaceModule
Else
MsgBox "Module not replaced,", vbCritical
End If
End Sub

Sub ReplaceModule()
Dim ModuleFile As String
Dim VBP As VBIDE.VBProject

' Export Module1 from this workbook
ModuleFile = Application.DefaultFilePath & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("Module1") _
.Export ModuleFile

' Replace Module1 in UserBook
Set VBP = Workbooks("UserBook.xlsm").VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("Module1")
.Import ModuleFile
End With

' Delete the temporary module file
Kill ModuleFile
MsgBox "The module has been replaced.", vbInformation
Exit Sub

ErrHandle:
' Did an error occur?
MsgBox "ERROR. The module may not have been replaced.", _
vbCritical
End Sub
</CODE>

 

[Jon von der Heyden]

Hi Judas; thanks for the update, I'm sure someone else will benefit from this too.

 

[Fazza]

FYI, Judas, you mentioned using Access is not possible due to licencing. Even without using Access, the data can be stored in an mdb file. For example, Excel can read/write to the mdb file using ADO.

 

[Jon von der Heyden]

FYI, Judas, you mentioned using Access is not possible due to licencing. Even without using Access, the data can be stored in an mdb file. For example, Excel can read/write to the mdb file using ADO.

Yup; like this: Create an Access Database (on the fly) using VBA and SQL - By Ken Puls

 

[rs2k]

Judas,

Another option would be to use MySQL (it's free) to hold your data and then import/export into Excel, I personally have never used this (Excel that is, I use Access), but I thought it might be another option for you.

Cheers
Colin

Edit: To clarify I have never tried it with Excel, only Access.