Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I've seen some examples on the forum using ADO in macro code. Search the forum.
-Rob
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