Update SQL server 2005 Database from closed workbook

[jmstampe]

Good day to you all. I'm fairly new to the forum so hopefully I'm not double posting anywhere as I didn't see anything regarding an inquiry I have.

To give you a little background, I have built this fairly simple, macro-based, excel tool that is connected to a SQL Server 2005 database. The tool allows the user to view customer information in a listview control. The user has the option to double click a row of customer data, bringing up a userform that would allowed them to change certain fields and also add any commentary. Once they have made their updates, they would select the "Update Information" button that would update the database. So far I have been able to get this to work perfectly as the data updates in the SQL Server table as it should.

One of the biggest requests I have been getting involves being able to export the data into a separate excel workbook for editing the fields and then being able to import the workbook back through the tool to SQL Server, updating the database. As of now, I am able to export the data into a workbook (allowing the user to choose where they would like to save it and what name they would like to call the workbook) as well as applying a lock on the data in the workbook that forces the user to only edit certain fields. I have also been successful with connecting to that workbook (while it is closed) from the excel macro tool, having the user select what sheet the data is on, and then aligning the connected workbook fields to the sql server database fields (as shown in the form below).

Field Match.png

The problem comes in applying the update to the database by selecting the "Ready to Upload" button shown in the form above. For some reason I cannot get it to work as I get the following error -- "Operation Must Use an Updateable Query." I'm not sure why this would be an issue given that I am able to update the database successfully through the listview/form option (as mentioned above). Here is the code I am using as of now along with commented code that I have tried:

Please Login or Register  to view this content.

I have even tried working with two recordsets (one from the closed excel workbook and the other the SQL Server database) to update the table and I'm not sure exactly how I would set that up.

I'm not exactly sure the best approach for this as I find it weird this doesn't work for bulk updates but the updating of a single row option does (single row update option also uses a similar Update query). Hopefully someone may be able to provide some insight and code as i'm not the best VBA programmer (hackjobs more than VBA building). Thanks in advance.

[jmstampe]

After a long battle I finally was able to figure out the issue. Seems my export to the workbook had misaligned data. Also, instead of using this connection within the update query -- [ODBC;Driver={SQL Server};Server=server;Database=database;Trusted_Connection=True;], I just created two ADODB connections (one for excel and the other for SQL Server) and looped the excel recordset through the update query, executing it to the SQL Server connection. Worked like a charm. Let me know if anyone is interested in a code sample.

[[email protected]]

Hi jmstampe,

I am very interested in what you've done here - I currently have an SQL connection in Excel which I want to update automatically when the workbook is closed.

Regards

[alansidman]

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.