Excel's Workbook Connections

Hello:

One of our users got a new machine.  Obviously, this "event" has caused him to lose the ODBC connections used within Excel's Workbook Connections.

I tried to recreate the ODBC connections through Administrative Tools in Control Panel, based on the settings in the Excel "Workbook Connections" Properties.

Unfortunately, upon refreshing the workbook, I received the error message saying "Data source name not found and no default driver specified."

Excel, then, brought up the "Select Machine Source" window.

My ODBC connections did not appear in that window.  So, I clicked its "New" button and got a message saying that the user is logged in with "non-Administrative" privileges.

I confirmed with IT, over a remote session, that the user's login is indeed an Administrator on this Windows 10 machine.

IT and I are both befuddled.

Any ideas?

Thank you!  Much appreciated!

John
John EllisAsked:
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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There are several things to make clear here.
The user most probably has a 64bit OS now. Is it a 64bit Office too? From what you describe, I would tell not. You need to be careful whether you set up a 64bit or 32bit ODBC DSN - a 32 bit application needs a 32bit ODBC DSN, 64bit apps 64bit ODBC DSNs.

Next, on W10 an user with administrative privileges does not have admin privileges all the time - only if runnning a process/app explicitly as administrator (e.g. using the corresponding option in the context menu on links and executables). The difference betweeen a non-adminsitrative and an administrative user is that the latter does not require to enter a password, just confirm the switch to admin privs.
0
John EllisAuthor Commented:
Hi:

Thank you, for your response.

What would be the best approach and resolution to the problem?

John
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That should be obvious.
Is it 64bit OS?
If yes, is Office 64bit too?

You can also take a shortcut and start Excel via context menue and "Run as Administrator", then you should be able to create the data sources from within Excel with that user now having real admin access.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John EllisAuthor Commented:
Thanks, Qlemo!

Yes, both the OS and Office are 64-Bit.  Does that change your answer any, out of curiosity?

John
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That is strange. Maybe you defined the ODBC DSN using the 32bit tool ...
I'll try the way I described last - run Office as admin, and try to set up everything there.
0
John EllisAuthor Commented:
Yes, I created the connections as 32-Bit.  

I apologize, for the dumb question.  But, is it imperative that the connections be 64-Bit?  In one of the major apps that I work with, other than Excel, 32-Bit is always used.  

Hence, there's my confusion.  :)

John
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You have to use ODBC drivers with the same bitness as the application. A 32bit Office would use 32bit DSNs, a 64bit Office does not, and the same applies for every other application.
That also might mean that you have to install two drivers, but in most cases you just install the 64bit one, and that manages 32bit connections too. But nevertheless ODBC DSNs are to be set up in 32bit mode for 32bit applications.

BTW, you usually still install Office as 32bit application, as long as you do not have special needs (very large Excel workbooks for example). Most add-ins for Office don't work with 64bit Office.
0
John EllisAuthor Commented:
Hi:

I used the 64-Bit app in Control Panel, this morning.  Now, the two ODBC connections that I created do appear in the "Select Machine Source" window that appears upon refreshing the workbook.

But, I still get the error message, when I choose the two ODBC connections that I created.

Any ideas?

Thanks!

John
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Looks like the change is not stored in the links used in Excel. You might have to edit them manually there, going into "Data", "Connections".
0
John EllisAuthor Commented:
Does that include editing the "Workstation ID" parameter, even though the file is on a network share used by multiple people?

By the way, the following is the Connection string of one of the four connections shown from its Data Properties window:

DSN=VS-GP;Description=GP;UID=sa;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=BDLRND45X5YQ1-W;DATABASE=PRK

John
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I'm not able to follow exactly, but guess "Workstation ID" is just used if you want to show the requesting machine (running Excel) with a different name than its real one in server logs and connection infos. If so, it should be empty.
0
John EllisAuthor Commented:
The following is what a co-worker of mine did:

So I remoted into his PC. Started to create the ODBC and got the permissions error you go below. I clicked ok to it. The ODBC window still appeared. The only option was to create a used-based ODBC connection so I tried that. It worked J

I had to try refreshing the data on the excel file a couple of times to make sure the credentials stuck. It appears to have ‘stuck’.


This fixed the problem.

John
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Sounds strange that a system DSN should not do, but if it works that way, so be it.
0
John EllisAuthor Commented:
A co-worker of mine solved the problem.
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
Windows 10

From novice to tech pro — start learning today.