Import Data from Excel into Access

Hi,

I'm trying to get Excel 2007 to send some data to an existing table in Access, the amount of data can vary in size so needs to be dynamic.

Please Login or Register  to view this content.

code currently errors on the line Set rs = conn.OpenRecordset("ChannelGroupList", dbOpenTable)
as this is wrong, but i need to be able to open the record at this location!

what am i doing wrong?

thanks

Jonathan

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

You are confusing ADO and DAO. An ADODB.Connection object does not have an OpenRecordset method (nor would dbOpenTable mean anything to it as that's a DAO constant). You need something more like:

Please Login or Register  to view this content.

Hmm ok now when i try to add new data i get a different error!

Please Login or Register  to view this content.

It compiles nicely but then errors on rst.AddNew ' create a new record
with current recordset does not support updating. this may be a limitation if the provider, or of the selected locktype

Spot the difference between your code and what I posted...
(by default you get a forward only, read only cursor)

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save