Excel to Access

[fi.or.jp.de]

Hi All,

I have working on Excel to Access updation.

I have unique number in column A.

I want to update from Excel to Access, As I have more than 10,000
records.

Please help me how to achieve this.

Thanks

 

[Clif McIrvin]

Hi All,

I have working on Excel to Access updation.

I have unique number in column A.

I want to update from Excel to Access, As I have more than 10,000
records.

Please help me how to achieve this.

Thanks

You can also try posting your question to comp.databases.ms-access or
microsoft.public.access.

 

[AB]

Lots of ways to achieve that - the simplest (and not automated) is to:
- select the range of your interest
- open the target table in MsAccess
- Edit>Paste Append
Done.

Another way is to create a linked table in access (New Table > Linked
Table) and then link it to the Excel file of your interest.

It all really depends on what exactly you need to achieve.

 

[Phil]

Lots of ways to achieve that - the simplest (and not automated) is to:
- select the range of your interest
- open the target table in MsAccess
- Edit>Paste Append
Done.

Another way is to create a linked table in access (New Table > Linked
Table) and then link it to the Excel file of your interest.

It all really depends on what exactly you need to achieve.

I think you are oversimplifying the problem.
With 10,000 lines in the Excel sheet, there must be a hell of a lot of
duplication (without any details, it is is impossible to say). So for example
if your Excel sheet has a Product, quantity & date bought, quantity and date
sold and you have just recorded the tranactions day by day, you will need to
create 2 Access tables, 1 for product and 1 for bought & sold. Creating these
tables can be done as Austris suggests. If you set the Product descriptipn as
indexed(No Duplicates) then each product will only occur once. The fun bit
comes when you have to go through all the product descriptions and manually
realise that "gren pepper" is a typo for "green pepper" and you have to
transfer the transactions for "gren pepper" to "green pepper" before you can
delete the former. Excel is for processing figures - Access is for storing &
manipupulating data.

Phil

 

[Jeff]

Meanwhile, here is a good explanation of linking Excel and Access
programmatically:
http://www.excelguru.ca/node/18

I built a similar interface for users, and after this experience
recommend limiting the number of rows a user may edit/insert at a
time.

Additionally, if both insert and update functionality are required, I
can share my approach to that -- perhaps not beautiful, but
functional.

jn

 

[AB]

Neat code, Jeff.

A question, though - is there any particular/technical reason why you
use "rst.Open INSERT..." as oppose to "cnt.Execute INSERT..."?
Wouldn't it do the same thing just with one variable less?

 

[Jeff Norville]

Ah, I shouldn't get credit for the neatness of Ken's code -- that's
Ken Puls's website. (About Ken: http://www.excelguru.ca/node/16)

However, reviewing my own spaghetti code I did use Execute (saw Ken's
page later):
SQL = 'long insert or update query
CMD.CommandText = SQL
CMD.CommandType = adCmdUnknown
'code to highlight row to be inserted...
CMD.Execute

....and I'm handling ADO error codes using an On Error handler.

While I can't remember the specific reason for specifying CommandType,
it had something to do with not knowing the exact context of all SQL
statements the interface could build -- I don't update every field in
each table, for example, and the SQL string may be either an insert or
update (if a particular "key" column is populated, for example). One
of the proper gurus on this list can go into detail there.

Cheers,
Jeff

 

[AB]

Thanks for taking the time to respond!
Cudos then goes to Ken.

A.