Linking BCM Database into Excel

[Guest]

I've got a series of highly-customized Excel templates (invoices, receipts,
quotes, etc.) that I woud like to link up with my brand-new BCM database.
Basically where I ultimately want to go is create controls/selection boxes in
Excel that will show me a list of all the customers in BCM and when I choose
one, it populates the Excel template with all the customer data.

How can I do this? My developing skills are very weak, though I do have
some experience creating the various Excel controls that I think I'll need.
I just don't know how to set up Excel to connect to the BCM data to pull in
what I need. I found one message via search that said it can be done via
ODBC, but I have no idea how. I tried all the tools under "Data > Import
External Data" but can't find any way to connect/query/import from the BCM
database.

Can anyone tell me what I need to do, or point me toward a good tutorial
that can walk me through it?

Thanks,
Nick

 

[Guest]

Okay, so I figured out one way to do it, but I'm wondering if there is a
better way.

I changed the BCM database to share, and was able to set up a new data
source via ODBC in administrative tools. Queries into Excel work great.

The issue is that I really don't need to share this database. I am a
one-person business so no need to share this database. So now SQL server is
running, asking for internet permission from my firewall, and when granted,
frequently listens to port 56183 (ZoneAlarm is showing flickering traffic in
and out whenever Outlook is open). I'm concerned that sharing this database
may introduce security risks and/or a drain on resources.

So, is there another way to use ODBC, Query, etc. to connect to the BCM
database WITHOUT sharing it?

Thanks,
Nick

 

[Luther]

If you are the same user, that created the BCM database and is running
Excel, you shouldn't need to share the database.

However, sql server will always have to running for you to use BCM or
access the BCM database from Excel. If you are only using a single
machine, you can shutdown port 56183 in your firewall.

 

[bbalasubramanyan]

Hi Nick,
You are on the right track by accessing the data from Excel via an
ODBC connection. We are in the process of documenting this for the
upcoming release of the BCM SDK. Couple of points to note -
1. You don't need to share out the BCM database in order to access the
data from Excel as long as you are using Excel on the same m/c on which
the BCM database is running.
2. When accessing BCM data from Excel, there are certain public views
(more below) that are the only ones you should access. Everything else
is BCM internal and should note be accessed from Excel or any other
app.
3. We are actively working on releasing the BCM SDK and would love to
learn more about the kind(s) of applications you want to build with
BCM. Can you share more details on your plans please?

Below is the relavant information about accessing BCM data from Excel.
Note that with Excel you only get a readonly view into BCM data. With
Access, you can both read and write data into BCM. Please keep in mind
that some of the information below is subject to change as we continue
to update our product.

1. Create a new file in Excel.
2. Select the cell where you want to insert the BCM data.
3. Go to the Data tab
4. In the "Get External Data" chunk, select "From SQL Server" from the
"From Other Sources" dropdown button.
5. Type in the server name - <Machine Name\MSSmlBiz.
6. In the drop down, select the appropriate BCM database.
7. Choose PublicAccountsView/PublicContactsView from the list of tables
depending on whether you want to access accounts or contacts..
8. Click Next.
9. Type in a description and/or friendly name (this is optional).
10. Click OK on the next dialog (titled "Import Data")
11. You can get a read only view of BCM data from here.

Note - the only supported views to which you can connect are
PublicAccountsView, PublicContactsView, PublicDeletedAccountsView and
PublicDeletedContactsView. The other tables and views are for internal
BCM use only and you should not access them or you may corrupt BCM.

 

[Guest]

Thanks for the help, both of you. I've actually had a really rough going of
this over the past two days. Per the suggestions, I tried to turn off
sharing of BCM, which lead to unspecified errors that completely ruined BCM.
I couldn't get BCM to work at all when I tried to unshare it, couldn't
"re-share" it, couldn't create a new database, nothing. I tried several
system restores to bring it back, but those caused problems elsewhere with
the software I had installed between BCM and this unshare failure. So I
ended just keeping my current system restore point and uninstalling BCM
entirely. I will try reinstalling and retrying all this again later. I
suspect that all this was caused by just what you warned against: using
MSQuery/Excel to access BCM tables other than the public ones.

However, during one of my attempted system restores, back to a time before I
tried sharing the BCM database, I did the try suggestion. Initially the
problem was that Excel couldn't see the MICROSOFTSMLBIZ database via MSSQL
(this was the problem that prompted me to try sharing in the first place).
By default MICROSOFTSMLBIZ wasn't running, even when I had Outlook opened.
But by using the SQL Service Manager on my tasktray to run the service, it
showed up and I could run queries.

So I'll try again later using the public tables only. By the way, I'm using
BCM for Outlook 2003, not 2007. I hope the public table suggestion applies
to this version.

As for my goals, I basically covered that up front. I have a whole series
of Excel files that I use to generate quotes, invoices, and receipts. I have
an "inventory" database in Excel. I use quotes because I don't really have
my own inventory, I market/sell those held by my business partners. Anyway,
I've also got all my customer data in Excel to automatically pull into my
quotes, invoices, etc. As you might imagine, it's a real pain maintaining a
customer database in Excel, and I'd rather use BCM for that (not to mention
its other account/opporunity features). So in the end what I want are my
Excel templates to have drop-down boxes or the like with a list of all my
customers in BCM and when I click on a customer, it automatically populates
the address, phone, delivery preferences, etc. out of BCM instead of the
Excel file I have now.

I imagine once I get up and running with a more sophisticated
accounting/inventory management system (right now it's a combination of those
Excel files and MS Money!) I'll want to create similar functionality from
that application. For example, have a drop-down box with a list of all my
items in inventory that I can similarly use to complete my quotes, invoices,
etc.

Hope that helps.
-Nick