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

 

[Norie]

Here's a small tutorial:

1 Goto the sheet with data in Excel.

2 Select all the data and copy it.

3 Open Access.

4 Right click in the table section/tab of the Navigation Pane/Database Window.

5 Select Paste.

6 Click yes when asked about headers. <super>*</super>

7 You should now have a table, it'll have the same name as the sheet you copied from.

This next bit is specific to Access 2010 but can be done in earlier versions as well, just slightly differently.

8 Select the table, goto the Create tab and click Form.

You should now have a, badly designed, simple, form based on your table.

* Assuming you have headers on the worksheet.

 

[Botje]

Here's a small tutorial:

1 Goto the sheet with data in Excel.

2 Select all the data and copy it.

3 Open Access.

4 Right click in the table section/tab of the Navigation Pane/Database Window.

5 Select Paste.

6 Click yes when asked about headers. <SUPER>*</SUPER>

7 You should now have a table, it'll have the same name as the sheet you copied from.

This next bit is specific to Access 2010 but can be done in earlier versions as well, just slightly differently.

8 Select the table, goto the Create tab and click Form.

You should now have a, badly designed, simple, form based on your table.

* Assuming you have headers on the worksheet.

The Microsoft Jet database engine could not find the object <NAME>. Make sure the object exists and that you spell its name and the path name correctly. (Error 3011)

That is the Error i keep getting.. I can't copy paste it from excel!
Probarly because im at another PC now.. MS-help aint helping either

 

[Norie]

What exactly did you try?

That's a strange message to get if you are just copying data from Excel to Access.

The only thing I can think that would cause it is something to do with how Access/Excel are set up.

Or maybe even a missing driver.

You could try the External Data tab in Access.

That should allow you to import data from Excel.

Once you've done that you can create a form.

 

[Botje]

What exactly did you try?

That's a strange message to get if you are just copying data from Excel to Access.

The only thing I can think that would cause it is something to do with how Access/Excel are set up.

Or maybe even a missing driver.

You could try the External Data tab in Access.

That should allow you to import data from Excel.

Once you've done that you can create a form.

Even more strange is the solution...

I changed the language to English because of tutorials etc. works easier..
Then my Excel gave me that 3011 error and Outlook just kept disconnecting..

Then i changed it back.. and all problems are solved..

Apperently they won't let me use the English Language pack!

Copy-Pasty works now its Dutch language again..

 

[baitmaster]

Follow various links start here, many examples available of code to link Excel and Access, using ADODB

 

[Botje]

Follow various links start here, many examples available of code to link Excel and Access, using ADODB

Thanks alot, i hope its usefull for me!

 

[Botje]

Follow various links start here, many examples available of code to link Excel and Access, using ADODB

Thanks alot, i hope its usefull for me!

Thanks, but this is the next step gotta find out the basics of Acces first^^

 

[Norie]

Botje

If you are going to use Access then you'll probably need to restructure your data.

You'll probably find out more about that when learning about databases.

There's actually a few things you should have a look at in the workbook/userform.

For example, the groups of checkboxes could be replaced with listboxes.

That would make things a bit easier when transferring the data to a worksheet.

It'll also make the code a lot shorter.

That's one of the things I noticed when I took a quick look at the file a few days ago.

I'll try and take a closer look today.

PS Nice bus.

 

[Botje]

Botje

If you are going to use Access then you'll probably need to restructure your data.

You'll probably find out more about that when learning about databases.

There's actually a few things you should have a look at in the workbook/userform.

For example, the groups of checkboxes could be replaced with listboxes.

That would make things a bit easier when transferring the data to a worksheet.

It'll also make the code a lot shorter.

That's one of the things I noticed when I took a quick look at the file a few days ago.

I'll try and take a closer look today.

PS Nice bus.

Stole the bus from the interwebs!

And im taking a "quick course acces" @ the interwebs at this very moment. step by step.. just not using VBA (yet)

 

[Norie]

Botje

If you set things up properly in Access you might not need code and you should definitely not jump to using code.

That's a mistake I've seen happen quite a bit.

You should concentrate on planning and designing the database.