Import/Export/Link Access Datasheet to Excel

[jwr]

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

 

[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.

 

[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.

 

[jwr]

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

 

[jwr]

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

 

[Ron2006]

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

 

[jwr]

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

JR

 

[jwr]

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]

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

 

[Ron2006]

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.

 

[jwr]

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

 

[jwr]

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?

 

[dbahooker]

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

 

[jwr]

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

 

[Ron2006]

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?

 

[jwr]

See below please

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.

 

[dbahooker]

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

 

[Ron2006]

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..

 

[jwr]

Unfortunately, the answer is no.

 

[Ron2006]

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