Hi,
This is here my first post, I hope you can help me. What I do in my script part1: I extract 1 access table to a dataset. part2: I make changes to dat dataset, I add and update records. part 3: I try to update the origenal DB with the dataset, but here he gives an error:
'Operation must use an updateable query.' Who can help?
Thanks,
Dennis
Code:
'PART 1: Acces database connection
'------------------------
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Inetpub\wwwroot\q_checks.mdb"
dbConnection = New OledbConnection(connectionString)
dbConnection.Open()
query_tablesegments = "SELECT * FROM TABLESEGMENTS"
dataAdapter_tablesegments = New OledbDataAdapter(query_tablesegments, dbConnection)
dataAdapter_tablesegments.Fill(Access_DS, "TableSegments")
Access_DG_TS.DataSource = Access_DS.tables("TableSegments")
Access_DG_TS.DataBind()
'PART2: Changes made in the dataset
'----------------------------------
For Each xml_Row_ts In Xml_DS.TABLES("TABLESEGMENT").Rows
newRow = Access_DS.tables("TableSegments").NewRow()
strExpr = "MCO_CODE = '" & xml_Row_ts.Item("MCO_CODE") & "'" _
& " AND MONTH_CODE = '" & xml_Row_ts.Item("MONTH_CODE") & "'" _
& " AND TABLE_NAME = '" & xml_Row_ts.Item("TABLENAME") & "'"
selectedRows = Access_DS.tables("TableSegments").select(strExpr)
'If Row doesn't exist
if selectedRows.Length = 0 then
newRow("TABLE_NAME") = xml_Row_ts.Item("TABLENAME")
newRow("MCO_CODE") = xml_Row_ts.Item("MCO_CODE")
newRow("MONTH_CODE") = xml_Row_ts.Item("MONTH_CODE")
if (location = "MECH") then
newRow("LAST_UPDATE_MECH") = xml_Row_ts.Item("SYS_DATE")
newRow("VERSION_MECH") = xml_Row_ts.Item("VERSION")
newRow("RECORDS_MECH") = xml_Row_ts.Item("RECORDS")
'newRow("LAST_UPDATE_CUST") =
newRow("VERSION_CUST") = 0
newRow("RECORDS_CUST") = 0
else
newRow("LAST_UPDATE_CUST") = xml_Row_ts.Item("SYS_DATE")
newRow("VERSION_CUST") = xml_Row_ts.Item("VERSION")
newRow("RECORDS_CUST") = xml_Row_ts.Item("RECORDS")
'newRow("LAST_UPDATE_MECH") =
newRow("VERSION_MECH") = 0
newRow("RECORDS_MECH") = 0
end if
Access_DS.tables("TableSegments").Rows.Add(newRow)
'teslabel only needed for testing
testlabel.text = testlabel.text & "<BR>" & strExpr
end if
'If Row already exist
if selectedRows.Length = 1 then
if (location = "MECH") then
selectedRows(0).Item("LAST_UPDATE_MECH") = xml_Row_ts.Item("SYS_DATE")
selectedRows(0).Item("VERSION_MECH") = xml_Row_ts.Item("VERSION")
selectedRows(0).Item("RECORDS_MECH") = xml_Row_ts.Item("RECORDS")
else
selectedRows(0).Item("LAST_UPDATE_CUST") = xml_Row_ts.Item("SYS_DATE")
selectedRows(0).Item("VERSION_CUST") = xml_Row_ts.Item("VERSION")
selectedRows(0).Item("RECORDS_CUST") = xml_Row_ts.Item("RECORDS")
end if
end if
Next
'PART3: Update origenal access DB Should work, but doesn't work...
'-------------------------------------------------------------------
Dim commandBuilder As OleDbCommandBuilder
commandBuilder = New OleDbCommandBuilder(dataAdapter_tablesegments)
dataAdapter_tablesegments.Update(Access_DS,"TableSegments") 'error message: not an update-able query?
update access db with dataset
