Overwrite access database from excel table using VBA

When you say "overwrite" do you want to only update the items you have in red? or completely erase the table and re-populate it?

I you just want to update those fields, then this would work...:

Please Login or Register  to view this content.

PS, welcome to the forum

I have 10000 of records in my access table, in that I retrieved some records (Eg : 10 records) using filters and change the values and want to overwrite only those 10 records in existing access database using ID number.

Irrespective of the changes in any column, I want to replace the entire row of a particular ID #

The principle is the same, you just need to expand the sql statement to include all columns and cell references. I'll try to send something updated this afternoon.

Please Login or Register  to view this content.

Thanks for your program, In real case senario, I've more than 150 columns in the table, Do i need to declare all the column names in the SQL statement, if there anyway, we can simplify it??

FYI - For fresh uploading, I didn't declare any column names.

Program given below for upload new data from excel to access. Can we get anything like this for updating existing data??

Please Login or Register  to view this content.

-------------

When i tried with your program, getting error in the following line (screen shot attached below). Thanks in advance for sorting out the error.

Debug.JPG

About the error: I tested it with the sample database you provided... are you running the code on the same database and excel file that you uploaded originally?

As far as simpifying, are the Excel columns in the same order as the database fields? We could probably come up with a loop to update recordset fields and do an update, rather than using sql statements. Could you create a copy of the real table? Doesn't need more than one or 2 dummy records, so we can test it out a little better.

Actually nevermind the sample database... give this a shot:

Please Login or Register  to view this content.

You could add a check before updating that the recordcount of rset1 is 1. If it is 0 then no matches were found for that ID, and if it is >1 then you have multiple records with the same ID. However, if it is a straight download, and the ID column has unique values only, then you should be fine.

Code Sub update_db_v2() worked well for the sample database. When i tried it with real database, getting some error.

I've attached the real database for your review.

Thanks for all your support

Which sheet in that new book is the one used to update the database?
And what error are you getting?

Well, wasn't getting an answer, so I guessed it is the Import sheet?

A few things... ID is now column B not A, so that needed updating. Also, the loop now needs to start at 10 not 2, since that is the first row with data:

Please Login or Register  to view this content.

@n21crowns

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)