@Greg Stiles I likely learned this from working with
@Clark Walliser . He did a class at Meeting of the Minds a few years back and I helped with the preliminaries.
@Kenny Daniel Here's a quick (and dirty) 55 second video that shows how one can convert a Crystal Reports query into a Power Query. This demonstration assumes that a prior Data Source setting (an recent Excel feature different than a "Silent DSN") has been established with the credentials and company code as was shown in the prior video. This is just the query part. Parameters are a bit more involved, but once converted these files are very reliable, resilient and, of course, silent.
------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------
Original Message:
Sent: 07-01-2022 15:51
From: Kenny Daniel
Subject: Silent ODBC Connection with new Excel
@Dan Burleson thank you. This is great. Yes, please post another video if it's not too much trouble.
---------------------------------
Kenny Daniel
TechnoClarity, Inc.
---------------------------------
Original Message:
Sent: 07-01-2022
From: Dan Burleson
Subject: RE: Silent ODBC Connection with new Excel
"From ODBC" should probably be renamed to "Power Query from ODBC", is redundant with a "Silient DSN" and a more powerful alternative to MS Query. Data Source settings are by definition capable of being silent without having to create alternate DSN's. If you're having problems I would first clear any existing Data Source settings that may be incomplete or in error as I show in the beginning of this 45 second video that loads a Sage 100 table.
One of the more powerful capabilities of using the companion to "From ODBC" called "Blank Query" is the ease with which one can convert a Crystal Report query into a Power Query. You're missing out if you aren't doing this because almost every client I have prefers doing queries into an Excel format that allows for filtering and traversing in a known environment. I'll post another video on doing this if there's any interest.
Summary: To convert a Crystal Reports query into a Power Query "M" language statement for use with Excel's Blank Query command:
- Use Notepad to remove the double-quotes from the Crystal Reports query using the Replace command
- Prepend the query with this string:
= Odbc.Query("DSN=SOTAMAS90", "
- Append to the query this string (a double-quote and a right parenthesis):
")
- Select the Blank Query command and paste the above string into the Power Query Formula Bar
- Select Close & Load
------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------
Original Message:
Sent: 07-01-2022 11:03
From: Greg Stiles
Subject: Silent ODBC Connection with new Excel
I've never been able to get From ODBC to work. I have only ever been able to use From Microsoft Query.
------------------------------
Greg Stiles
S & W Microsystems
Original Message:
Sent: 06-30-2022 10:57
From: Kenny Daniel
Subject: Silent ODBC Connection with new Excel
Kevin, thank you, your suggestion of using "From MS Query" worked and makes all functionality of the Query just like in the old versions of Excel.
"From ODBC" refuses to work no matter what i do. Is anyone using "From ODBC" with a silent query? I now tried it on a third install and it's still doing the same so it must be that I'm doing something wrong, I'd love to know if From ODBC is supposed to work or are we just to use From MS Query.
------------------------------
Kenny Daniel
TechnoClarity, Inc.
Original Message:
Sent: 06-29-2022 13:46
From: Doug Higgs
Subject: Silent ODBC Connection with new Excel
Shot in the dark... Try manually creating a System DSN instead of using the user DSN. Also, maybe there is an issue with the driver compatibility. Try installing the Sage 100 64 bit driver.
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles