General Networking/Lan/Wan/Access database

Advertisement


Question
QUESTION: Hi Scott - I have an access database which I use to collect data from paralegals in rural areas. They have limited access to internet so each month they perform an export to transfer the data from access to a spreadsheet with a sheet for each table.  They email this to me; at head office, all the excel spreadsheets are stored in a folder and imported into tables (currently takes about 5 -8 min to import). This consolidates all the client records from 18 different paralegal offices into tables.  Thereafter, analysis may be done on the data.

(Each month, ALL the data is sent to head office since some cases may extend over months and even years. However, there are some cases which are re-sent unchanged each month - not ideal I know but I haven't worked out how to correct that).

Now - my problem is that we would like to extend this program to up to 300 paralegals which was originally not the intention.  I am aware that the rate of importing the information will slow down considerably due to the increase in number of spreadsheets and the increase in records per spreadhsheet.   I also know that there is a limit to the size of the access database file (2Gb?).

Currently, after collecting data for 6 months from 18 paralegals the database is 38MB and contains about 4000 records.

Can you advise me
1. about using SQL server for my database instead of Access; would that solve the size limit and what is involved in saving to SQL server?
2. can you think of a better way of including these spreadsheets into the database?
3. how could I add the new records and replace only the changed records in the head office tables each month?
I look forward to hearing from you.
Regards, Kary

ANSWER: 1) SQL Server will solve the size limits, but you are far away from reaching the 2G limit. Also, you can get around that limit to some extent by splitting your database between front end and back end.

2) How are records identified? Where and how is the primary key assigned.

3) I would add an UpdatedWhen field to your tables, and use that to filter for records updated since the last transmittal.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for your reply Scott.  Two additional queries:
1. What is involved in my changing to SQL Server? Will my forms and queries still work in the same way? I have no knowledge of SQL Server - how would I go about doing this?
2. I will add an update field as you suggest; the procedure would then be: a)Export updated and new records -> b)import at head office. What happens if an "old" record is updated so it'll already exist on head office database - what is the best way of identifying and deleting these?
Thanks, Kary

ANSWER: If you have no knowledge of SQL Server in your organization, I would hold off on considering it. Like I said, you are far away from reaching that limit If the database passes 1G then you can start considering it.

You didn't answer as to how records are identified and primary keys assigned. I need to know that to provide further advice.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Table Name   PersonalDetails   Case       CSeq1          CSeq2
Primary Key  RefNo (Text, 10)   CaseID(Text, 20) CSID (Text, 20)   CSeqID
Related field          RefNo(Text, 10)    CaseID   CSID

RefNo is created in the BeforeInsert event by calculating the maximum of field PDNUm (autonumber field) and adding one. This is then combined with the name of the Support Centre to produce a number such as PLE10.
PDNum = (Nz(DMax("[PDNum]", "Personal Details"), 0) + 1)
RefNo = Forms!IndivPop!SupportCentre & [PDNum] 'set SC Code to value of Support Centre on IndivPop FRM

Similarly I have created the CaseID from the RefNo to produce a CaseID such as PLE10-1.  The other tables all collect additional information for the case as well as two tables (CSeq1 and CSeq2) which store data about each visit from the client. In CSeq1, CSID is generated from CaseID to produce PLE10-1-1, etc for each visit.
I hope that this will help you to have a clearer picture of the database.  If I donítí go for SQL server now, is it easy to switch over when and if necessary?

Answer
Well first I would not do it the way you are doing it. The reason is because you are storing redundant data in your table. You can use a composite key so you aren't storing the same info multiple times.

So let me review the process. The records are created by the paralegals in their local Access databases. They export to Excel and send the sheets to you. The Excel sheets can contain new records, updated records or existing records that have not been updated. You want to only import the new records, plus update existing records that have been changed since the last update.

Is that the process?

Let me know and I have some ideas for you.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

Most general questions on LANs, especially home networks. Some admin and design issue.

Experience

I've been an IT Professional for more then 16 years. I've accumulated a larege store of knowledge that allows me to answer a wide variety of IT related questions.

©2016 About.com. All rights reserved.