How do I get external data from an MS access file (with MDW) to connect to Microsoft excel?

I can open access fine using the following command line but, i cannot get the syntax for it to connect in excel through any of the wizards.  Any help would be appreciated-


"C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe" "\\srv05\database.mdb" /wrkgrp "\\srv05\folder2\jobboss.mdw" /user username /pwd password123
LVL 7
PhilonatorownerAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can just copy jobboss.mdw over to the directory you need, if that's the only trouble.

If you instead need to declare the .mdw file in your Excel connection, you'll have to modify the Connection String in the Properties section of your data connection in Excel. You can do that during the creation by clicking the Properties button, and then viewing the Definition tab. The full connection string is shown there, and you can modify it as needed.

Here's the connection string that was added when I created a connection in Excel:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Development\Access Databases\Civic Assoc mbrshp stripped.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

I don't know if you need all of those, but the one you're looking for is the "Jet OLEDB:System database" property. Set that to the location of the .mdw file, and be sure to add the "User ID" and "Password" options as well (plus you'll be using the Microsoft.Jet.OLEDB.4.0 provider instead of 12).

You could also just connect directly to the SQL database from Excel and get the information that way. In other words, don't even use JBUDS, just go directly to the source.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Jobboss.mdw file is used with Jobboss (the ERP program). Are you sure you should be using that workgroup file? I do a LOT of work with Jobboss, and that .mdw file is generally used for external reports, utilities, etc.
0
 
PhilonatorownerAuthor Commented:
Yes it is jobboss.  We have a hundred custom reports in access (jbuds) and it is quering the SQL database.  We are on the latest and greatest jobBOSS and need to rewrite over the hundred custom reports.  I want to pull the information from some of those reports as a stop gap in excel.  I have been doing it with other rougue access databases we have but, the mdw file in a different directory is tripping me up in the syntax in excel connection.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PhilonatorownerAuthor Commented:
I tried doing exactly that (including JET) and I was getting tripped up somewhere.  Let me revisit it and see where I end up.
0
 
PhilonatorownerAuthor Commented:
Entered the MDW file in Jet OLEDB:System database and that was  the ticket under advanced properties.  Ironically if you select jet db 4, the connection will test successfully but, excel will crash once you click finish.  Tested in two different versions of excel, still crashed.  I was able to get it to work by selecting access v14, tested ok and pulled in tables correctly.
0
 
PhilonatorownerAuthor Commented:
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.