Welcome to SPN

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

Sign Up Now!

Import/Export/Link Access Datasheet to Excel

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

  1. jwr

    jwr
    Expand Collapse
    Guest

    In Microsoft Office Online it explains how to export data to excel.
    However, I have a question that I cannot find an answer.

    The instructions are to open the object in database window and ---- to save
    only a part of a datasheet, open the datasheet and select that portion of
    the datasheet before continuing. How do I do that??? I do not have an
    option to select a portion of my report once it opens with data.

    I want to export only a portion of a long report or query. How do I
    accomplish this?? Then I need to import that information back to access
    once my end-user fills in the blanks. I have created a table that matches
    the information I am wanting to export/import. How do I then import without
    overwriting existing data?

    Thank you in advance,
    JR
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    If I want to export only part of a table, I create a query that limits the
    fields and records properly. Then I export the query the same way as a table.

    Reports are a little different. You may need to create a specific report to
    export.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "jwr" wrote:

    > In Microsoft Office Online it explains how to export data to excel.
    > However, I have a question that I cannot find an answer.
    >
    > The instructions are to open the object in database window and ---- to save
    > only a part of a datasheet, open the datasheet and select that portion of
    > the datasheet before continuing. How do I do that??? I do not have an
    > option to select a portion of my report once it opens with data.
    >
    > I want to export only a portion of a long report or query. How do I
    > accomplish this?? Then I need to import that information back to access
    > once my end-user fills in the blanks. I have created a table that matches
    > the information I am wanting to export/import. How do I then import without
    > overwriting existing data?
    >
    > Thank you in advance,
    > JR
    >
    >
    >
     
  4. jwr

    jwr
    Expand Collapse
    Guest

    Thank you. Can you also tell me how to return the information to my access
    table without overwriting data already stored there?


    "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message
    news:DFE79867-4A13-454B-B9E5-7472EFB90B28@microsoft.com...
    > If I want to export only part of a table, I create a query that limits the
    > fields and records properly. Then I export the query the same way as a
    > table.
    >
    > Reports are a little different. You may need to create a specific report
    > to
    > export.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "jwr" wrote:
    >
    >> In Microsoft Office Online it explains how to export data to excel.
    >> However, I have a question that I cannot find an answer.
    >>
    >> The instructions are to open the object in database window and ---- to
    >> save
    >> only a part of a datasheet, open the datasheet and select that portion of
    >> the datasheet before continuing. How do I do that??? I do not have an
    >> option to select a portion of my report once it opens with data.
    >>
    >> I want to export only a portion of a long report or query. How do I
    >> accomplish this?? Then I need to import that information back to access
    >> once my end-user fills in the blanks. I have created a table that
    >> matches
    >> the information I am wanting to export/import. How do I then import
    >> without
    >> overwriting existing data?
    >>
    >> Thank you in advance,
    >> JR
    >>
    >>
    >>
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    Basically,

    create a macro or vba code to import (transferspreadsheet acimport)
    but make sure that it imports it into a separate table.

    Now create an update query that reads that imported table and updates
    the fields that you want it to. The trick here is to make sure that you
    have exported and then imported somefield that uniquely identifies
    which records to update. This also implies that whatever fields you are
    going to update via the spreadsheet have ALSO not been allowed to be
    updated in the access table where they reside.

    Now considerations:
    Are(is) the spreadsheet being sent to multiple people? If so, when it
    comes back how do you know whick records should be updated? If person
    A updates 5 and Person B updates 5 others, how do you know which ones
    they actually updated. You will NOT be able to update all records
    received back with person B's sheet because that would wipe out Person
    A's updates. Now what happens when both Person A and person B
    accidentially update the same record? When you get the spreadsheets
    back you will have to save the spreadsheets with different names
    otherwise you will wipe out one spreadsheet with the other. The above
    considerations are ONLY true if they both receive a copy of the same
    spreadsheet. If they are sent different sets of data then that problem
    does not occur.

    What is to happen if they change the identifying key on the spreadsheet
    so that now you do NOT know which record to update?
    What is to happen if they Add records?
    Will it cause a problem if they delete records? (therefore NO update
    information.)

    This is that infamous 95% of the code to cover 5% (or less) of the
    situations.

    Good Luck
     
  6. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    Thank you for your detailed response. With your guidance, I think I can
    accomplish what I am looking for.

    JR
    "Ron2006" <ronnemec@hotmail.com> wrote in message
    news:1148678527.990983.199270@u72g2000cwu.googlegroups.com...
    > Basically,
    >
    > create a macro or vba code to import (transferspreadsheet acimport)
    > but make sure that it imports it into a separate table.
    >
    > Now create an update query that reads that imported table and updates
    > the fields that you want it to. The trick here is to make sure that you
    > have exported and then imported somefield that uniquely identifies
    > which records to update. This also implies that whatever fields you are
    > going to update via the spreadsheet have ALSO not been allowed to be
    > updated in the access table where they reside.
    >
    > Now considerations:
    > Are(is) the spreadsheet being sent to multiple people? If so, when it
    > comes back how do you know whick records should be updated? If person
    > A updates 5 and Person B updates 5 others, how do you know which ones
    > they actually updated. You will NOT be able to update all records
    > received back with person B's sheet because that would wipe out Person
    > A's updates. Now what happens when both Person A and person B
    > accidentially update the same record? When you get the spreadsheets
    > back you will have to save the spreadsheets with different names
    > otherwise you will wipe out one spreadsheet with the other. The above
    > considerations are ONLY true if they both receive a copy of the same
    > spreadsheet. If they are sent different sets of data then that problem
    > does not occur.
    >
    > What is to happen if they change the identifying key on the spreadsheet
    > so that now you do NOT know which record to update?
    > What is to happen if they Add records?
    > Will it cause a problem if they delete records? (therefore NO update
    > information.)
    >
    > This is that infamous 95% of the code to cover 5% (or less) of the
    > situations.
    >
    > Good Luck
    >
     
  7. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    I was wrong!

    Ron - can you give me some place to "learn" how to do what we are talking
    about??

    Under considerations - of course, I need all the scenarios.

    I am transmitting a query via email to different individuals. They will
    insert the appropriate information and email back to me. I save in excel
    and NOW ----------- I need to import that information back into my access
    files. Problems that I see: (1) the information will be added to more than
    one table.(2) I may receive 10-50 emails daily that need to be imported.
    therefore, I do not want to overwrite my information.

    Each transmission has a unique control number that drives all of this. Once
    I get the information imported (without cutting and pasting), I need to send
    that info on to corporate.

    This should probably be very easy, but I am a self taught access user who
    has been fortunate in that I have been able to use wizard on most of my
    applications and know nothing about VBA or Macros.

    Thanks in advance,
    JR
    "jwr" <joyrose@bellsouth.net> wrote in message
    news:s3Zdg.2362$8e2.2198@bignews1.bellsouth.net...
    > Thank you for your detailed response. With your guidance, I think I can
    > accomplish what I am looking for.
    >
    > JR
    > "Ron2006" <ronnemec@hotmail.com> wrote in message
    > news:1148678527.990983.199270@u72g2000cwu.googlegroups.com...
    >> Basically,
    >>
    >> create a macro or vba code to import (transferspreadsheet acimport)
    >> but make sure that it imports it into a separate table.
    >>
    >> Now create an update query that reads that imported table and updates
    >> the fields that you want it to. The trick here is to make sure that you
    >> have exported and then imported somefield that uniquely identifies
    >> which records to update. This also implies that whatever fields you are
    >> going to update via the spreadsheet have ALSO not been allowed to be
    >> updated in the access table where they reside.
    >>
    >> Now considerations:
    >> Are(is) the spreadsheet being sent to multiple people? If so, when it
    >> comes back how do you know whick records should be updated? If person
    >> A updates 5 and Person B updates 5 others, how do you know which ones
    >> they actually updated. You will NOT be able to update all records
    >> received back with person B's sheet because that would wipe out Person
    >> A's updates. Now what happens when both Person A and person B
    >> accidentially update the same record? When you get the spreadsheets
    >> back you will have to save the spreadsheets with different names
    >> otherwise you will wipe out one spreadsheet with the other. The above
    >> considerations are ONLY true if they both receive a copy of the same
    >> spreadsheet. If they are sent different sets of data then that problem
    >> does not occur.
    >>
    >> What is to happen if they change the identifying key on the spreadsheet
    >> so that now you do NOT know which record to update?
    >> What is to happen if they Add records?
    >> Will it cause a problem if they delete records? (therefore NO update
    >> information.)
    >>
    >> This is that infamous 95% of the code to cover 5% (or less) of the
    >> situations.
    >>
    >> Good Luck
    >>

    >
    >
     
  8. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    I was wrong!

    Ron - can you give me some place to "learn" how to do what we are talking
    about??

    Under considerations - of course, I need all the scenarios.

    I am transmitting a query via email to different individuals. They will
    insert the appropriate information and email back to me. I save in excel
    and NOW ----------- I need to import that information back into my access
    files. Problems that I see: (1) the information will be added to more than
    one table.(2) I may receive 10-50 emails daily that need to be imported.
    therefore, I do not want to overwrite my information.

    Each transmission has a unique control number that drives all of this. Once
    I get the information imported (without cutting and pasting), I need to send
    that info on to corporate.

    This should probably be very easy, but I am a self taught access user who
    has been fortunate in that I have been able to use wizard on most of my
    applications and know nothing about VBA or Macros.

    Thanks in advance,
    JR

    "jwr" <joyrose@bellsouth.net> wrote in message
    news:s3Zdg.2362$8e2.2198@bignews1.bellsouth.net...
    > Thank you for your detailed response. With your guidance, I think I can
    > accomplish what I am looking for.
    >
    > JR
    > "Ron2006" <ronnemec@hotmail.com> wrote in message
    > news:1148678527.990983.199270@u72g2000cwu.googlegroups.com...
    >> Basically,
    >>
    >> create a macro or vba code to import (transferspreadsheet acimport)
    >> but make sure that it imports it into a separate table.
    >>
    >> Now create an update query that reads that imported table and updates
    >> the fields that you want it to. The trick here is to make sure that you
    >> have exported and then imported somefield that uniquely identifies
    >> which records to update. This also implies that whatever fields you are
    >> going to update via the spreadsheet have ALSO not been allowed to be
    >> updated in the access table where they reside.
    >>
    >> Now considerations:
    >> Are(is) the spreadsheet being sent to multiple people? If so, when it
    >> comes back how do you know whick records should be updated? If person
    >> A updates 5 and Person B updates 5 others, how do you know which ones
    >> they actually updated. You will NOT be able to update all records
    >> received back with person B's sheet because that would wipe out Person
    >> A's updates. Now what happens when both Person A and person B
    >> accidentially update the same record? When you get the spreadsheets
    >> back you will have to save the spreadsheets with different names
    >> otherwise you will wipe out one spreadsheet with the other. The above
    >> considerations are ONLY true if they both receive a copy of the same
    >> spreadsheet. If they are sent different sets of data then that problem
    >> does not occur.
    >>
    >> What is to happen if they change the identifying key on the spreadsheet
    >> so that now you do NOT know which record to update?
    >> What is to happen if they Add records?
    >> Will it cause a problem if they delete records? (therefore NO update
    >> information.)
    >>
    >> This is that infamous 95% of the code to cover 5% (or less) of the
    >> situations.
    >>
    >> Good Luck
    >>

    >
    >
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    In a sense the individual steps are easy, Your problem is that there
    are so many iterations and not a whole lot of control over what you
    receive back.

    Given that volume of users. Do they have access to Access? Otherwise
    you will spend all day just trying to save and import all of the
    various spreadsheets,

    When you are preparing to send the data to them, do you know what sets
    of information goes to which individuals. Can it be pinned down enough
    that it would be possible to create a separate spreadsheet for each of
    the individuals. That would eliminate a significant part of potential
    overlap. And if that can be done, it would really really help in the
    overall data retrieval process.
     
  10. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    Ron -

    Again, I thank you so much for your time and input. Let me give you some
    more basics. If I am repeating myself, I apologize.

    I doubt if most of them have or know access.

    We receive an order and assign one of the 50 dealers to handle each order.

    I create a query for corporate that contains every bit of information from
    the purchase order as well as information for the dealers to input their
    invoice numbers, invoice costs, serial numbers and delivery date. (These are
    blank fields until received back from dealer(s)). The query runs on the
    parameter of the control number we assign to each order -- unique to each
    order.

    At the present time, I am creating a query for corporate that contains all
    information for both corporate and dealers. Transmission to corporate is
    not until delivery is completed. The information emailed to the dealer has
    the corporate fields "hidden" on the worksheet. The dealer(s) input their
    information and return to me. At that time, I "unhide" corporate columns
    and transmit this query on to corporate containing all purchase order
    information as well as dealer delivery information.

    The fields remain the same for all dealers to use; however, information is
    unique to each control number we assign.

    I would like to be able to do something rather than hide and unhide columns;
    this does get time consuming. We also have to protect the sheet to prevent
    the dealer from "exploding" the entire worksheet which is 45+ columns. I
    have thought of a template linked to my data. Irregardless of how I do it
    though, I do not know how to import the information back into access other
    than my crude way of hiding and unhiding.

    If this is not clear, please ask again.

    JR
    "Ron2006" <ronnemec@hotmail.com> wrote in message
    news:1149017117.329376.287540@i39g2000cwa.googlegroups.com...
    > In a sense the individual steps are easy, Your problem is that there
    > are so many iterations and not a whole lot of control over what you
    > receive back.
    >
    > Given that volume of users. Do they have access to Access? Otherwise
    > you will spend all day just trying to save and import all of the
    > various spreadsheets,
    >
    > When you are preparing to send the data to them, do you know what sets
    > of information goes to which individuals. Can it be pinned down enough
    > that it would be possible to create a separate spreadsheet for each of
    > the individuals. That would eliminate a significant part of potential
    > overlap. And if that can be done, it would really really help in the
    > overall data retrieval process.
    >
     
  11. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    One more thing that I did not include:

    When I transmit to corporate, they would like to have the file by "DAY"
    rather than by control number. I can create a query whereby the information
    is selected by date, but I do not have totals for each control number. Am I
    going about this incorrectly?
    "jwr" <joyrose@bellsouth.net> wrote in message
    news:1o2fg.3426$8e2.1452@bignews1.bellsouth.net...
    > Ron -
    >
    > Again, I thank you so much for your time and input. Let me give you some
    > more basics. If I am repeating myself, I apologize.
    >
    > I doubt if most of them have or know access.
    >
    > We receive an order and assign one of the 50 dealers to handle each order.
    >
    > I create a query for corporate that contains every bit of information from
    > the purchase order as well as information for the dealers to input their
    > invoice numbers, invoice costs, serial numbers and delivery date. (These
    > are blank fields until received back from dealer(s)). The query runs on
    > the parameter of the control number we assign to each order -- unique to
    > each order.
    >
    > At the present time, I am creating a query for corporate that contains all
    > information for both corporate and dealers. Transmission to corporate is
    > not until delivery is completed. The information emailed to the dealer
    > has the corporate fields "hidden" on the worksheet. The dealer(s) input
    > their information and return to me. At that time, I "unhide" corporate
    > columns and transmit this query on to corporate containing all purchase
    > order information as well as dealer delivery information.
    >
    > The fields remain the same for all dealers to use; however, information is
    > unique to each control number we assign.
    >
    > I would like to be able to do something rather than hide and unhide
    > columns; this does get time consuming. We also have to protect the sheet
    > to prevent the dealer from "exploding" the entire worksheet which is 45+
    > columns. I have thought of a template linked to my data. Irregardless of
    > how I do it though, I do not know how to import the information back into
    > access other than my crude way of hiding and unhiding.
    >
    > If this is not clear, please ask again.
    >
    > JR
    > "Ron2006" <ronnemec@hotmail.com> wrote in message
    > news:1149017117.329376.287540@i39g2000cwa.googlegroups.com...
    >> In a sense the individual steps are easy, Your problem is that there
    >> are so many iterations and not a whole lot of control over what you
    >> receive back.
    >>
    >> Given that volume of users. Do they have access to Access? Otherwise
    >> you will spend all day just trying to save and import all of the
    >> various spreadsheets,
    >>
    >> When you are preparing to send the data to them, do you know what sets
    >> of information goes to which individuals. Can it be pinned down enough
    >> that it would be possible to create a separate spreadsheet for each of
    >> the individuals. That would eliminate a significant part of potential
    >> overlap. And if that can be done, it would really really help in the
    >> overall data retrieval process.
    >>

    >
    >
     
  12. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    screw excel

    use Data Access Pages.

    DAP.

    use Access Data Projects in conjunction with Data Access Pages.
    it's a beautiful combination.

    they fill it out; and the data is already in your database.

    if this doesn't work; hire someone to write the ASP to do it for you
     
  13. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    You are the first one to suggest this. I have never used your scenario.
    Can this be emailed to and from end user to achieve what I want?

    Thanks
    <dbahooker@hotmail.com> wrote in message
    news:1149031389.294196.197660@l28g2000cwl.googlegroups.com...
    > screw excel
    >
    > use Data Access Pages.
    >
    > DAP.
    >
    > use Access Data Projects in conjunction with Data Access Pages.
    > it's a beautiful combination.
    >
    > they fill it out; and the data is already in your database.
    >
    > if this doesn't work; hire someone to write the ASP to do it for you
    >
     
  14. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    You still have to answer the following questions.....

    The question was if they had access. They do not have to know access,
    because what they would be doing is filling out data in forms.

    The other question is when you send out the data can you distinquish
    what orders you have go with what dealers.

    What solution you pick (using ASP with data pages) or even if it could
    be a common database will be controlled by the following:

    Is everyone connected by an Intranet? Can All the dealers logon to a
    common internet or intranet web page?
     
  15. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    See below please

    "Ron2006" <ronnemec@hotmail.com> wrote in message
    news:1149078104.299327.87220@c74g2000cwc.googlegroups.com...
    > You still have to answer the following questions.....
    >
    > The question was if they had access. They do not have to know access,
    > because what they would be doing is filling out data in forms.
    >

    I do not know, but can find out.


    > The other question is when you send out the data can you distinquish
    > what orders you have go with what dealers.


    Yes


    > What solution you pick (using ASP with data pages) or even if it could
    > be a common database will be controlled by the following:
    >
    > Is everyone connected by an Intranet? Can All the dealers logon to a
    > common internet or intranet web page?
    >

    We can log on to a common page; however, I cannot (to my knowledge)
    add anything to those pages; i.e. access forms, etc. simply use their
    information.
     
  16. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    well if you're going the route of plain ASP you should try using
    Dreamweaver-- it is a bear to use; but if you cram on it for about 2
    weeks you should be able to make a plain ASP form to pull stuff from a
    db and push data back into a database.

    if it is an intranet configuration; i would look at Data Access Pages
    (via ADP) or just plain ADP data entry forms.

    the next version of access is going to have some really really really
    sharp functionality for emailing and having results emailed back... i
    dont know how that's going to help you now.

    but i would look for a short-term solution now and plan on changing it
    to access emailable forms once 2007 comes out

    i dont know if the emailable forms require office 2007; i dont think
    that they do.

    -Aaron

    jwr wrote:
    > See below please
    >
    > "Ron2006" <ronnemec@hotmail.com> wrote in message
    > news:1149078104.299327.87220@c74g2000cwc.googlegroups.com...
    > > You still have to answer the following questions.....
    > >
    > > The question was if they had access. They do not have to know access,
    > > because what they would be doing is filling out data in forms.
    > >

    > I do not know, but can find out.
    >
    >
    > > The other question is when you send out the data can you distinquish
    > > what orders you have go with what dealers.

    >
    > Yes
    >
    >
    > > What solution you pick (using ASP with data pages) or even if it could
    > > be a common database will be controlled by the following:
    > >
    > > Is everyone connected by an Intranet? Can All the dealers logon to a
    > > common internet or intranet web page?
    > >

    > We can log on to a common page; however, I cannot (to my knowledge)
    > add anything to those pages; i.e. access forms, etc. simply use their
    > information.
     
  17. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    One other question (hopefully)

    Do you have access to a network. By that I mean a "common" drive where
    you would be able to save data and ALL of the dealers would have access
    to it?

    Typically these types of drives have a lettermapping starting above K
    and going through Z..
     
  18. jwr

    jwr
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    Unfortunately, the answer is no.

    "Ron2006" <ronnemec@hotmail.com> wrote in message
    news:1149094516.942683.263040@u72g2000cwu.googlegroups.com...
    > One other question (hopefully)
    >
    > Do you have access to a network. By that I mean a "common" drive where
    > you would be able to save data and ALL of the dealers would have access
    > to it?
    >
    > Typically these types of drives have a lettermapping starting above K
    > and going through Z..
    >
     
  19. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    You don't seem to want to make it easy, (- not your problem, you have
    to work with what they give you.)

    I will get back with some thoughts. I don't want to do it on the fly,
    but will write it up and get back with you.

    Ron
     
  20. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    Without a network, then that limits the choices. I am also assuming
    that all are using outlook.
     
  21. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Import/Export/Link Access Datasheet to Excel

    jwr

    I sent you three emails.

    The last is all inclusive with notes. Should give you a place to start.
     

Share This Page