ADO Update Delay 1

[Dmcniell]

Hello and thanks for your help.

This is a question involving access and ADO.

I have a nagging problem with what seems to be a delay between the instant I add a record with ADO and the ability to refresh the informatin into another form. I'll try to describe the steps.

From customer_form I have a button that allows the user to add another contact record onto the customer (as customers can have multiple contacts). When they hit the button, I bring up a separate form, contact_form, that allows the user to type in contact info. When they click DONE, I use ADO to create a record linking the customer with the newly entered contact row.

All of that works. My problem is this: When I return to the customer_form and do a Me.Refresh command, the newly added and linked data does not show (leading the user to believe their data was not added). However, if I keep refreshing (clicking a refresh button multiple times) the contact info eventually shows up. It's unpredictable, but usually around 2 to 3 seconds before the form can see the new data.

Here is the code that adds the linking record:

Dim curConn As New ADODB.Connection
Dim rowSet As New ADODB.Recordset
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 rowSet = New ADODB.Recordset
rowSet.CursorType = adOpenDynamic
rowSet.LockType = adLockOptimistic

' Open the table that was passed in in newLinkTable and add
' the linking information to that table

rowSet.Open "[" & newLinkTable & "]", curConn, , , adCmdTable

With rowSet
'Add a new parent record for the newly created contact record
.AddNew

If newLinkField = "client_uid" Then
![Client_uid] = newLinkUID
ElseIf newLinkField = "donor_uid" Then
![donor_uid] = newLinkUID
End If

![Person_uid] = Me![Person.uid]

![created_date] = Date
![created_by] = Environ("Username")
![last_changed_date] = Date
![last_changed_by] = Environ("Username")

.Update
End With

rowSet.Close
curConn.Close

Thanks again for your help and for going through this long explanation.

 

[Tranman]

Dmcniell,
Well, all of that instantiating and setting and updating a dynamically linked table via a recordset must be taking some time...

Why not try just inserting the data directly with SQL and see if it does any better? Something like:

Select case newLinkField
Case client_uid
CurrentProject.Connection.Execute "Insert into
newLinkTable Values (newLinkUID,Null,Me![Person.uid],
Date,Environ("Username"),Date,Environ("Username"))
Case donor_uid
CurrentProject.Connection.Execute "Insert into
newLinkTable Values (Null,newLinkUID,Me![Person.uid],
Date,Environ("Username"),Date,Environ("Username"))
End Select

I don't know your table structure, so there may be a bunch of other fields to initialize, and you might have to hose with single quotes and pound signs to get the strings and dates to take, but my guess is that this method would be screaming fast...

Tranman