A form that synchonizes two tables. 2

[ponderdj]

Hello,

As part of a greater goal, I'm tying to create a form that the user does not see that checks a table on the backend and compares it to a table with the same data structure on the front end. If a record doesn't exist on the front end that exists on the backend, run an update query to add that record to the front end.

I know to set form.visible = FALSE, use the OnTimer event to do the checking, and setup an update query, but what should I use to do the checking? DLookup? Any other options/ideas?

Thanks

 

[tonyflavell]

Why use an invisible form? Why not create and compare recordsets, or have I misunderstood what you are trying to do?

Regards

Tony

 

[ponderdj]

Don't I have to hang that code on a form that repeatedly checks to see if a new record has been added to the backend table? I thought I would do that on the OnTimer event.

And yes, I'd like to know how to create and compare recordsets, as I think it would be the correct way to do this, right now I have:

Private Sub Form_Timer()
Form.Requery

Dim FrontEndID As String
Dim strSQL As String

FrontEndID = DLookup("ID", "TBL_USERMGMTFE", "[VIEWED] = 0")
If Me.txtID > FrontEndID = True Then
'Working on This now

  End If
End Sub

This seems to be working and I'm working on the UPDATE statement now, but how would I do it comparing recordsets? I just haven't grasped the DAO or ADO thing yet.

 

[ponderdj]

OK, nevermind it isn't working.

This line:

FrontEndID = DLookup("ID", "TBL_USERMGMTFE", "[VIEWED] = 0")

Needs to find the greatest value of ID, not where VIEWED=0

Should I just scrap this and go with recordsets?

 

[PHV]

You may consider the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

Thanks PHV,

I was just searching for some kind of Max() function.

In your opinion, does DLookup cause a resource strain and should I use recordsets instead here?

 

[PHV]

The Domain functions (DLookUp, DMax, ...) are known to be slow with linked tables ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

For possible replacement, you might want to check out Trevor Best's replacement functions Domain Aggregate Functions Replacements. Syntax is the same, just change D to T (DMax -> TMax)

Roy-Vidar