Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7

    Help required to set up database

    Hi All
    Looking for help to setup a data base in Access that collects information from the attached spreadsheet pictures from A1:FO26
    Spreadsheet is macro enabled and updates automatically with data.
    have tried to setup Access data base but when Excel Tab updates it copies over the data that was last copied into Access.

    David
    Attached Thumbnails Attached Thumbnails Personal Page 1.jpg   Personal Page 2.jpg   Personal Page 3.jpg  

  2. #2
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    I don't understand this sentence, "have tried to setup Access data base but when Excel Tab updates it copies over the data that was last copied into Access. "
    Are you wanting to copy the data into Access (data wont change when it changes in Excel)
    or link to the Excel file (data within Access changes when it changes in Excel)?
    Are you keeping the Excel system, or trying to eliminate it?

    Why do you want this data in a database?

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your photos are too small to read the details.

  4. #4
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by JamesDeckert View Post
    I don't understand this sentence, "have tried to setup Access data base but when Excel Tab updates it copies over the data that was last copied into Access. "
    Are you wanting to copy the data into Access (data wont change when it changes in Excel)
    or link to the Excel file (data within Access changes when it changes in Excel)?
    Are you keeping the Excel system, or trying to eliminate it?

    Why do you want this data in a database?
    Hi James
    data within Access changes when it changes in Excel instead of filling down the table
    Am wanting to collect data to create a database for analyzing past performance of my Racing predictions from Excel
    So the excel pictures are of all the data that is downloaded from the internet into Excel that I would like to capture in access as it goes thru each race meeting

    David

  5. #5
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    From what I can see you will have to take each section of you excel sheet that you want in a table and make several worksheets then copy each section into a new worksheet then open access and external data excel select the excel file and then it will ask you which sheet you want to import to a new table and then finish. you will have to repeat the process for each sheet but on the 2nd 3rd sheet etc.. you select to append the copy of the table you created and it will add the fields to the table

  6. #6
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Hi LaughingBull

    The Excel sheet that has the data that I would like to capture, runs in the background then updates the main race sheet
    I was hoping there was a way of capturing the data from Excel into Access as it ran in the background, updating the data in access so that every races data is captured in access
    I have been able to get the data into access but only for a single race the when the Excel sheet updates the data in Access gets renewed without saving the previous race data down the table

    David

  7. #7
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    how did you do that?

  8. #8
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    using the Excel External data tool in Access then having both Access and Excel open side by side on screen I can see the data been imported into access when Excel is running, But issue is it only has just the race that is currently generated in Excel then when Excel goes to next race the data in Access changes to the next race without saving it down the page

  9. #9
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    Does Excel's info clear out with each race, or is it appended? How is the data being copied into Access?

  10. #10
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Hi JamesDecket
    Yes the info clears out of Excel with each race has been rated
    Excel is automated to run several times daily using VBA macro

  11. #11
    peiyezhu is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2015
    Posts
    8
    how about link table?

  12. #12
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Hi Peiyezhu
    I have tried the link table as well but with same result
    the data clears out
    would like to capture any Excel data that comes into A1:FK26 , Transferring it into a Access Database.
    Not sure if I need to do it with VBA in Excel

  13. #13
    peiyezhu is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2015
    Posts
    8
    So the excel pictures are of all the data that is downloaded from the internet into Excel that I would like to capture in access as it goes thru each race meeting

    can you consider to download data to Access table from internet directily instead of via Excel worksheet?

  14. #14
    peiyezhu is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2015
    Posts
    8
    if you must tro Excel , need VBA to name the data range then link this range to access with the name,so the data can be dynamic with excel worksheet.

  15. #15
    dmcg9760 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Hi Peiyezhu
    Thanks for the reply

    Yes all of the pictures are of the data that has come from the internet then organized into the table in Excel

    So I would need to use VBA to have the data transferred into access. Then it should work keeping all of the data from each Race in Access so that I can work with it later.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-12-2015, 07:03 PM
  2. Replies: 3
    Last Post: 08-06-2012, 04:59 PM
  3. Horses database help required
    By arnie in forum Database Design
    Replies: 8
    Last Post: 04-26-2011, 08:34 PM
  4. Simple database required
    By Steve in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 06:34 PM
  5. Access Database Problem: HELP required ASAP
    By maverick in forum Queries
    Replies: 1
    Last Post: 01-06-2010, 04:47 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