Updating sets of data

[MrSandman666]

Hi, it's me again. I'm aware that you have to get sick of me slowly but surely but, hey, I always put off all my problems to the very last moment and now the deadline is approaching and I have to solve all my problems all at once and possibly until yesterday.
To get to the point:
I'm exporting contacts from an Access database to Outlook. My problem is this:
The program just adds new contacts and doesn't overwrite/update existing ones. If I hit "Export" once I get a certain amount of contacts exportet to my contacts folder in Outlook. And when I hit "Export" again, I get all the same contacts again, so that I have them all double. Outlook doesn't take care of updating existing data automaticaly. All I can do (as far as I know) is add and delete data, read data and edit data.
Is there an elegant and efficient way to update all the already existing data and simply add those contacts that aren't already in my contact folder? I don't want to do stuff like search the list of contacts once for each contact in order to check whether the contact is already existing because I oftentimes have to deal with huge amounts of data and that would take hours to process. Any ideas from any experienced programmers who aren't as burnt out as I am?

 

[rjr9999]

store your contacts names, or some other info in an array...let's call it contacts. Store your New Contact in a string...let's call that newcontact and do something like this...

for I = 1 to ubound(Contacts)
if NewContact <> Contacts(I) then
' export function here
end if
next

Or if you have multiple New Contacts being added at once, do this...

for J = 1 to ubound(NewContacts)
for I = 1 to ubound(Contacts)
if Newcontacts(J) <> Contacts(I) then
' export function here
end if
next
next

Good luck Rob
&quot;Programming is like art...It makes me feel like chopping my ear off.&quot;

 

[MrSandman666]

That's exactly what I was trying to avoid.
Let's just say the company that uses my program has a database of 3500 Clients. Now they want to export all of these to Outlook. Currenty that takes about 7 minutes and that is just putting them all in wihtout checking and everything. If I now test each new contact against each old contact, which is what you're telling me to do, the time needed to proccess this stuff would go skyrocket. You would have to sit there for hours that would be 3500^3500 operations. That is a number with 12404 zeros attached to it. Now remember: 500 contacts take one minute...
I need something much, much faster.
But thanks anyways.

 

[scorpio66]

Could this be done through careful use of SQL?

For example, (warning: I don't know how Outlook works)

'pseudo-code
LastModDate = #31/3/2001#

' Build lookup table of existing contacts
For Each Contact in Contacts
  ' insert into tempContactTable Contact.ContactID
Next

' Get new contacts
sSQL = "select * from ContactTable where ContactID not in (select ContactID from tempContactTable)"

' run the SQL, add to Outlook

' Get Modified contacts
sSQL = "select * from ContactTable where ContactID in (select ContactID from tempContactTable) and LastModDate > '" & LastModDate & "'"

' run the SQL, modify Outlook

' Get Deleted contacts
sSQL = "select ContactID from tempContactTable where ContactID not in (select ContactID from ContactTable)

' Run the SQL, modify Outlook

Chaz

 

[MrSandman666]

Hey, this could work... I have to think about it a little... SQL... Never thought of that... Good idea... I just have to figure out where I store the Outlook contacts so that I can access them via SQL quickly. Outlook itself isn't SQL capable as far as I know. I know there is a collection of contacts which can be accessed kinda like an array.
Don't know whether it'll work but it's worth a try.

Thanks a lot, I'll try that in a minute. RIght now I'm working on another problem, but that shoudln't take too long to fix.

Oh, any additional comments/suggestions are still more than welcome.

 

[scorpio66]

OK, some more comments

I originally started building a string containing the IDs of the contacts in Outlook ...

' pseudo-code follows
ContactIDs = -1
For Each Contact in Outlook.Contacts
  ContactIDs = ContactIDs & ", " & Contact.ContactID
Next

and then passing these IDs to the SQL.

  ...  and ContactID in (" & ContactIDs & ")"

The only problem is that it becomes difficult to detect deleted entries, which may or may not be a problem.

Also, with lots of entries, I suspect that this may be a little, ahem, inefficient ...

Chaz

 

[strongm]

You could try using a trick that I use quite a lot, involving a collection. It goes something like this (pseudo code):

For Each NewContact in AccessDatabase
myCollection.Add NewContact,NewContact
Next

On Error Resume Next ' ignore entries we cannot remove
For Each Contact in Outlook
myCollection.Remove Contact
Next
On Error Goto 0

This will leave you with a collection that only contains contacts that are not already in Outlook.

 

[MrSandman666]

Ok, I got a serious problem.
The contacts don't have a contact ID. They can have duplicates, etc. Each contact is defined by a set of about 72(!) Attributes, if I counted right. Since there is no ID, I have to check each contact for every single one of those 72 attributes. Now, even if I let SQL do that, that'll take a while. I guess I'm gonn ahave a little talk with my employer. I think it would be faster to just delete the old contacts and insert the new ones.

 

[scorpio66]

If you use the SQL approach detailed initially, you do not need to use a ContactID, just any unique identifier. The email address might be a good choice.

Chaz

 

[MrSandman666]

Yeah, sounds good...
I just had a talk with my employer though, and he told me to keep it simple: when the user hits the Export button, I just delete ALL the old contacts, insert the new ones and be done with it. I don't think it's very elegant but it's easy (I already have the deleting routine written out and working in another part of the program) to do and I'm lazy.
I hope he sticks with that method. If not, I'm simply gonna revive this thread

 

[scorpio66]

KISS: Always a good idea!

Good luck

 

[MrSandman666]

KISS?

 

[scorpio66]

Keep it Simple, Stupid - no offense meant

 

[MrSandman666]

Oh, yeah, I remember. Heard that before somewhere...

Well... thanks... I guess... or so...