1) Is it a normal procedure to use ADODB.Recordset to modify database in Access. Is there some way know when updating is 'ready'(?) ?
There is some strange problem with my system with it. The code below works erratically: the table "tbl_kansallisuus" is always updated when the code is executed, but it takes an irregular interval before database is updated. Usually about 1 second.
I had this same problem in other places in my application too. It seems that when editing the databse with ADODB.Recodset, Access fails to see modifications until some time later, no matter how much I call for 'requery' to the form. Also If I open Access table-window, and then run a VBA code, which modifies that table's data, the table is not updated. But when I close the table window, and open it again, data is updated.
Could this be some kind of bug in Access 2002?
-------example code:
Private Sub combo1_NotInList(NewData As String, Response As Integer)
Dim intReturn As Integer
'----------------database connection starts
On Error GoTo btnTest1_Click_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database
Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection
With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With
Set rst = New ADODB.Recordset
'----------------database connection completed
'---------------add record start
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tbl_kansallisuus", CurConn, , , adCmdTable
With rst
'Add new record to end of Recordset object
.AddNew
![txt] = NewData
.Update 'Save changes
End With
rst.Close
'---------------add record completed
acFormAdd, acDialog, NewData
Response = acDataErrAdded
New_Customer_Click_Exit:
Exit Sub
btnTest1_Click_Err:
MsgBox Err.Description
Resume New_Customer_Click_Exit
End Sub
There is some strange problem with my system with it. The code below works erratically: the table "tbl_kansallisuus" is always updated when the code is executed, but it takes an irregular interval before database is updated. Usually about 1 second.
I had this same problem in other places in my application too. It seems that when editing the databse with ADODB.Recodset, Access fails to see modifications until some time later, no matter how much I call for 'requery' to the form. Also If I open Access table-window, and then run a VBA code, which modifies that table's data, the table is not updated. But when I close the table window, and open it again, data is updated.
Could this be some kind of bug in Access 2002?
-------example code:
Private Sub combo1_NotInList(NewData As String, Response As Integer)
Dim intReturn As Integer
'----------------database connection starts
On Error GoTo btnTest1_Click_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database
Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection
With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With
Set rst = New ADODB.Recordset
'----------------database connection completed
'---------------add record start
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tbl_kansallisuus", CurConn, , , adCmdTable
With rst
'Add new record to end of Recordset object
.AddNew
![txt] = NewData
.Update 'Save changes
End With
rst.Close
'---------------add record completed
acFormAdd, acDialog, NewData
Response = acDataErrAdded
New_Customer_Click_Exit:
Exit Sub
btnTest1_Click_Err:
MsgBox Err.Description
Resume New_Customer_Click_Exit
End Sub
age Timeout