What should i use? Excel or Acces.

[Botje]

Hej there,

I'm on this forum for about 2 weeks now.
Busy with Excel Userform trying to get it done!
And thanks to the people here it looks lovely and it works..

But... I have a problem!

When 2 or more people simultaneously open my Useform, fill it in.
And then save it, it will give errors because the files are different because of the data that is filled in..

What i want to achieve is the following:
About 500 people should be able to fill in the form at all time without getting "can't save the file" errors.
​

I was wondering...

Did I make a mistake by using Excel?
Or should I make an form in Acces that automaticly imports the Data from Excel? ( If that is possible. )
Or should I make the form in Acces?
​

Thanks in advance for any tips/tricks etc! ( PS. Using Excel and Acces 2003 )

If it is possible I can add the .xls file if thats nice to have.
This is my entire code:

Private Sub checkAnders_Click()
If checkAnders.Value = True Then
TextAnders.Enabled = True
TextAnders.Visible = True
Else
TextAnders.Enabled = False
End If
End Sub
Private Sub UserForm_Activate()
TextAnders.Visible = False
End Sub
 
Private Sub cmdinvullen_Click()
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
    If Me.ComboBox1.Value = "" Then
    MsgBox "Vul alsjeblieft je team in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.ComboBox1.SetFocus
    Exit Sub
End If
    If Datum.Value = "" Then
    MsgBox "Vul alsjeblieft een datum in."
    Exit Sub
End If
    If Not IsDate(Datum.Value) Then
    MsgBox "Ongeldige datum ingevoerd."
    Exit Sub
End If
If DateValue(Datum.Value) <= Date Then
    MsgBox "Datum dient in de toekomst te liggen."
    Exit Sub
End If
Dim Resp As Variant
    Resp = MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit? " & vbNewLine & "Naam  :        " & Me.TxtNaam.Value & vbNewLine & "Team  :        " & Me.ComboBox1.Value & vbNewLine & "Idee    :        " & Me.txtidee.Value, vbYesNo + vbQuestion)
    If Resp = vbNo Then
                    Exit Sub
    Else
        MsgBox "Idee ingevoerd!"
 
End If
RowCount = Worksheets("Archief").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 14).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
.Offset(RowCount, 15).Value = Me.ComboBox1.Value
.Offset(RowCount, 25).Value = Me.TextAnders.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 19).Value = "X"
Else
.Offset(RowCount, 19).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 20).Value = "X"
Else
.Offset(RowCount, 20).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 21).Value = "X"
Else
.Offset(RowCount, 21).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 22).Value = "X"
Else
.Offset(RowCount, 22).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 23).Value = "X"
Else
.Offset(RowCount, 23).Value = ""
End If
If Me.checkAnders.Value = True Then
.Offset(RowCount, 24).Value = "X"
Else
.Offset(RowCount, 24).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 1).Value = "X"
Else
.Offset(RowCount, 1).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 2).Value = "X"
Else
.Offset(RowCount, 2).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 3).Value = "X"
Else
.Offset(RowCount, 3).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 4).Value = "X"
Else
.Offset(RowCount, 4).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 5).Value = "X"
Else
.Offset(RowCount, 5).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 6).Value = "X"
Else
.Offset(RowCount, 6).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 7).Value = "X"
Else
.Offset(RowCount, 7).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 8).Value = "X"
Else
.Offset(RowCount, 8).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 9).Value = "X"
Else
.Offset(RowCount, 9).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 10).Value = "X"
Else
.Offset(RowCount, 10).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 11).Value = "X"
Else
.Offset(RowCount, 11).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 12).Value = "X"
Else
.Offset(RowCount, 12).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 13).Value = "X"
Else
.Offset(RowCount, 13).Value = ""
.Offset(RowCount, 16).Value = Format(Now, "dd/mm/yyyy hh:nn")
End If
.Offset(RowCount, 17).Value = DateValue(Datum.Text)
End With
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub
Private Sub UserForm_Click()
End Sub

 

[Weaver]

In my experience, Access is better for multiple users, although the data model is completely different from Excel. Come to think of it, that's probably what makes it better for multiple users. You can still get clashes when 2 users are both editing the same record, but if all people are doing is adding new records, the process is much slicker than even usung shared Excel files.

 

[Botje]

In my experience, Access is better for multiple users, although the data model is completely different from Excel. Come to think of it, that's probably what makes it better for multiple users. You can still get clashes when 2 users are both editing the same record, but if all people are doing is adding new records, the process is much slicker than even usung shared Excel files.

You can still get clashes when 2 users are both editing the same record, but if all people are doing is adding new records, the process is much slicker than even usung shared Excel files.

All that people are/should be doing are adding new records.. By filling in the form.
But how do i process this?

 

[Botje]

Any other idea's?

 

[Norie]

Botje

Is this just going to be for the one thing?

Or are you seeing it as possibly expanding in the future?

If it's the latter I would probably say Access but if it's just for one thing I'm not so sure.

If you were to use Access you would have practically no code.

All the validation you are currently doing is built-in to Access.

Any navigation, addition, deletion etc of records, that's built-in too.

Setting things up properly in Access would take a little time but once done should be worth it.

 

[Hermanito]

I would use a combination of Excel and Access.
Excel as frontend, with the userform for data-entry, because most users tend to be more familiar with Excel than with Access.

Your Excel frontend then inserts the user-input into a database. Multiple users creating records at the same time will (almost) never give issues because Access will handle those details for you. The chance that two users submit their data at exactly the same time is very low (and even then Access will probably solve that behind the screens), at least in your case i think, correct me if I'm wrong...

This concept would require a lot more code than what you have now, so I'm not sure you're up for that...

 

[Botje]

I would use a combination of Excel and Access.
Excel as frontend, with the userform for data-entry, because most users tend to be more familiar with Excel than with Access.

Your Excel frontend then inserts the user-input into a database. Multiple users creating records at the same time will (almost) never give issues because Access will handle those details for you. The chance that two users submit their data at exactly the same time is very low (and even then Access will probably solve that behind the screens), at least in your case i think, correct me if I'm wrong...

This concept would require a lot more code than what you have now, so I'm not sure you're up for that...

I do want to keep the "Excel" , got a link to a tutorials from the above? so I can get started?
Searched on the Acces forum but don't really know the right keywords..

Botje

Is this just going to be for the one thing?

Or are you seeing it as possibly expanding in the future?

If it's the latter I would probably say Access but if it's just for one thing I'm not so sure.

If you were to use Access you would have practically no code.

All the validation you are currently doing is built-in to Access.

Any navigation, addition, deletion etc of records, that's built-in too.

Setting things up properly in Access would take a little time but once done should be worth it.

You are right.. this is about that "one thing".

But i am confused now!

Norie says its is less codes and Hermanito it is alot more coding!

Im willing to put in alot more effort.. Willing to learn

But is it as easy as excel?
Acces is new for me! And recommendation what to look for? as in tutorials etc.?

 

[Hermanito]

Norie is right, but so am I

Norie talked about a solution 100% in Access, which would indeed need very little or no code. While I talked about a combination between Excel and Access where you need to write code to move data from Excel to Access (and maybe also in the other direction if you need it).

I don't have any links to tutorials handy, but I could provide some code to get you started, although that would take me some time to gather and simplify it to your needs...

 

[Norie]

I think Hermanito might mean there could be a more coding using an Excel/Access setup, perhaps to do with transferring data between the two.

The coding I was thinking of was all that validation stuff.

That's not needed in Access as it can be setup when you create the tables/forms etc.

Setting it up might take a bit it effort though.

The main advantage I can see though is that you would be able to add/edit/delete records without the need for code.

Access would take control of all that itself.

Hope that makes sense.

PS If you want you can send me the current file, send me PM and I can give you my email.

 

[Botje]

Norie is right, but so am I

Norie talked about a solution 100% in Access, which would indeed need very little or no code. While I talked about a combination between Excel and Access where you need to write code to move data from Excel to Access (and maybe also in the other direction if you need it).

I don't have any links to tutorials handy, but I could provide some code to get you started, although that would take me some time to gather and simplify it to your needs...

To bad that you aint got a tutorial! Always better to learn by trial and error! But it would be great if you could provide some coding!