Appending to an access table

[ceemo]

I would like to be able to append to an access table through Excel. I believe this to be possible but not sure how?

[colofnature]

This example will add a new record to a table in a database with the table's name, database's name and path defined by the constants. To use this code you'll need to create a reference to the MicroSoft DAO Object Library (in VB Editor: Tools menu > References... then check the box next to the library)

Please Login or Register  to view this content.

HTH
Col

[ceemo]

thanks for the reply this lookas really helpful.

Im a little confused about how to handle the folllowing line

rs!MyField = "Blah, blah, blah..."

can i put a cell value here or a range?

an example would be useful

[colofnature]

Sorry, the 'MyField' was the name of the field I created in my test database - didn't make that clear. When you're refering to a field in a recordset the normal separator is a bang (!), so

rs!MyField

refers to the field named "MyField" in the recordset. So if you had a database with a field called "NAME", and you wanted the contents of cell A1 to be put into that field in your new record, you'd change 'rs!MyField = "Blah, blah, blah."' to

rs!name = [a1].value

{[a1] notation is shorthand for Range("A1")}. The field name is case-insensitive)


C

[ceemo]

Thanks i got it working this is really helpful and really useful code