Re: Export to Excel setting field types

I am exporting using a query, but importing into a table so I can then run
queries to populate the tables and fields per our database. We have Excel
2007

I do not know how to link to the excel file. Or how I would query the data
once I did. Whatever I do it needs to be seamless, so the user doesn't have
to do anything except hit the import button in Access and select which client.

For some reason I no longer receive emails when anyone responds, I just keep
checking in ever so often. I don't want you to think I am not responding as
I should and wanted to let you know I really appreciate the help on this, I
am stumped on what the correct way to go. We will be importing a thousand
rows with 70 or so fields, so it is important I don't just slap something
together. Most of my tables are linked SQL tables, but I was planning on
making these temporary tables in Access only.
--
Cynthia


"Gina Whipp" wrote:

Cynthia,

First, I did not know you were first dumping to a table then exporting or is
that only for importing? Your initial eMail indicated you were exporting a
query, so I am a little confused. What version of Access? Setting up an
Import Specification differs depending on versions and I think this may be
what you need.

Just wondering, did you consider Jeff's idea of linking?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"cynthia" <cynthia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6EF98D8D-D4A2-48BE-BE56-4E6A80D2D65A@xxxxxxxxxxxxxxxx
I do not know what you mean by Import Specification. The only way I know
to
import from excel is to dump all the data into an existing table or let it
create one. I found that if I create the table first with all text fields
I
have better luck. But that is causing other issues and I am still not
confidant I am getting all the data across.
I have stated below what I am trying to accomplish and what problems I am
having. Quite possibly I am going about this the wrong way.

1. I have a table with 70 fields i.e. (field1 is Tag /field2 is Length
/field3 is Status)
2. Depending on the client when I dump to excel field1, 2 and 3 etc need
the name changed to what the client wants, and when I import back the
client
will have their field names and I will have to populate back to the field
name I have. Each client is different. The user selects which client
before
I do the export/import.
3. I have a matrix set up so the users can add clients and set what the
client expects for each field name, and if left blank I will not export
that
field. I intend to check the import against this matrix and stop the
import
with an error listing field names not in the matrix.
4. I can create this temp table each time before the import (sounds like
a
lot of coding with the matrix). If I create this table before the import
having a temp table for each client using the matrix (which the users can
modify) I need to write code to update field names or remove fields for a
client when the matrix is updated. I cannot find any way to remove a
field
name from an existing table.
5. Is there a way to write code so it finds the excel field name and I
can
change it before importing to the table?
--
Cynthia


"Gina Whipp" wrote:

cynthia,

Try setting up an Import Specification. Doing it that way you can set
the
Data Type of your fields and it will no longer matter if there is data in
them or not.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"cynthia" <cynthia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:286F7A68-20F9-48E7-8187-1B96D4F19AF3@xxxxxxxxxxxxxxxx
I am exporting a query out to excel. The client then fills in the
fields
and
we import the data back into a temp table that we sort through before
actually populating the assorted tables. My problem is when I export
to
excel all fields show as general in excel, then when I import back if
the
first 50 or so fields are blank it sets the field to a number type and
the
text fields do not import.

Any help would be greatly appreciated.
--
Cynthia


.



.

.

Relevant Pages

  • Re: Export to Excel setting field types
    ... You are grabbing your Excel Spreadsheet ... "Gina Whipp" wrote: ... client excel file match to my table fields, but I will need to find the ... that only for importing? ...
    (microsoft.public.access.formscoding)
  • Re: Export to Excel setting field types
    ... "Gina Whipp" wrote: ... client excel file match to my table fields, but I will need to find the ... way to get the data from excel to a table without chancing loosing data. ... that only for importing? ...
    (microsoft.public.access.formscoding)
  • Import and exporting Contracts
    ... importing or exporting contacts, the email addresses will ... When exporting contacts to an Excel file, ... they show up in Outlook similar to ...
    (microsoft.public.outlook.contacts)
  • Re: Export to Excel setting field types
    ... I did not know you were first dumping to a table then exporting or is ... the name changed to what the client wants, and when I import back the ... Is there a way to write code so it finds the excel field name and I ... change it before importing to the table? ...
    (microsoft.public.access.formscoding)
  • From XP back to 2000?
    ... I was considering exporting every object to Excel and ... then importing in the old version of access, but kind of a chore. ...
    (microsoft.public.access.externaldata)