save excel in access

hi,

How do i save my data to access from excel. This will import from access.

Please Login or Register  to view this content.

You may use this code (in this example I write rows from 2 to 10 and columns from 1 to 5 in an Access table):

Please Login or Register  to view this content.

Regards,
Antonio

You can also use this methos for update:

Please Login or Register  to view this content.

Regards,
Antonio

thanks very much.

What about transaction processing . I want to update data to 3 tables and want to do it at once.

Now I could either use the access cascades on update I believe or use some sort of transaction staement from VBA which I prefer. Can I do this?

I get this error with the code
//item cannot be found in colection corresponding to name or ordinal ? on col row 2,2 where data exsits? I set up table 2 with 1 field.
I set up 5 cols and 10 rows of numbers.

Please Login or Register  to view this content.

When you open a recordset for update you need to do attention at the number of fields you reference in the select instruction.

For example, if I do:

Please Login or Register  to view this content.

May be you get error because in your 'select' instruction you referenced less than 5 fields and when code try to set rs(4) value it gets error.

Regards,
Antonio

Please Login or Register  to view this content.

this is failing on your first iteration because the ordinal is not in your collection. Inthe first pass, C=1. You set your recordset=C-1 (which evaluates to 0). The collection does not hold a value for this position in the recordset which is why you get the error saying the ordinal is not in your collection.

Hi,

The previous posts have contradicted each other (as it seems to me but correct me if I am wrong. One says I can have
rs(c - 1) = Cells(r, c) // where rs(0) is the 1st and the other says i can't have rs(0)?


/...
Hang on rs(0) is all I need as i have less than fields. Lt me check my code again.

Ok it works fine thanks.

Can I do some transaction statments from VBA as well. Access has cascades on deletes etc but I prefer to do them manually.

I want to update data over 3 tables .