Update SQL Table from Excel

[mrliam69]

Starting from scratch on this one.
Our production team use a spreadsheet to manipulate data pulled into it from our SQL Server and they then want to send the updated data back to the SQL Server.
Luckily for me it is just the Production Date they want to update.
All they need from the spreadsheet is the production ID and the Date but I am stuck in how to do the SQL statement from Excel.
And I would guess that I would have to do it a row at a time from excel.

server = NRGH1
database = EXH1
table = TBL_ProductionOrders
fields = PRODID and PRODREQDATE

 

[SkipVought]

Hi,

Assumint that you have update rights to the database, you could use an UPDATE query in MS Query - Data/Get External DAta/New Database Query...

However, it will take VBA, so please report in VBA Visual Basic for Applications (Microsoft) forum707.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

As a starting point, use MS Query on a new sheet, to enter a simple Select query, and File/Return data to Excel

Turn on the macro recorder and record editing the query and return data to Excel. This will generate the conde containg the Connection string and the CommandText SQL that you just entered. This code will be the basis for your little system. Please post in Formu707.

Skip,
[sub]
[red][/red]
[/sub]