Insert Records into Access DB
-
I am working on a project that pulls SQL Server data and places it into a MS Access database. The dataset contains 3438 rows, and 0 rows are inserted into Access. There are no errors when the code run, just nothing happens. The code is listed below: Public Function InsertAccessData(ByVal dsTDO_Header as dataset) As String Dim cn As New OleDbConnection(strACCDBConn) Dim strSQL As String = "" Dim DA As New OleDbDataAdapter strSQL = "Select * from TDO_HEADER" DA.SelectCommand = New OleDbCommand(strSQL, cn) DA.FillSchema(dsTDO_Header, SchemaType.Source, "TDO_Header") Dim oleCB As New OleDbCommandBuilder(DA) Try cn.Open() oleCB.GetInsertCommand() DA.Update(dsTDO_Header, "TDO_Header") ''dsTDO_Header contains 3438 records, ''DA.Update inserts 0 Records in db with no errors ''that is the problem cn.Close() cn.Dispose() DA.Dispose() Catch ex As Exception Dim strErrMess As String strErrMess = "Something didn't work right we received this update error: " & ex.Message cn.Close() cn.Dispose() DA.Dispose() Return strErrMess Exit Function End Try Return "Success" End Function Can anyone see where I went astray? Thanks in advance for any help. culbysl
-
I am working on a project that pulls SQL Server data and places it into a MS Access database. The dataset contains 3438 rows, and 0 rows are inserted into Access. There are no errors when the code run, just nothing happens. The code is listed below: Public Function InsertAccessData(ByVal dsTDO_Header as dataset) As String Dim cn As New OleDbConnection(strACCDBConn) Dim strSQL As String = "" Dim DA As New OleDbDataAdapter strSQL = "Select * from TDO_HEADER" DA.SelectCommand = New OleDbCommand(strSQL, cn) DA.FillSchema(dsTDO_Header, SchemaType.Source, "TDO_Header") Dim oleCB As New OleDbCommandBuilder(DA) Try cn.Open() oleCB.GetInsertCommand() DA.Update(dsTDO_Header, "TDO_Header") ''dsTDO_Header contains 3438 records, ''DA.Update inserts 0 Records in db with no errors ''that is the problem cn.Close() cn.Dispose() DA.Dispose() Catch ex As Exception Dim strErrMess As String strErrMess = "Something didn't work right we received this update error: " & ex.Message cn.Close() cn.Dispose() DA.Dispose() Return strErrMess Exit Function End Try Return "Success" End Function Can anyone see where I went astray? Thanks in advance for any help. culbysl
First, this is a bad way to do this. Second, the DataAdapter is looking at the status of each row in each table in your source dataset that you pass in and not seeing rows that are tagged changed, added, or deleted. So, yes, the DA is doing nothing, because it hasn't found anything to do! It doesn't know that these rows need to be added to the Access database, because it doesn't know anything about the Access database. It's looking solely at the rows you passed in the dataset. I would recommend reading up on the documentation for any of the DataAdapters to understand HOW and WHY they work the way they do.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
First, this is a bad way to do this. Second, the DataAdapter is looking at the status of each row in each table in your source dataset that you pass in and not seeing rows that are tagged changed, added, or deleted. So, yes, the DA is doing nothing, because it hasn't found anything to do! It doesn't know that these rows need to be added to the Access database, because it doesn't know anything about the Access database. It's looking solely at the rows you passed in the dataset. I would recommend reading up on the documentation for any of the DataAdapters to understand HOW and WHY they work the way they do.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Thanks for getting back to me. I'm get started learning more about dataadapters. As for your first statement, could you recommend another way of doing this? Thanks again