Results 1 to 6 of 6
  1. #1
    jcrice88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    3

    Creating my first database and i need help

    Hello
    I am creating a database and need some guidance please.

    I have never used access before but have been reading and watching videos to prepare to build a database.

    Here is what I am trying to do.
    I need multiple excel tables to fill out a database (at remote locations not on the same server) that can be access and merged into central database at my office.
    The excel sheets will be changed out frequently but I need the database to record historical data from excel sheets that are no longer linked or available.



    If I have 4 different excel sheets do I need 4 different tables in access that will need to be unioned? or is there a way (vba/sql) to have each row (table design will be identical) or table update without overriding previous data?


    Any suggestions on where to start researching ways to accomplish this is greatly appreciated. I really don't know where to start.

    Thank you
    Jake

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In general, Excel spreadsheet design has lots of columns (fields) and may have relatively few rows (records).
    By contrast, good Access tale design typically has few fields and many records.
    Importing Excel data into Access tables will normally require changes to the design.

    Having said that, you can link the spreadsheets from Access and then use an APPEND query to import all the data into one table.
    There should be no need to create multiple tables in your situation.
    Last edited by isladogs; 09-18-2018 at 02:12 AM. Reason: Typo
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    jcrice88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    3
    If I have multiple linked tables can I append them together in a new table and still basically receive live updates from the linked tables?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by jcrice88 View Post
    If I have multiple linked tables can I append them together in a new table and still basically receive live updates from the linked tables?
    Linked tables are always 'live'.
    Whilst you can append the data from several sources into a new table (but see caveats in my last reply), it will only be up to date at that time.
    It won't stay up to date automatically
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    You can update Access table automatically from linked tables using e.g. On Open Event of Main form of your database (i.e. data in Access table is refreshed when you open the database);
    You can update Access table using a procedure run fom On Click event of button on form (i.e. data in Access table is refreshed when you click on button);
    You can create a windows scheduled task, which will run the procedure in your access database. The procedure updates your Access table (e.g. at nighttime);
    You can design your database with SQL Server Database as back-end. When using licensed SQL Server, you can define works which run procedures reading data from your Excel files and refreshing table(s) in your SQL back-end on certain schedule. (You can't link Excel tables into SQL database directly!)

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As Arvil has helpfully explained, there are various ways of keeping the Access data up to date.
    The method I would avoid the first one he listed as it will cause your database to take longer to start.
    If there are a lot of records, the delay may be significant.

    You will ask need to ensure that records are only imported once to avoid duplicates.
    However if the Excel files are to be permanently linked, do you really need to import the data into Access to use the underlying data?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Creating Database
    By samot79 in forum Database Design
    Replies: 10
    Last Post: 04-29-2017, 04:48 PM
  2. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  3. Creating new database.
    By rjurke in forum Database Design
    Replies: 5
    Last Post: 09-28-2011, 04:55 PM
  4. Creating new database
    By rjurke in forum Access
    Replies: 10
    Last Post: 09-23-2011, 04:59 AM
  5. Creating database
    By ramzyamal in forum Database Design
    Replies: 1
    Last Post: 05-07-2007, 08:53 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