function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
EnigmaPaulEnigmaPaul 

Integration with MS Access

Hi All,

 

I'm looking for a relatively simple solution to pull data from the SF database into Access to build some custom reports.  I've looked at OpenAccess ODBC and it works nicely but very expensive ($2000).  Also looked at Dataloader but it requires Enterprise edition and we have Professional.    DBAmp is also very expensive at $1250 per year.

 

Is there any type of lower cost solution to get access to the SF database?

 

Thanks,

 

Paul 

RickNTARickNTA
Dataloader requires API access, which isn't included with Professional (so it should probably be called Amateur).  We discovered a little secret a few months ago: you can get API access for an additional charge.  I believe one catch is that you have to add API access for every licensed user, but am not sure.  This may be your lowest cost approach.  BTW, when I looked into this many months ago I think I concluded that all of the available solutions required API access - but again, I may be mistaken.  Good luck!
gotherthanthougotherthanthou

I'm using the Office Toolkit v4.0.

 

I've got an Access VBA routine that, given login credentials, will re-create the schema of any force.com org in Access tables and then suck down the data into those tables.

 

I wrote this on company time so I can't just give it you, but it really wasn't that hard.  You could rec-create for yourself with probably a couple of hours time if that.  Email if you need pointers or get stuck.

RickNTARickNTA

That sounds very cool, but Office Toolkit uses the API, so he's still stuck.  We also use Off. Toolkit and Access VBA and it works well - with our client's Unlimted Edition.

 

EnigmaPaul, there are a couple of other options that may help.  First, I had forgotten that the Excel Connector has a Professional version that doesn't need the API (separate from the regular version and seems to be a well-kept secret, but you can find it and it works well).  Also, I just discovered the Informatica Data Loader, which is free and they say works with Prof. (https://sites.secure.force.com/appexchange/listingDetail?listingId=a0N300000016cUTEAY).  I haven't used it yet but it has good reviews and may do what you need.

 

Good luck... post back what works out for you so we all can learn...

EnigmaPaulEnigmaPaul

Hey RickNTA.... thanks for the tip.  Informatica looks great.  I know it says its free, but that's pretty hard to believe when you look at the product.  I can only assume is free for a very limited time.  what do you think?

 

Paul 
RickNTARickNTA
As near as I can tell, it's really free, free, free... maybe they'll try to start charging in the future, but I think this is just smart marketing on their part ("the first taste is free - wanna upgrade to better stuff?").  Sounds legit to me but I can't be sure yet.
EnigmaPaulEnigmaPaul

i just talked to them and the Data Loader part of it is FREE and they have no plans to charge for it in the future.   However Data Loader is limited to making connections to either CSV file, Oracle, MySQL, or SQL server 2000/2005.   My MS ACCESS and no ODBC which is a bit of a drag.

 

Is there a free MySQL that I could use to bridge the data from Access-Salesforce? 
RickNTARickNTA
I've never worked with MySQL but am quite sure there are free versions (it's open source).  Two other options: 1. Access (since 2003, at least) will link to CSV files (in addition to importing them), so you could probably put together a good dynamic solution that way (although importing may be the better way, depending on exactly what you want to do with the data in Access).  2.  SQL Server 2005 Express is free, behaves just like full SQL Server in most ways, and makes a great back-end for Access - although there's definitely a learning curve for it.  HTH...
PaulMacPaulMac

How about connecting it to SQLExpress, which is a free download and then creating an Access Data Project that uses  the SQL Express database as it's datasource?
lz1lz1

If you want to import the files instead of link, you can write code, or write a macro that will:

 

Back up the table (if desired)

Truncate the existing table (but leave the structure for the next import)

Import the new file (use TransferText)

 

You will need to import the file the first time and save the Import Specification, or create a schema.ini file in the same folder as the csv file

 

When you want to refresh the file, execute the macro, or have it execute upon launch of the database. 

 

If you want control over the individual steps, you can include conditions in the macro, and use "MsgBox("Do you want to copy the XYZ table?",1)=1"  This will prompt you to click "Ok" if you want to perform the action. 

 

I'm a bit light on VB skills, so this is a fast workaround for me.  I use CopyObject, RunSQL ((Delete * from [your table name here] and Use Transaction = Yes), and TransferText to copy, truncate and import/export my files.

 

This works well with Informatica Data Loader (free) since you can schedule the exports.  The only thing I could not do with Informatica Data Loader that I can do with Apex Data Loader is pull data from related tables.