Excell link table format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that this is a common question and it always gets the same answer, but
it won't work in my situation. I have one of those situations where, when
linking a table from Excell, it calls a text field as numbers. I am currently
the only user who needs to do this, but we want to add up to 7 users soon. It
would be done at the begining of the day (maybe 2 or 3 times if necccessary).
Is there a way to programicly do this? Or create a routine (macro?) to empty
the current import table and re-update it? There are several reasons why I
can't alter the table:

1) The table is generated by a 3-rd party program and it must access this
table, so i CAN'T alter it.
2.) there are several Access users who will be linking, daily.
3.) I am the only person in our plant who would understand how to "import"
to everyone's computer. There are no Technoids who would understand how to
update, daily, the import table. If I am off on a business trip or vacation,
no one can doo this.

I prefer to do it as a linked table so if we need to update it several times
a day, we can. If we use a macro, we can have the controller of this Excell
spreadsheet to e-mail update notifications. It might work as a macro but i
can't figure out how to do the macro, with it's limited selections.

On my current Import table every morning I:
1.) Delete all records in in the table I call tblImport. This eliminates
deleted data and adds any updated entries.
2.) Go thru the Import Table wizzer to re-update it.
3.) i can then do my queery to do my dirty work.
 
I am confused about what you want to do. Your post mentions linking to an
EXCEL spreadsheet, and it also mentions importing the data from an EXCEL
spreadsheet. Which is it?

As for automating the process, this can be done via VBA for either
situation, and via macro for importing the sheet. In a macro, you can run a
saved delete query using OpenQuery action, and you can run the import of the
spreadsheet by TransferSpreadsheet action.
 
I'd prefer to link the table so it would imediately update if any changes
were to be made (unless you can't update the spreadsheet while there are
active links to it - that's why I mention the import table). I am lousey with
programming and not sure I corrrectly understand the query appoach. Any
guidance or codework would be appreaciated.
Thanks,
Jackle.
 
I still am not sure of what you want to do. If you link to the spreadsheet,
you will not be able to make any changes directly in the spreadsheet (via
EXCEL) while ACCESS has the spreadsheet "open" as a table (e.g., you have
the the table open in datasheet view, you're running a query that uses the
spreadsheet, etc.).

Where will data changes be made: in ACCESS or in EXCEL?

It will be very helpful if you can specify exactly what you want to
accomplish so that we might suggest ways to make that happen.
--

Ken Snell
<MS ACCESS MVP>
 
What I have found is Access looks at the first row or few
rows of the excel data and assumes a numeric or
alphanumeric format. Either make sure the first row of
data in Excel is the proper format or insert a properly
formatted dummy row as the first row. You can delete it
later manually or with a delete query.

Chris
 
Acccess will not make any changes to the spreadsheet "table." It may in the
future, but that might only be adding a comment field back to a different
spreadsheet. We CANNOT alter the original spreadsheet, or it will conflict
with the program the creates and uses it.

In a nutshell, Access will link the data from the spreadsheet "table" (a
shortsheet table) and run a queery comparing it to completed production
operations in a B.I. table so we can tell which operations of expedite parts
are finished and which are unfinished. This Shortsheet table is only made
first thing in the morning. Every supervisor checks this thru Epicore's
Avante program line by line. This means we have up to five supervisors doing
this for up to an hour each, every day.

Since I have Access, I can do this in seconds. I have the brainpower to do
an import tableand update it everyday, they don't. I am on vacation this week
so this is something that wouldn't get done this week. Now we want to get
Access installed on their computers to do this, and other things that I do.
The time it would save in a week would pay for each copy of Access.

That's why I want to do whichever is the easiest to set up and have the
other supervisors to run. If they can press a button to empy out the existing
imported table and import the new one, great. But if it is easier to swap out
the original spreadsheet without conflicting with the other supers, great.
Thanks for the help Ken, I think this will help. It's a shame that Microsoft
does't allow a user to redirect the link table's format, or at least for it
to read Excell's format better.
 
Thank's for the help, but I cannot alter the original table due to another
program that read it. Also that means someone has to alter it before it can
be used. So I am off this week and no one is "smart" enough to do this, if
it would work. Without any other "technoids" I'd have to do the work daily.
That's why i'd like to see if I can do it thru a link or import. So it must
read the work orders as text, not numbers. If it doesn't, it won't run the
queery or relationships correctly. The other program that i link to treats
these as text. I just Microsoft could fix this popular snafu. And then they
could also include a mousewheel controller, so you don't have to input the
control yourself. Thanks anyway.

Chris Reveille said:
What I have found is Access looks at the first row or few
rows of the excel data and assumes a numeric or
alphanumeric format. Either make sure the first row of
data in Excel is the proper format or insert a properly
formatted dummy row as the first row. You can delete it
later manually or with a delete query.

Chris
Click to expand...
 
What you want to do is farily simple to do. The concept will be to import
the spreadsheet's data into a temporary table (I will call this table
tblTempExcel for the purpose of this example), append the data into a
permanent table (I will call this table tblExcel), and then delete the
temporary table. My example does this using a macro, as that is what you
asked to use.

(1) Create the table tblExcel that will hold the imported data from the
spreadsheet.

(2) Create a delete query that will delete all records from the tblExcel
table. I will call this query qry_del_tblExcel. The SQL statement for this
query will be similar to this:
DELETE *
FROM tblExcel;

(3) Create an append query that will copy data from a temporary table into
this permanent table. I will call this query qry_app_tblExcel. The SQL
statement for this query will be similar to this:
INSERT INTO tblExcel.*
SELECT tblTempExcel.*
FROM tblTempExcel;

(4) Because you say you want to use a macro to do this, the macro should
contain the following actions:

Action: SetWarnings
Warnings On: No

Action: DeleteObject
Object Type: Table
Object Name: tblTempExcel

Action: TransferSpreadsheet
Transfer Type: Import
Spreadsheet Type: Microsoft Excel 8-10
Table Name: tblTempExcel
File Name: (path to EXCEL file, including filename)
Has Field Names: (set to yes or no depending upon what the sheet
contains)
Range: WorksheetName! (if you want to impor a specific worksheet from
the file)

Action: OpenQuery
Query Name: qry_del_tblExcel
Data Mode: Edit

Action: OpenQuery
Query Name: qry_app_tblExcel
Data Mode: Edit

Action: DeleteObject
Object Type: Table
Object Name: tblTempExcel

Action: SetWarnings
Warnings On: Yes


(5) Attach the query to a command button on a form so that your users can
run the macro when needed.
--

Ken Snell
<MS ACCESS MVP>
 
Looks good and thourough. I'll give it a try. Thanks for all of the help.
It's greatly appreciated.
 
Back
Top