Export from Excel to Access

Hi, I'm trying to export data from Excel to a Database in Access, this is the code i have at the moment

Please Login or Register  to view this content.

Now, if the date thats on the Header Sheet (B6) in excel matches the date in Access, it will overwrite the data (Customer name, customer account etc), but if it doesnt see that date (in Access) in the Date field (in excel), it will create a new record in access.

Now the problem is, when it gets to that IF statement, if brings up an error that no data is in that record if the database table is empty, i would have thought the else would kick in.

Any ideas how to get past this?

Many Thanks

David

Hi Dave31,

firstly would it not just be easier to create an import query in access?

if not,

then i think what would prob be best to do is to create a database connection with your access database and then use a SQL INSERT INTO query to enter the data into the relivant table.

im sorry i dont have the code for this right now as am at work but im sure if you google it you will find plenty like "VBA Data access" or something

Thanks for your reply Stuie. amd sorry for the delayed response.

The only problem with doing the update query, theres a few conditions, like if the datatable is empty, and if to overwirte the data, or paste it at the end, so i was planning on having a button on the excel sheet and having this automated thro vba.
It could be easier to do it your way, but im not sure how, and i believe the way ive done it is close to working.
So i was hoping somebody had any idea about my vba and what ive done wrong, although, if you think it will still easier (and my code isnt close to working), then please let me know and ideas how i can get around the conditions using the update query - My access knowlege isnt as strong as excel

Thanks in Advance

David

If your column headings match your fieldnames, then you could use a named range to insert into the mdb. This example doesn't check for an existing date that equals your date in excel.

Your method might work if you iterated through all of your records. I could post some code where I used code similar to yours if you like.

If you can get SQL to do it all, that would be best of course.

Please Login or Register  to view this content.