Access Limits?

corporateaccount

Board Regular
Joined
Aug 11, 2004
Messages
64
I'm usuing Access on a small network of about no more than 10 users at any given time. I have them use Excel as a method of submitting data to an Access DB file - collectively, they make no more than 100 or so entries a day.

If the number of agents turned into 25 the next day with anywhere from 300 to 500 entries within an 11 hour period, will it be able to hold up?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
That number of transactions is unlikely to bother Access; it's more about the design of the system. How is the Excel data being transferred into Access? Are you using workbooks linked in as Access tables (which has limitations) or pushing the data using code (much more extensible)?

Once the data has been submitted, is it edited at a later time?

If you want to use Excel as a code-based front end, check out this tutorial. It should provide most of the required detail.

Denis
 
Upvote 0
Yup, I'm using an excel wb with code underneath to send the selected data to an Access Db on our network:

Code:
Private Sub CommandButton1_Click()
Dim db As Database, rs As Recordset
Set db = OpenDatabase("\\Manuals\DB\Access\inqdb.mdb")
Set rs = db.OpenRecordset("Inquiries", dbOpenTable)
    With rs
.AddNew
.Fields("User") = Application.UserName
.Fields("DateOfCall") = Format(Now, "MMM DD, YYYY")
.Fields("TimeOfCall") = Format(Now, "HH:MM")
.Fields("LineDisplay") = "Omitted"
.Fields("InsuranceCo") = "Omitted"
.Fields("InsuredName") = TextBox1
.Fields("PolicyNo") = TextBox2
.Fields("Caller") = TextBox3
.Fields("Reason") = TextBox4
'.Fields("User") = Application.UserName
'.Fields("Date") = Format(Now, "DD-MMM-YYYY")
    .Update
    End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'Call PickDB
UserForm1.Hide
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
End Sub

Works pretty well with 6-10 people and the information is never edited after the fact. It is dumped and then called, but never edited.

Thanks for the quick reply. I'm guessing it should be okay.
 
Upvote 0
The only issue you're likely to have is if more than one user runs the upload at the same time. If it's one record at a time you'd have to be unlucky; if you're loading a couple of thousand at a go you could lock the table while the load process runs (typically I load 1000 records / sec over a network). In that case, users will get an error message. If they wait a few seconds and run again, they should be fine.

Denis
 
Upvote 0
Thanks for the reply. Looks like I will have to take my chances. Luckily, the influx should be short term (no longer than two weeks) and we can probably handle it.

I've been very luck with Access so far...


But I'll be sure to back up daily just in case.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top