Custom Access database with BCM?

[Guest]

Hi,
Below is a question someone asked in a live chat back at v.1 of bcm.
(http://www.microsoft.com/technet/community/chats/trans/office/off0626.mspx)

I'd like to know if it *is* now possibleto easily pass data back and forth
from a custom access application, either in v2 or v3?
Host: Ronna
Q: Can I pass data back and forth from my custom Access application? Is
there an easy way to keep the 2 data stores in sync?

A:Not for v1, this is something that will be considered for the next version

 

[Luther]

Maybe with v3, for Account and Contact info.

They've added a couple new database views, PublicAccountsView and
PublicContactsView. A BCM dev told me the official release of v3 will
include developer's documentation and explain these views.

As far as I can tell, updating data in these views (e.g. through an
ODBC connection from Access), updates the data in BCM.

In the past I've found that some data in BCM tables can be updated
directly. For example, I wrote an application to change the CreatedDate
for activities back to the original dates following an import--the
CreateDate had been set to the time of import.

Be sure to backup your database before experimenting, as it is easy to
change something that appears safe but BCM stops working.

 

[Guest]

Luther,
The app you wrote to revert Created Dates back to original date is something
I need. Is it possible to get this from you?
(e-mail address removed)

 

[Luther]

Any program to do something like this is going to depend on the
specifics of the original data, and in my case belongs to a customer,
but the pattern goes something this:

A field (or fields) in the original data must match the records in the
ActivitiesTable (e.g. Subject).

For each record in the original data
find the record in the ActivitiesTable and update the CreatedOn
column

The SQL for the update woud be something like this:

UPDATE ActivitiesTable
SET CreatedOn = OriginalTable.CreatedOn
WHERE Subject = OriginalTable.Subject

That's somewhat simplified and depends on the original data being in a
table in the same database, but you get the idea. You can do something
in VBS, or your favorite language, to update ActivitiesData from an
Excel spreadsheet or whatever the orginal data source was.

 

[Guest]

Thanks, I'm messing around with this - I get what you're suggesting.