Need advice on Small CRM Utility

[rocky09]

Greetings,

I am trying to build a small CRM Utility which will be using by 6 - 8 members.

Basically, it has three tables, one form and few queries.

tblemails (some fields will auto populates from outlook)
emailsid
vehicleno
manufacturer
receivedon
from
body
subject

tbllive
This is linked table, the data is populating from a sql remote server
id
vehicleno
partno
serialno
manufacturer
tblSI
siid
vehicleno
sidescription

I have a Form, on top right, I have search button Functionality,
Below, left side, I have fields from the tblemails (Split form)
Right side I have fields from tbllive. (just for live lookup purpose)

The problems I am facing

1. when user search by using the search button, It is taking too much of time to get the data from the tbllive (remote server).
Is there a way to get the results quick from the sql server?

2. My Team members are saying that it became very slow even accessing to the local tables also. (the tblemails has now 20,000 rows.)
Does Access cannot handle 20,000 rows or do i need to do any tweak?

3. My TL wants to show display charts by using tblemails table.
Is there any good tutorials available to do chart things in MS Access?

Is there any free or demo MS Access CRM System available to study it and implement some new features into my CRM?

Any help is really appreciated.

Thank you in advance.

 

[Ranman256]

20,000 should not be a trouble in Access.
This could be CPU speed.
Server speed,
Network cable type,
Router,
PC memory, etc.

All these could be a factor, but normally 20k records should pop in 1 second.
I've pulled a million records with no long delays at all.

For charts, use the query wizard to pull the data for the chart.
Then in a report drag a chart down into the report. It's pretty easy.

 

[CJ_London]

not sure what version of access you are using, but be aware that pivot charts and tables are no longer available from 2013.

In addition to Ranmans comments, other reason for speed issues are inefficient forms/reports and queries - including using domain functions, tables not properly indexed, forms calling for complete tables (rather than just the one or a few records) - which applies to combo/list boxes as well. DB not split between front end/backend, users sharing same front end.

The fact you have tables in two separate db's may also have an impact - can you not have a routine to have a table in the access backend which is updated regularly from your live db.

With regards free or demo crm db's, take a look at the templates offered within access

 

[rocky09]

@Ranman256
Thank you very much for your reply.

@CJ_London
Yes. I will try to make a routine to pull the data from SQL Server at some interval. Thank you so much for your suggestion.