Insert / Update data into MSSQL

[#1]

Hi there,

Does anyone know how to insert / update data directly from QV into MSSQL. Let's say I have done a what-if on my budgets and wanted to save that back into my DB.

Regards,

Werner

[rwunderlich]

I've seen some examples on the forum using ADO in macro code. Search the forum.

-Rob

[#3 · Accepted solution]

Hello Werner,

here are an example of macro code for writing back into a databse.

What you have to know is that I´m not able to comment this (i´m not a vba guy).

sub save

Dim mConnection
Dim sqlstr


Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Database\DBNeu.mdb"

rem check all rows
set table = ActiveDocument.GetSheetObject( "TB01" )
for RowIter = 1 to table.GetRowCount-1
set ID = table.GetCell(RowIter,0)
set OStatus = table.GetCell(RowIter,1)
set NStatus = table.GetCell(RowIter,2)

rem check for changes and write back into database
if OStatus.text <> NStatus.text then
sqlstr = "update fonds set Status=" & NStatus.Text & " where ID=" & ID.Text
mConnection.Execute sqlstr
table.SetInputFieldCell RowIter-1,1,NStatus.text
end if
next

rem close database connection
Set mConnection = Nothing


end sub

Good luck!

Rainer