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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.