Accessing Custom Fields via MS SQL 2005 using MS Access Front End

[zMendel]

Dear Forum:

GOAL:
I want to use MS Access to write reports based on the BCM SQL database. I
need to utilize the standard fields in BCM, plus any custom fields that I
create.

APPROACH TO DATE:
1. I have set up MS Business Contact Manager and it is running well.
2. I have added several custom fields in the Accounts form. That works
fine as well.
3. I have also used MS Access to connect to the BCM SQL database. I am
connected to the database and can see all of the tables and views. I have
used Access as a front end to MS SQL via an Access Project.
4. When I pull up a view in Access (i.e. AccountIMAPIview) I can see the
Account data, as well as the custom fields that are called UserField(1 - 40).
I can even see SOME of the data that I entered in the custom fields and that
is my problem.

PROBLEM I AM HAVING:
The problem I am having is that only certain custom fields appear to be
visible in the Access DBS. It looks like only a Text or Drop Down custom
field is visible in the Access DBS. The other fields (i.e. Number, Percent,
Integer) have a ?A in the field. It appears as if Access or SQL cannot
recognize the custom field that was created.

Does anyone know how to solve this issue, or have any other suggested
methodologies.

Regards,
zMendel

 

[Luther]

Dear Forum:

GOAL:  
I want to use MS Access to write reports based on the BCM SQL database.   I
need to utilize the standard fields in BCM, plus any custom fields that I
create.

APPROACH TO DATE:
1.  I have set up MS Business Contact Manager and it is running well.  
2.  I have added several custom fields in the Accounts form.  That works
fine as well.  
3.  I have also used MS Access to connect to the BCM SQL database. I am
connected to the database and can see all of the tables and views.  I have
used Access as a front end to MS SQL via an Access Project.
4.  When I pull up a view in Access (i.e. AccountIMAPIview) I can see the
Account data, as well as the custom fields that are called UserField(1 - 40).
 I can even see SOME of the data that I entered in the custom fields andthat
is my problem.

PROBLEM I AM HAVING:
The problem I am having is that only certain custom fields appear to be
visible in the Access DBS.  It looks like only a Text or Drop Down custom
field is visible in the Access DBS.  The other fields (i.e. Number, Percent,
Integer) have a ?A in the field.  It appears as if Access or SQL cannot
recognize the custom field that was created.

Does anyone know how to solve this issue, or have any other suggested
methodologies.

Regards,
zMendel

I can't answer your question directly, but I expect that any view with
"MAPI" in the name is probably intended for Outlook's internal use. I
you tried any of the other Account views? They may have those custom
field columns defined a type that's more Acess friendly.

 

[rickl43065]

What version of Access is required? I have MSOffice with BCM, however Access
2007 was NOT included. I have licenced version of MS Office Professional 2000
(don't laugh), but that does not seem to be able to open the database. (File
appear to be locked - even if Outlook /BCM is not running) I do have MS SQL
2005 (and the VSS Writer, etc) But that is a completely new application for
me (interperet, lots of start up time).

My goal is to write some custom reports from the Opportunity page. (Custom
sorts on fields, totals by product type, basic custom reports.. I thought
Access 2000 would be the best way to handle this, I may try Excel 2007, but
I really dont like writing Database macros in Excel.) Your guidance would be
greatly appreciated.

Rick