Results 1 to 7 of 7
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    Need Help with Satellite Database

    Hi Guys,

    I'm new here so I hope this kind of post is welcome. A little info about my project:

    - I am modifying an existing database to use a series of forms for direct entry. We currently use excel then manually import which is tedious.
    - I need to build in a number of data validation tools and query based QA/QC checks
    - Several calculated fields will be used


    - A master copy will be stored on a server and will only be accessed by one computer at a time which will use the existing tables and queries to view data
    - Multiple satellite databases will be used for data entry on non-networked computers
    - At the end of every day the data collected by the satellite databases needs to be appended to the master database. The last few days of collected data needs to remain on the satellite databases

    I guess that's it summarized. I need the records to append nicely and not get over-written or accidentally deleted if something changes in the satellite databases - once data is imported the master database should take precedent I guess?

    I have recently completed a number of courses and am getting fairly good with database design, linking forms etc, but we did not cover backup up to a master database. What is the easiest way to do this? Do I split the database? I will have to learn how to do that? Do I just have several copies of the exact same database and build a macro of append queries?

    Should something like this be date based? Every single record will have a special name field to group them by project, in addition to the primary key. The primary key is currently a basic Autonumber but it might make more sense to use a more complicated GUID?

    Any help is appreciated. I am gathering info/forming my strategy and will officially start on this project in a few weeks.

    Thank you!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. database should be split. tables in backend everything else in front end
    2. depends on whether your satellite db's are for data collection only, or require updating from the master (new products and the like)
    3. consider using sql server/express back end - it supports replication
    4. if you decide you want to build your own replication in access:

    • you will need a number of additional fields - the autonumberPK needs to be random (not incremental), you will need another replication field - autonumber replication GUID, a 'last synchronised' date field and a 'last change' dated field
    • you will need a number of queries to manage the synchronisation
    • you will need a method for handling data clashes - where you have two or more updates since last synced. Also random autonumbers are random, but not guaranteed to be unique (which a GUID is) so you can get clashes on this as well.
    • you will need some basis for handling deleted records - usual way is to have a 'deleted' field which can be set to yes and then ignored in your queries.



    be aware replication guids are significantly larger than autonumbers (4x larger) so simplistically, a replicated db will be 4x slower. Strongly recommend you do not use them as primary key

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Thank you Ajax. The satellite DB will be used strictly for data entry. It will be done manually at first (copying using USB stick?) with hopefully the capability to work over a local network. Will likely be updated every day.

    I would like it to work as simply as possible. Seems I've got to give it quite a bit of thought before I start modifying tables, etc.

    If the DB is currently ~60mb large, will using GUID slow it down appreciably? I hadn't thought of using a GUID as a replication ID but not as a primary key.

    I should add that I have no interest in doing this via SQL server/express back end if possible. I don't want to over-complicate things.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I wouldn't connect to a accdb on a usb stick - copy it first to your hard drive/server.

    60mb is pretty small so assuming it is staying that size synchronising will probably only take a few minutes - depends on number of tables/records etc. Best guess is when you add the guids it will increase by 25-30% in size. Guids should only be used for replication, you use the autonumber/random/long integer as a primary key to link to other tables. guids do not make good primary keys - see this link http://www.databasedev.co.uk/primary_keys.html. If it was a case of a lone table not related to anything else, probably not a problem, but if you are referencing the guid as a family key in other tables, the space requirement escalates considerably.

    The satellite DB will be used strictly for data entry
    I suspect you need to think this through in more detail. Implication is there is just one table which is emptied every time you sync. I don't know what your in-field db is doing, but won't it need reference data such as customer names/addresses/equipment etc - data that will need to be updated so at least the user doesn't enter it again?

  5. #5
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    There is one table that keeps track of unique 'projects' and all the other tables use that 'project ID' (a string) as their foreign key so that I can query based on project id.

    I want to build a form based front end so that the user (data entry) simply adds to those tables.. then at the end of the day all the tables get appended to the same tables in the master database. That way all my existing queries etc. should remain functional.

    Does that make sense?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Does that make sense?
    without knowing the full detail - possibly. Your question is akin to 'I want to get from A to B by train - does that make sense?' I have the same amount of information - don't know where A or B are, don't know if they have a train station, don't know if the trains run to a schedule that suits you, etc.

    Before you work out how you are going to do something you need a detailed specification of what is required. The 3 lines in bold in your first post are a start - but it is not much more than a blank piece of paper - how do the data validation, QA checks fit in. What are the calculated fields? where does this take place. You mention multiple satellite db's - where are they, how often do they need to be upload to the master db - do the other satellites need to know about the data? If so, when? What are the satellite machines? laptops? tablets? smart phones?

    etc

    etc

    once data is imported the master database should take precedent I guess?
    no guessing - what do you want to happen?

    You may find that once you have a full specification, Access is not the way to go

  7. #7
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    So like I said I have several tables that are all linked by one specific table, we'll call it 'header' which keeps track of all the unique 'projects' (project-id used as primary key in 'header' and foreign key in all other tables that store data. All the tables are currently setup in the same way;

    Autonumber (primary key) - project id (foreign key) - data - data - data - data, etc.

    This is currently copied and pasted from excel into access (all fields except autonumber which autofills). Each excel sheet in the workbook has a corresponding table in our database.

    The foreign key and all the data needs to be updated daily from the satellite databases to one central database. The satellites do not need to see any of the other data or the 'bigger picture'. I am assuming I won't bring over the primary key from the data collection tables. I think I'll make the 'header' table only visible by the person (me) who controls the master database.

    I think I'll do calculated fields in my forms for simplicity, but I'll need away to get those values into a table as well. I'll have to figure out how to do that as the calculations require several steps and rely on other fields that will be input by the user. I suppose that's another hurdle I'm recognizing as the data to be used in the calculation might not be input until well after some of the other fields, so perhaps I'll have to do that as a query, which would require the data be on the satellite computers and use-able. I'll have to figure that out.

    Satellite devices are laptops running the same version on Access and will be used within the same office.

    Thank you for the continued conversation. It is helping me better visualize what I need to do and the potential ways to do it. My problem is that I am not yet familiar enough with access to know what I can can not do without a lot of trial and error. I don't want to spend hours restructuring my tables only to find out that my method of replication will not work. The database in general is relatively simple I think, but preserving functionality of our queries is crucial.

    I am starting to envision a date based query for updating. I could add the date modified and date synced fields to each table and only update where modified is later than last synced. I could make the modified date autofill using forms/subforms and then autofill then fill the sync date after the append/update query runs. I think each record would still require a GUID but it would not be used as a primary key but rather a replication ID? I will have to familiarize myself with the difference, I am not very familiar with replication ID's and less so with using them as a primary key for my records.

    Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Replies: 1
    Last Post: 07-01-2015, 04:18 AM
  5. Replies: 1
    Last Post: 03-21-2015, 11:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums