Re: Import 'Choice' Data into Access From Excel

Tech-Archive recommends: Speed Up your PC by fixing your registry

See comments below:

"Fletcher" wrote:

First, If you have had problems with users messing with the code in Excel,
then unless you implement security or at least deliver only an MDE file, they
will still be able to do that.

The security that we are putting in place for this database is having
the forms that take up the whole screen and allowing only the engineers
to get into the system. It's not that the operators would purposfully
mess up the code, it is just that they would accidentally get into the
system and delete a cell here or hit a button there and it could be
detrimental to the way the whole thing worked.

Why do you need a different table for each machine? If there will be
differences in the field among the tables, then it is reasonable. If all
data elements are the same for all machines, then add a field that identifies
the machine and use one table.

You are the first to respond in this way. Thank you. We have
different data for each machine so a single table would be unreasonably
difficult. Everyone else who responded to a message asking for help
told me that I was going about it wrong not using relationships and the
like. I hope you respond to more of my posts.

I have done as indicated with a field identifying different machines in
tables where I can log machines that have similiar data associated with
each.

Since you have multiple operators, it will be important that you split your
database, put the back end on a share folder that all operators have
permissions to and install a copy of the front end on each operators computer.

I'm not familiar with this idea of the front end and back end, but I
don't believe that it will be an issue for our company. I don't know
if you are familiar with a citrix environment, but that is what our
company uses for its networking. Essentially it replaces computers for
each users with what we call "ding-boxes." Ding-boxes are essentially
very small computers without any drives. They have ports in the back
for monitor, mouse, keyboard, power and network cable. Users log
directly into a server from these using a name and password. This has
been especially useful in a production facility where space is minimal.

So what we have to do is publish our database onto the servers in
shared folders (as you indicated) that the operators can access if
their user name is given permission.

Splitting the database is very important in an environment like yours. I
would suggest you do some research. Basically, what happens in the split
process (Tools, Database Utilities, Database Splitter) is that all tables are
put into a different mdb with _be added to the name. For example if your mdb
is named Foobah.mdb, it will create two mdbs. Foobah_be.mdb which will
contain all your tables. Foobah.mdb will contain all other objects (forms,
reports, queries, etc). It will automatically link Foobah to the tables in
Foobah_be.

This may seem to be overkill, but it is not. It will make your life easier
for development. With a non split mdb, every time you need to add
functionality or correct an error, you have to get everybody out of the
database and move all your objects to the production database before it can
be used again. With a split database, all changes are made to a copy of the
front end. Once that is done, you just copy the new front end over the old
one.

As to getting the data from Excel, If you don't want to use queries to
manipulate the data, then cut and paste will probably do.

I have tried cutting and pasting from excel to Access and access gives
me the error that the data is too long. So what I've been doing is
going through the current spreadsheets and cutting chunks of the
desired data and pasting it to a new ***, then importing it to
access. This works, but is alot of work for little output.

The problem you are having is that Access will accept a maximum of 255
characters in a text field, but Excel does not have that restraint.

Post back if you have more questions as you move forward.


Thank you again for your help.


.

Quantcast