Jet Database Driver and VB6 problems 1

[xterra]

This may be a bug but I can't find anything telling how to fix it. I am using Jet 4.0 with Access 2000 and VB6. I am updating a record and imediatly requerying the database to show the changes in a listbox. My problem, is that the changes are not showing. I look in the database and the change is there, its like VB showing the info before access can see the changes. When I step through the code, it works fine, but when I let it run on its own, the changes are not shown in the listbox, but in access. If anyone knows how to fix this please let me know.

Thanks

Adam

 

[MarcLara]

how is this listbox populated? is it by additem method? connected to a data object? etc.

 

[Hungoverhippie]

try putting a DoEvents in right after you write the record then do your requery..

if that doesnt do it would have to see the code
Hungoverhippie

 

[xterra]

The listbox is populated with an additem method for each record in my recordset. It is not connected, it is unbound.

I tried the doevents already and that doesn't work but I did find something that works but it sucks. If I put a loop in to loop from 1 to 1000000 right after I add the record, it sees the changes, but one 0 less and it doesn't. It seems like some kind of timing bug in Access. Got any ideas?

Adam

 

[krank]

Before Access 2000 Access always had a delay before the internal buffer was flushed. If using DAO this can be forced (check the documentation).

However, using ADO with Jet 4.0 and A2000 you should be able use resync on the recordset update call and see the changes immediately. Haven't tried myself but thats what the documentation tells me.

 

[xterra]

When I call the resync I get this message

Error Number - 3251
Error Description - Current Provider does not support refreshing underlying values.

I am using the Jet 4.0 provider, so it should work. Does my cursor need to be client side? I looked at the help file for Resync but it doesn't help any.

Thanks,

Adam

 

[MarcLara]

Adam,
Can we see your connection code to the database and how you are loading the listbox?

 

[xterra]

Sure, I am using classes in a 3 tier design, and storing my connectionstring in the registry but here is what I am using

my connectionstring in my registry is
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Maytown\Maytown.mdb;Persist Security Info=False

Private Sub Class_Initialize()

m_Connection.Open GetSetting("Maytown", "Login", "ConnectionString&quot

End Sub

This one updates the record

Public Function Update(ByVal objBusCustomer As clsBusCustomer) As Boolean
On Error GoTo UpdateError

Dim rsRecordset As New Recordset
Dim lngcounter As Long

With rsRecordset

.Open "SELECT * FROM Customers WHERE CustomerID = " & objBusCustomer.CustomerID, m_Connection, adOpenKeyset, adLockOptimistic, 1

If Not .EOF Then

SetRecordFields objBusCustomer, rsRecordset

.Update

.Resync 'this is what I just added but it doesn't work

Update = True

.Close

Else

Err.Raise modErrors.errCustomerNotFound, "clsDBCustomer.Update", "Could not Find Customer."

End If


End With

Set rsRecordset = Nothing

Exit Function
UpdateError:

Update = False

modErrors.ErrorNumber = Err.Number
modErrors.ErrorDescription = Err.Description
modErrors.ErrorSource = Err.Source

End Function

This is what loads my array, which I use in my User object to load the listbox

Public Sub LoadCompanyNameArray(aryCustomers() As String)
'Load the array with a list of customers

Dim rsCustomer As New Recordset
Dim lngcounter As Long

Erase aryCustomers

With rsCustomer

lngcounter = 0

.Open "SELECT Company_Name FROM Customers", m_Connection, adOpenKeyset, adLockOptimistic, 1

Do Until .EOF

lngcounter = lngcounter + 1

ReDim Preserve aryCustomers(lngcounter)

aryCustomers(lngcounter) = !Company_Name

.MoveNext

Loop

.Close

End With

Set rsCustomer = Nothing

End Sub

I know this seems like alot, but it does work, I use this in SQL Server 2000 and it works perfect, so I know it has to be some problem with ADO and the Jet 4.0 Driver.

Thank you,

Adam

 

[GeordieGeek]

Try updating your Jet 4 drivers with Service 5 see Q239114.
I'm pretty sure that I remember seeing that the record changes are not immediately visible with early versions.

 

[xterra]

You are right, it is supposed to fix the problem, but it doesn't work. I did exactly like the article said, I checked the dll version and it still doens't work. I think it is something in XP, I am going to try it on a 2000 machine and see if it works. If anyone is interested here is the article

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q282010

Thanks for all your help,

Adam