Importing data from excel spreadsheets

[Pwyd]

I'm using a 2003 version of access, and 2003 version of excel. I have the
database importing a spreadsheet to an append table, and letting the append
table populate the work table. my problem is i need a LOT more flexibility
in what the table can import. Unless each person specially prepares every
worksheet they want to import, it rejects the whole thing. You cannot
convince me that access does not address this issue -- it would be
practically worthless without the ability to do this. Should i be importing
into a new table, copying the data, and deleting the import table? someone
help

 

[Klatuu]

Avoid Make Table queries when you can. Repeatedly adding and deleting tables
contributes to bloat. However, sometimes it can be necessary.

What do you mean by specially preparing a worksheet? If there is no
consistency to how the data is organized, Access may not be able to deal with
it correctly. Access and Excel are two different animals. Excel allows a
lot of things Access cannot allow. They are two different worlds. Excel is
free form data. Access is a relational database application.

The last resort is to use Automation to read the data in the spreadsheet and
write it to Access tables, but even then, if you don't know what the data is
going to look like, you can't accurately import it.

 

[Pwyd]

by specially preparing i mean deleting all the columns that aren't going to
be used, and selecting whihc records they want to import, saving it in a
seperate sheet. its 15 minutes of work for every audit and i wanted to
remove it.
How does adding tehn deleting a table bloat the database? aren't these
things esentially like linked lists but using objects ? won't the space be
recaptured?

 

[Klatuu]

The space may or may not be recaptured correctly, but if you do periodic
compact and repair it should not be a problem.

Which columns to use can be handled, which records to include can't. The
user will have to designate which records to include. Maybe a column in the
worksheet that would indicate which to include would speed the process.

One way to handle this would be to link to the worksheet and use an append
query to import the columns you want and filter it on the column I recommened
to determine which rows to include.

 

[Pwyd]

Let me give you some details of this process, and my options. Perhaps that
will help most.
We do our audits by the following process:
An excel spreadsheet containing our audit data for the month is sent to a
company in the UK. After several minutes, it spits back a formatted sheet
containing some sum data and so on. The formatted sheet has its own name and
date (so basically in order to import it, it's going ot have to be input
manually, somewhere.) These names and dates change. The format of the
sheets does not. The process i have to use on the sheets, when they are
received, does not. But it comes to this: either each person has to have
their own copy of the database, and get a piece of the formatted spreadsheet,
process it themselves, and then use the database, or, (and this is what i
would prefer. i can compact the database each time on exit, thats not an
issue.) or the Person who currently submits the spreadsheets to be formatted
could import the entire thing into the database, and let each person use a
replica to filter the records and edit the ones relevant to them.
I'll take the first if its going to be much too complicated to get the
second. Does that help?

I was having serious issues importing it properly. So i was going to have
the user import it inot a new table, and make sure the new table always had
the same name so i could refer to it in a query which i use for filtering and
displaying the data.
Am i going about this the wrong way? Its honestly been years since i've
worked with this stuff.

 

[Klatuu]

First, I suspect you are not using a split database. You absolutely should
be. The correct configuration is to have the back end database (contains only
data) on a shared folder on the network. Each user should have a copy of the
front end on their own computer. This way, all users will use the same data.

If all the spreadsheets returned go into the same folder, it would be helpful.

As to locating the files, here is a link to an API that will allow the users
to locate the file they want to use.

I would also prefer the second option were I you. Have one person do all
the imports and the others filter records. Not a problem, but it is an
absolutly necessary you split your database.
When using a split database, compact on close only compacts the front end.
You will have to manually compact and repair the back end, but using the
method I suggested, that wont have to be that often.

http://www.mvps.org/access/api/api0001.htm

 

[Pwyd]

All right. what is the procedure for splitting the database, besides
following the wizard instructions. What will it leave me with, and how do i
proceed to give each person a copy of the front end. does it need to stay in
a certain location in order to stay synched? what other maintenance will i
have to perform on a split database?
Thanks for the link, too. No, it doesn't go to the same folder, but if just
one person is doing the importing, it won't matter, they can root around foR
It.

 

[Klatuu]

Following the wizard is most of what you need to do. When it is completed,
the front end will already be linked. There is one issue you do have to be
aware of. That is Drive mapping. If all users do not have the same drive
mapped, those with a different drive mapped will not be able to use the
application without relinking.
The best approach is to use UNC mapping rather than Drive Letter Mapping.
For example, you may have the folder mapped as:
M:\MyAccessApp\someapp.mdb

Instead, use the network mapping:
\\SomeServer\MyAccessApp\someapp.mdb

There are numerous ways to distribute the app to the users. The most simple
is to put a copy in a shared folder and have them copy it to there computer.

Another is to use a Shortcut to a batch file to do the copying.

And, the most sophisticated is an auto update program that autmatically
copies new versions into place.

 

[Pwyd]

All right. however. after the original has imported the data, i will not have
to do further maintenance on the copies beyond having them compact
themselves? i mean, does access take care of the updating between the copies
and the original in a split database? (i'm copying just the front end right?)
I've read all of the information concerning how it determines record
conflicts -- that shoudln't be an issue here, they won't be making any new
records, and each person only edits one filtered set at a time, they'll never
be editing the same records except by accident.

 

[Klatuu]

No maintenance is necessary on the copies the users use. That would be the
front end. The front end probably will not need compacting unless you are
using local data.

There is no updating between copies. All users share the same data in the
back end file. That should be compacted as needed. Just keep an eye on
growth. This, in fact, is why it is necessary to split - so all users have
access to the same data.

 

[Pwyd]

Thanks so much for your help. I've never designed databases this complicated
before, and i must say without you guys, yourself especially, I would not
have been able to do it. I really appreciate it. If i have trouble with the
API i know where i'll go first. That'll be tomorrow's task.
Thanks again.

 

[Klatuu]

Good luck.

The API is not hard to use. There are some examples built into the code
that should give you some ideas.