im sorry to trouble you all again but i need some pointers i am having a issue with my code taking a long time to update my database table,
i load the excel document into a datagridview and then format it using code as below. its making my database jump to nea4 50 mb . and spreadsheet im sorting is about 20k rows the excel file tends to be around 1 mb. after i tidy it in access is about 300 records but even with everything in it the compression tool brings it to 800k.
can anyone advise a better way to optimize my data update to table in mdb
thanks for any help, as i said mydatabase balloons in size for almost no records , and its very slow to update but seems ok datagridviw at actually manipulating the fields? till update
i load the excel document into a datagridview and then format it using code as below. its making my database jump to nea4 50 mb . and spreadsheet im sorting is about 20k rows the excel file tends to be around 1 mb. after i tidy it in access is about 300 records but even with everything in it the compression tool brings it to 800k.
can anyone advise a better way to optimize my data update to table in mdb
adapter.AcceptChangesDuringFill = False da.AcceptChangesDuringFill = False da.AcceptChangesDuringUpdate = False adapter.AcceptChangesDuringUpdate = False _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & exPath1 & ";" & "Extended Properties=Excel 8.0;" Dim _connection As OleDbConnection = New OleDbConnection(_conn) _connection.Open() Dim dtSheets As DataTable = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim drSheet As DataRow Dim realsheet As String For Each drSheet In dtSheets.Rows TextBox1.Text = drSheet("TABLE_NAME").ToString() Next _connection.Close() realsheet = TextBox1.Text _connection.Open() _command.Connection = _connection _command.CommandText = "SELECT * FROM [" & realsheet & "]" da.SelectCommand = _command Try da.Fill(ds1, realsheet) Me.DataGridView1.DataSource = ds1 Me.DataGridView1.DataMember = realsheet Catch e1 As Exception MessageBox.Show("Import Failed") End Try Dim recordcount, pos As Integer Dim Convertme(10) As String recordcount = DataGridView1.Rows.Count - 1 Do Until pos = recordcount If IsDBNull(DataGridView1.Rows(pos).Cells("DIRECTORY_NUMBER").Value) Then DataGridView1.Rows(pos).Cells("DIRECTORY_NUMBER").Value = 0 Else End If If IsDBNull(DataGridView1.Rows(pos).Cells("TIMES_TRANSFERRED").Value) Then DataGridView1.Rows(pos).Cells("TIMES_TRANSFERRED").Value = 0 Else End If Convertme(7) = DataGridView1.Rows(pos).Cells("DATE_IN_QUEUE").Value Dim passme2 = Mid(Convertme(7), 1, 9) passme2 = CDate(passme2) & " " & Mid(Convertme(7), 10, 9) Me.DataGridView1.Rows(pos).Cells("DATE_IN_QUEUE").Value = passme2 pos = pos + 1 Loop BindingSource1.DataSource = DataGridView1.DataSource table = ds1.Tables(realsheet) DataGridView1.DataSource = Me.BindingSource1 BindingSource1.EndEdit() adapter.Update(table)
thanks for any help, as i said mydatabase balloons in size for almost no records , and its very slow to update but seems ok datagridviw at actually manipulating the fields? till update
Last edited by a moderator: