Exporting Access tables to Excel ...

  • Thread starter Thread starter m.cringle
  • Start date Start date
M

m.cringle

Hi guys

I need to export a number of tables into an Excel workbook so that it
can be used to input and then update (import to) my database, when
there is a large amount of data to be added.

Due to user-level security I am unable to create the usual link between
Access and Excel; Access will not allow Excel to access the tables
(even with the appropriate password).

I can manually export each table required to the workbook but this is
quite tedious - does anybody know how I can somehow automate this
process? The ideal way would be when a user clicks on a link to open
the 'bulk input sheet', all required tables are exported / updated in
the workbook.

It's the only way to prevent data inconsistencies and problems with
referential integrity

Thanks for you help
 
Hi guys

I need to export a number of tables into an Excel workbook so that it
can be used to input and then update (import to) my database, when
there is a large amount of data to be added.

Due to user-level security I am unable to create the usual link
between Access and Excel; Access will not allow Excel to access the
tables (even with the appropriate password).

I can manually export each table required to the workbook but this is
quite tedious - does anybody know how I can somehow automate this
process? The ideal way would be when a user clicks on a link to open
the 'bulk input sheet', all required tables are exported / updated in
the workbook.

It's the only way to prevent data inconsistencies and problems with
referential integrity

Thanks for you help
Click to expand...

I assume you have, but have you considered not bothering with Excel?
Access has IMO better tools for data entry than Excel.
 
Hi Joseph

I have set-up an input form for entering data into this table, which
works fine for small numbers of entries. However, often there are a
huge amount of entries.

For example, the purpose of the database is to keep track of all our
competitors; by recording the many articles / reports and recording
their key financial details and key performance indicators.

When a competitor releases a trading update or annual results we
include these figures in the database. When using the form to input we
have to constantly repeat the same information, inputting one item at a
time - by using Excel we can copy and paste these details making entry
much more efficient.

(we may have 3 figures relating to USA, then 2 for UK, then just
relating to online poker, and then relating to a different company
....etc. - so it wouldnt be any easier leaving default values in the
fields)

If you have any better ideas I would be more than grateful to hear them
 
Hi Joseph

I have set-up an input form for entering data into this table, which
works fine for small numbers of entries. However, often there are a
huge amount of entries.

For example, the purpose of the database is to keep track of all our
competitors; by recording the many articles / reports and recording
their key financial details and key performance indicators.

When a competitor releases a trading update or annual results we
include these figures in the database. When using the form to input we
have to constantly repeat the same information, inputting one item at
a time - by using Excel we can copy and paste these details making
entry much more efficient.

(we may have 3 figures relating to USA, then 2 for UK, then just
relating to online poker, and then relating to a different company
...etc. - so it wouldnt be any easier leaving default values in the
fields)

If you have any better ideas I would be more than grateful to hear
them
Click to expand...

Having been manager for a data entry unit for government, I have had to
handle large amounts of data. I would rather use Access and the tools
available there than Excel with any data I have ever seen.

It is very possible to repeat information in Access. There are several
solutions for this. You can set it up any number of ways. For example
using the Control ' will copy the last entry from that same field. You
can write code so it will copy data from the prior record for any specific
field or fields. You can program it to not copy it when you change another
filed. Of course if you are getting a lot of repeated information, I hope
you have normalized the data. :-)
 
Is there anyone who can give me some code to do this
'Docmd.TransferSpreadsheet' command, which I can adapt?

I need to be able to export a number of different tables into the same
workbook, at the same time.

I have also designed a form within Access to enter information but i
have been asked by 'the boss' to give a couple of different options!

Thanks
 
Back
Top