Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using Excel as the interface for an Access database

Discussion in 'Information Technology' started by David, Jul 28, 2006.

  1. David

    David
    Expand Collapse
    Guest

    Is there a way where I can store information in Access and use Excel and
    create a spreadsheet and be able to pull data from the Access database? If
    so, if the information in the database is changed, would it automtically
    update the linked excel file?

    Thanks,
     
  2. Loading...


  3. SusanV

    SusanV
    Expand Collapse
    Guest

    Why would you want to do this?

    "David" <David@discussions.microsoft.com> wrote in message
    news:D91169CB-0AE1-435C-BFB3-9D78C29B6245@microsoft.com...
    > Is there a way where I can store information in Access and use Excel and
    > create a spreadsheet and be able to pull data from the Access database? If
    > so, if the information in the database is changed, would it automtically
    > update the linked excel file?
    >
    > Thanks,
     
  4. David

    David
    Expand Collapse
    Guest

    I have an excel workbook that I have setup that when I pick something from a
    drop down box, a field would automatically be pulled. For example when I
    chose David from a drop down box, the cell next to it would bring my date of
    birth. Now, I have the lists for the drop down boxes on another worksheet
    within the file and it is slowing down the program a lot because of all of
    the data that I am using. I am looking to see if there is a way that I can
    put in excel a formula where it would pull the information from an access
    database and not excel and just use excel as the "Face"
    Thanks

    "SusanV" wrote:

    > Why would you want to do this?
    >
    > "David" <David@discussions.microsoft.com> wrote in message
    > news:D91169CB-0AE1-435C-BFB3-9D78C29B6245@microsoft.com...
    > > Is there a way where I can store information in Access and use Excel and
    > > create a spreadsheet and be able to pull data from the Access database? If
    > > so, if the information in the database is changed, would it automtically
    > > update the linked excel file?
    > >
    > > Thanks,

    >
    >
    >
     
  5. Krizhek

    Krizhek
    Expand Collapse
    Guest

    Yeah In excel goto Data > Import External Data > New Database Query
    Select Microsoft Access and then the location of your file. Then you will
    be able to pull the data.

    Once you have the link set up you can only add/update/edit data in Access.

    Excel will update the data when you open the excel file but if both access
    and excel is open you will have to manually update Excel via Refresh Data.


    And
    "David" wrote:

    > Is there a way where I can store information in Access and use Excel and
    > create a spreadsheet and be able to pull data from the Access database? If
    > so, if the information in the database is changed, would it automtically
    > update the linked excel file?
    >
    > Thanks,
     
  6. SusanV

    SusanV
    Expand Collapse
    Guest

    You can certainly do this by setting an Access db as the datasource, but it
    seems to me that's kind of like using a sledge hammer to hang a picture in
    your condo.

    Also keep in mind that puling data from an external source is also going to
    take time.
    --
    hth,
    SusanV




    "David" <David@discussions.microsoft.com> wrote in message
    news:E1A11836-034C-4B2B-B777-00ECFE62E791@microsoft.com...
    >I have an excel workbook that I have setup that when I pick something from
    >a
    > drop down box, a field would automatically be pulled. For example when I
    > chose David from a drop down box, the cell next to it would bring my date
    > of
    > birth. Now, I have the lists for the drop down boxes on another worksheet
    > within the file and it is slowing down the program a lot because of all of
    > the data that I am using. I am looking to see if there is a way that I
    > can
    > put in excel a formula where it would pull the information from an access
    > database and not excel and just use excel as the "Face"
    > Thanks
    >
    > "SusanV" wrote:
    >
    >> Why would you want to do this?
    >>
    >> "David" <David@discussions.microsoft.com> wrote in message
    >> news:D91169CB-0AE1-435C-BFB3-9D78C29B6245@microsoft.com...
    >> > Is there a way where I can store information in Access and use Excel
    >> > and
    >> > create a spreadsheet and be able to pull data from the Access database?
    >> > If
    >> > so, if the information in the database is changed, would it
    >> > automtically
    >> > update the linked excel file?
    >> >
    >> > Thanks,

    >>
    >>
    >>
     
  7. David

    David
    Expand Collapse
    Guest

    Thanks for your help. Now, can I create a drop down box in the excel sheet
    that pulls the data from the access database and how do I do it?

    THanks again for your help

    "Krizhek" wrote:

    > Yeah In excel goto Data > Import External Data > New Database Query
    > Select Microsoft Access and then the location of your file. Then you will
    > be able to pull the data.
    >
    > Once you have the link set up you can only add/update/edit data in Access.
    >
    > Excel will update the data when you open the excel file but if both access
    > and excel is open you will have to manually update Excel via Refresh Data.
    >
    >
    > And
    > "David" wrote:
    >
    > > Is there a way where I can store information in Access and use Excel and
    > > create a spreadsheet and be able to pull data from the Access database? If
    > > so, if the information in the database is changed, would it automtically
    > > update the linked excel file?
    > >
    > > Thanks,
     
  8. Krizhek

    Krizhek
    Expand Collapse
    Guest

    For that I am afraid you will have to seek someone on the excel forum. I
    never got into this but had some co-workers that would do something similar
    to this by creating an excel query (which would be linked to the Access
    table) and use whatever's in the dropdown as an condition.

    "David" wrote:

    > Thanks for your help. Now, can I create a drop down box in the excel sheet
    > that pulls the data from the access database and how do I do it?
    >
    > THanks again for your help
    >
    > "Krizhek" wrote:
    >
    > > Yeah In excel goto Data > Import External Data > New Database Query
    > > Select Microsoft Access and then the location of your file. Then you will
    > > be able to pull the data.
    > >
    > > Once you have the link set up you can only add/update/edit data in Access.
    > >
    > > Excel will update the data when you open the excel file but if both access
    > > and excel is open you will have to manually update Excel via Refresh Data.
    > >
    > >
    > > And
    > > "David" wrote:
    > >
    > > > Is there a way where I can store information in Access and use Excel and
    > > > create a spreadsheet and be able to pull data from the Access database? If
    > > > so, if the information in the database is changed, would it automtically
    > > > update the linked excel file?
    > > >
    > > > Thanks,
     
  9. David

    David
    Expand Collapse
    Guest

    how so i set access db as the data source?

    thanks

    "SusanV" wrote:

    > You can certainly do this by setting an Access db as the datasource, but it
    > seems to me that's kind of like using a sledge hammer to hang a picture in
    > your condo.
    >
    > Also keep in mind that puling data from an external source is also going to
    > take time.
    > --
    > hth,
    > SusanV
    >
    >
    >
    >
    > "David" <David@discussions.microsoft.com> wrote in message
    > news:E1A11836-034C-4B2B-B777-00ECFE62E791@microsoft.com...
    > >I have an excel workbook that I have setup that when I pick something from
    > >a
    > > drop down box, a field would automatically be pulled. For example when I
    > > chose David from a drop down box, the cell next to it would bring my date
    > > of
    > > birth. Now, I have the lists for the drop down boxes on another worksheet
    > > within the file and it is slowing down the program a lot because of all of
    > > the data that I am using. I am looking to see if there is a way that I
    > > can
    > > put in excel a formula where it would pull the information from an access
    > > database and not excel and just use excel as the "Face"
    > > Thanks
    > >
    > > "SusanV" wrote:
    > >
    > >> Why would you want to do this?
    > >>
    > >> "David" <David@discussions.microsoft.com> wrote in message
    > >> news:D91169CB-0AE1-435C-BFB3-9D78C29B6245@microsoft.com...
    > >> > Is there a way where I can store information in Access and use Excel
    > >> > and
    > >> > create a spreadsheet and be able to pull data from the Access database?
    > >> > If
    > >> > so, if the information in the database is changed, would it
    > >> > automtically
    > >> > update the linked excel file?
    > >> >
    > >> > Thanks,
    > >>
    > >>
    > >>

    >
    >
    >
     
  10. SusanV

    SusanV
    Expand Collapse
    Guest

    I'm sure the Excel folks would be better able to help you - generally
    speaking I only use Excel for data output from Access. The Help in Excel
    (F1) is pretty good too:

    <copied from Excel Help>
    Ways to retrieve data from an external database

    You can retrieve external data from databases in the following ways:

    Using Microsoft Query
    Using Microsoft Visual Basic
    Using Web queries
    <snipped>



    Good luck!

    SusanV

    "David" <David@discussions.microsoft.com> wrote in message
    news:85369788-6D4D-40F6-AB56-0AACF85964A1@microsoft.com...
    > how so i set access db as the data source?
    >
    > thanks
    >
    > "SusanV" wrote:
    >
    >> You can certainly do this by setting an Access db as the datasource, but
    >> it
    >> seems to me that's kind of like using a sledge hammer to hang a picture
    >> in
    >> your condo.
    >>
    >> Also keep in mind that puling data from an external source is also going
    >> to
    >> take time.
    >> --
    >> hth,
    >> SusanV
    >>
    >>
    >>
    >>
    >> "David" <David@discussions.microsoft.com> wrote in message
    >> news:E1A11836-034C-4B2B-B777-00ECFE62E791@microsoft.com...
    >> >I have an excel workbook that I have setup that when I pick something
    >> >from
    >> >a
    >> > drop down box, a field would automatically be pulled. For example when
    >> > I
    >> > chose David from a drop down box, the cell next to it would bring my
    >> > date
    >> > of
    >> > birth. Now, I have the lists for the drop down boxes on another
    >> > worksheet
    >> > within the file and it is slowing down the program a lot because of all
    >> > of
    >> > the data that I am using. I am looking to see if there is a way that I
    >> > can
    >> > put in excel a formula where it would pull the information from an
    >> > access
    >> > database and not excel and just use excel as the "Face"
    >> > Thanks
    >> >
    >> > "SusanV" wrote:
    >> >
    >> >> Why would you want to do this?
    >> >>
    >> >> "David" <David@discussions.microsoft.com> wrote in message
    >> >> news:D91169CB-0AE1-435C-BFB3-9D78C29B6245@microsoft.com...
    >> >> > Is there a way where I can store information in Access and use Excel
    >> >> > and
    >> >> > create a spreadsheet and be able to pull data from the Access
    >> >> > database?
    >> >> > If
    >> >> > so, if the information in the database is changed, would it
    >> >> > automtically
    >> >> > update the linked excel file?
    >> >> >
    >> >> > Thanks,
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     

Share This Page