+ Start a Discussion
Mike Schinkel.ax196Mike Schinkel.ax196 

Microsoft Access SForce Connector?

I am using Excel SForce Connector, which is a great tool when compared to what you can't do in SalesForce without it, but what I really need is an SForce Connector for Microsoft Access that would let me bring data down into Access tables directly, and to update SalesForce from Access. 

 

Why do I need this?  I'm doing a lot of prospecting work to find contact names and addresses for a specific group of opportunities (we sell advertising in "guides" and companies with products related to the topic of a guide are our prospects) and then postal mailing them an advertising media kit.

 

To do this I query SalesForce for opportunities, accounts, and contacts. I do my research in Excel and then add or update the information I find about accounts and contacts back into SalesForce.  Then I copy all that information into Access and create a join so I can print letters and labels using my Dymo label printer.  (As far as I know I can't do this latter in SalesForce because:


    • SalesForce won't let me create a report with two joins (grrr), and

    • SalesForce doesn't support my Dymo label printer

    So a Microsoft Access SForce Connector would be a godsend, but it would have to work with Professional Edition.  SalesForce, you've already got 80% of the work done in the Excel Connector; any chance of creating a Microsoft Access SForce Connector?


     
    qmanqman
    Why not use Data loader to get down some csv files, then upload these into either Access or the free SQL Server 2005 Express ? Or just save the Excel data as a csv and bring it into them. Either way, you could do the joins you want.

    Bill
    Mike Schinkel.ax196Mike Schinkel.ax196
    Doesn't Data Loader require Enterprise Edition?
    RickNTARickNTA
    This may not be in the right direction for you, but we created an interface between Access and SF based on code borrowed from a VB6 app named SFC_DataUpdates.vbp, which I downloaded from somewhere on the SF site a few months ago. It uses two class modules, one from Ron Hess called CSession that is essentially a wrapper for the Office Edition COM Object, one from someone else (don't recall) called SFC that uses CSession. Our Access app is interactive (not batch), so we're reading from and writing back to a single Account/Opportunity record at a time, but the same basic approach should work for more batch-oriented cases, since the original VB app was apparently intended for batch use. We used parts of the two classes with only minor mods because we needed a solution in a hurry, and buried it in a lot of our own custom code, so it's pretty messy code overall, but it's been working fine for several months.

    OTOH, it may be more straightforward for you to use the Excel Connector to download what you need, then import the data into Access from Excel. Or use one of the 3rd-party ODBC interfaces to SF? etc.

    HTH!!
    Mike Schinkel.ax196Mike Schinkel.ax196

    RickNTA: Thanks for the reply.


    It does sounds like something that might work well though I don't have the skill to write the code. But I do have VB6 and the skill to modify existing code; are you offering to share what you've done?

    foghornfoghorn
    You can also try out

    http://www.OpenAccessSoftware.com/products/openrda/Salesforce_overview.asp

    which exposes salesforce data as an ODBC datasource, which you can use however you'd like.
    Mike Schinkel.ax196Mike Schinkel.ax196
    Now that sounds like a winner! Thanks!
    Ron HessRon Hess
    Yes, i believe it does.
    even the ODBC solution requires Enterprise, but i could be wrong, never set that up.

    with so many good options ( like DBAmp, odbc, ect.) , i gave up on moving the excel code over to access.

    I see you mention Professional Edition.

    I would try to export the tables of interest using the report module into CVS files, then load those into MS Sequel Express, where you can perform your joins.

    Message Edited by Ron Hess on 12-01-2005 07:57 AM

    RickNTARickNTA
    I'd be happy to share the code, but don't have time right now to extract it from our app - way behind on project work... I'm guessing you'll want to use a different approach anyhow, as others have suggested - but if you decide you want to take our approach, let me know and I'll try to get it for you.

    Rick N.
    Mike Schinkel.ax196Mike Schinkel.ax196

    >> Yes, i believe it does even the ODBC solution requires Enterprise, but i could be wrong, never set that up.

    I do hope you are wrong. :)

    >> with so many good options ( like DBAmp, odbc, ect.) , i gave up on moving the excel code over to access.

    Bummer. I am also looking at DBAmp BTW.  I might end up using them all, if possible, for different use cases.  BTW, do you work for SalesForce, or are you an external? 

    And how did you get it to talk to PE if the API doesn't support PE?  I'd be happy to write some VB/VBScript code if I could figure out how to access the API using PE.

    >> I would try to export the tables of interest using the report module into CVS files, then load those into MS Sequel Express, where you can perform your joins.

    I'm essentially looking for an automation solution for synchronizing data. Unless I misunderstand, that's just another way to do what I've been doing manually, and it's also only one way.

     

    Mike Schinkel.ax196Mike Schinkel.ax196
    >> I'd be happy to share the code, but don't have time right now to extract it from our app - way behind on project work...

    Thanks for the consideration. Well I certainly understand being way behind, and I can relate!

    >> I'm guessing you'll want to use a different approach anyhow, as others have suggested - but if you decide you want to take our approach, let me know and I'll try to get it for you.

    Actually, DBAmp is looking like it might be the best solution for the moment, but there are still things I'd like to be able to do via VB code. If you ever do get a chance to extract it, I would definitely appreciate it. Thanks again!
    Ron HessRon Hess
    >> I could figure out how to access the API using PE.
    that's top secret, the PE connector was built as a special case to allow PE orgs to import custom objects, as the wizards don't do that yet.

    >> do you work for SalesForce, or are you an external?
    i'm a new hire at salesforce.com, was a customer (admin/developer) prior.

    Automating this process is the business justification for upgrading your org to Enterprise Edition!
    Mike Schinkel.ax196Mike Schinkel.ax196
    >> that's top secret, the PE connector was built as a special case to allow PE orgs to import custom objects, as the wizards don't do that yet.

    Frankly, and I'm venting, that *really* frustrates me. Limiting the API to Enterprise only is SOOOO short-sighted on SalesForce's part. That limits the market for 3rd party vendors and hence the extended functionality that most SalesForce customers can access.

    >> Automating this process is the business justification for upgrading your org to Enterprise Edition!

    Spoken like someone who doesn't know or understand my business.

    People preaching "value-based pricing" are often being both self-centered and short-sighted.

    SaleForce's pricing model requires everyone using it to be on the same version which does not work for me, nor for many other small businesses. Getting logins for occassional users just costs far too much. Having to pay $1500 each for the ones we have is just totally out of the question.

    And $1500/person is also too much for most small businesses given our revenues and overhead. Mine included. As is, the $800/person really hurts, especially while we are still in start-up mode.

    SalesForce should be focused on growing it's customer base, not on milking existing customers.

    Message Edited by Mike Schinkel on 12-01-2005 11:55 AM

    Message Edited by Mike Schinkel on 12-01-2005 11:55 AM

    Ron HessRon Hess
    >> That limits the market for 3rd party vendors

    i clearly don't understand your business, sorry if i implied that i do.

    Message Edited by Ron Hess on 12-02-2005 07:41 PM

    RickNTARickNTA
    >> Actually, DBAmp is looking like it might be the best solution for the moment, but there are still things I'd like to be able to do via VB code. If you ever do get a chance to extract it, I would definitely appreciate it. Thanks again!

    OK, I'll make you a deal - if you'll post back here what you decide to use (DBAmp or whatever) and how it works out for you, so I can learn something from it, I'll make the time to extract the code and get it to you. Just don't jump on me like you did that new salesforce.com hire!!

    Rick N.
    mmanganommangano

    Ron Hess wrote:
    >> I could figure out how to access the API using PE.
    that's top secret, the PE connector was built as a special case to allow PE orgs to import custom objects, as the wizards don't do that yet.

    >> do you work for SalesForce, or are you an external?
    i'm a new hire at salesforce.com, was a customer (admin/developer) prior.


    Congratulations Ron on the new gig - are you going to continue to work on sforce-related projects?

    -- Mark
    http://www.salesforcewatch.com

    adamgadamg
    To add my $0.02 here:

    As a company, we've done a lot to make CRM (and Web services!) more available and accessable then most other vendors out there - but we do still need to charge for our products, and charge a premium for extra features. Hence the ability to write your own code that uses the API is EE for only.

    We allow some prepackaged solutions to access the API on your behalf (Offline, Outlook Edition, and because its just so damn useful, the Excel Connector), but not everything, and not products that are integration related, as doing so would hurt our ability to charge for the feature.

    I'd recommend talking to your CSM or AE to see what EE/API access would really cost. Also trust that we are always working to improve our import and data cleaning tools to minimize the need to write code for common tasks.

    Adam
    Mike Schinkel.ax196Mike Schinkel.ax196
    >> 3rd party vendors can apply to salesforce.com to build products that can and do work with PE orgs.
     
    So why then can't end users?  I know, it is a rhetorical question.
     
    >> I just don't know if the ODBC product has applied for this level of partnership.
     
    If they have to apply (and I assume pay extra), then SalesForce is limiting the number of 3rd party products, right?  IOW, why can't someone come out with an opensource PE add-on?  If they have to pay, or even apply, that is friction that would be better left undone.
     
    >> and yes, i clearly don't understand your business, sorry if i implied that i do.
     
    Thanks, and sorry I got bent out of shape. It is a hotbutton of mine, far beyond the scope of just SalesForce.com
     
    BTW, I have run another copy since 1994 called Xtras.Net (we were #123 on the Inc 500 in 1999) that sells 3rd party components and tools for .NET developers.  So building 3rd party ecospheres is something about which I am very knowledgable. Actually, it was partially my interest in 3rd party ecospheres that led me to start the company.  I greatly value what a strong 3rd party can bring to an application, and am frustrated whenever I see the vendor (in this case SalesForce) doing things that would limit a 3rd party ecosphere.
     
    Mike Schinkel.ax196Mike Schinkel.ax196
    >> OK, I'll make you a deal - if you'll post back here what you decide to use (DBAmp or whatever) and how it works out for you, so I can learn something from it, I'll make the time to extract the code and get it to you.

    Absolutely!

    >> Just don't jump on me like you did that new salesforce.com hire!!

    Ouch, I think I deserved that. Uh, yeah, I do get quickly frustrated these days, but mainly on things about which I'm already really frustrated like the fact that SalesForce limits the API to EE users.

    Clearly it's there right to do whatever they want, but when I believe that their decision actually limit their growth and profitability because of what I see as short-sighted greed, I feel like I have a little more right to be vocal about it. FWIW anyway!
    RickNTARickNTA
    >>Ouch, I think I deserved that.

    I was just kidding - I have the impression Ron can take care of himself... and it's good to shake up a thread a bit once in a while.
    Mike Schinkel.ax196Mike Schinkel.ax196
    Adam:
     
    Well, your forum just ate my last reply. Just as well, I was venting.
     
    >> As a company, we've done a lot to make CRM (and Web services!) more available and accessable then most other vendors out there - but we do still need to charge for our products, and charge a premium for extra features. Hence the ability to write your own code that uses the API is EE for only.
     
    Respectfully, you are being short sighted.  Microsoft Windows is the leader in part because access to the API has always been free.  Far more software developed for internal use has gone on to become a product than software originally developed for sale.  What's more, when companies develop software for a platform, be it Windows or SalesForce.com, the platform vendor gets lock-in.  Both of those would far offset the extra money you can currently milk out of companies for the API.  Charge more for business benefits, i.e. for higher end modules, not for programmer benefits like the API.
     
    That said, why not offer the API to Pro and Team users, but at a volume level below that which most EE customers use?  You've got the data to be able to determine what that level could be, why not then see what EE customers are using and set a lower threshold for Pro & Team members?  How would that limit your ability to charge for it, which btw, I still think is incredibly short-sighted.
     
    That's good to know. >> I'd recommend talking to your CSM or AE to see what EE/API access would really cost.
     
    I have. Too much.  One of the reasons is you require an all-or-nothing approach to user fees, yet I have users that are not sales people and only work for me part time (my authors and editorial staff) and they need limited access to info.  There is absolutely no way I can afford $1500/year for every one of them.
     
    >> Also trust that we are always working to improve our import and data cleaning tools to minimize the need to write code for common tasks.
    That's good to know as a seperate issue, but open access to the API for everyone is still on my list of hot buttons. 
     

    Message Edited by Mike Schinkel on 12-02-2005 11:15 AM

    Ron HessRon Hess
    Congratulations Ron on the new gig - are you going to continue to work on sforce-related projects?

    -- Mark
    http://www.salesforcewatch.com

    Thanks, I am working with the API daily, mostly AJAX these days. Look for some cool new tools to come out in the AppExchange, i'll post more details on this on the sforce blog soon.
    Ron HessRon Hess
    i was mistaken about the 3rd party vendors and API, Adam cleared that up for me.
    Mike Schinkel.ax196Mike Schinkel.ax196

    >> i was mistaken about the 3rd party vendors and API, Adam cleared that up for me.

    Meaning they can't sell to PE or Team users?

    BTW, I know I came across as negative on this thread, so let me be possible here at the end.  I *really* appreciate the SForce Excel Connector when compared to not having it.  Though I'd really like to have more, it makes SalesForce an order of magnitude better than it was before I found out about it.  Great job!

    DP007DP007

    Hello RickNTA,

    I new to SF and the app you mentioned SFC_DataUpdates.vbp sound interesting.  Do you know where I can get a copy of it at?

    Thanks

    Derek

    RickNTARickNTA
    Hi Derek -

    I've tried and haven't been able to find where I got it (although I was pretty sure it was somewhere on the SF site).  I do have the original file, SFC_VB6.zip, so I'm dropping it here on the assumption that will upload it (never tried this before on this forum):

    file:///U:/FromNatash0925/NatashaG/Salesforce.com/API/VB6/SFC_VB6.zip

    Good luck - let me know if you can get the file here.

    Rick N.
    Force2b_MikeForce2b_Mike
    Funny enough :smileysurprised: I posted that file in May 2005. I've made some changes to the logic since then, but not that many. Keep in mind that this version requires the SFC Office Toolkit. Ron's current Excel Connector using the SFC OutLook Toolkit as its base. I just didn't have the time or need to rewrite my VB code to use Ron's updated code.

    More recently I've been trying to update the code to VB.NET, however it's been more of a hacking job. So, if anybody has converted Ron's code to VB.NET please let me know.

    http://www.kepner-tregoe.com/salesforce/sfc_vb6.zip

    Mike Smith
    RickNTARickNTA
    Ah, the mystery author appears! I'm glad you turned up; I lost track of where I'd found your code, and it's been real helpful to us, so I expect there are others who could use it, too.  I can't even find the secret location on the SF website where files are posted, even though I apparently found it a year ago somehow... any clues on that?  I'm pretty sure the link I posted below isn't useful to anyone (our internal server).

    Rick N.

    PS - I'm also a fan of Kepner-Tregoe - have their 1981 book still on my shelf...
    Force2b_MikeForce2b_Mike
    Rick,

    Yes, I have surfaced. I had really just forgot that I posted those files. The link in my previous email will take you to the zip file.

    Also, I'm glad you like Kepner-Tregoe. The New Rational Manager is such a classic. Over my 12 years here, I've really become a true believer in the value of KT Process.

    Mike Smith
    DP007DP007

    Thanks for the information.  Your link did not work, however another subscriber posted the link for me.

    Thanks

    Derek

    DP007DP007

    Hey Mike,

    Thanks a bunch I will look at this code and hopefully it will help.

    Thanks Again.

    Derek

    vanessenvanessen

    But this does not work as the Excel Sforce connector.It is mean for vb users,if i m not mistaken....isn't it?

    Dea73Dea73
    Is there a way to stop Non Admin users from doing this?