Solved

Linking an Excel page to a database

Posted on 2011-09-07
12
202 Views
Last Modified: 2012-05-12
How do I link a sheet in the workbook to a database, so that a few columns of data are taken from the DB?

Then, when the user has chosen to work offline, the old data will still be there and referencable by other pages in the Excel file?

I assume we could make a toggle field in the spreadsheet with two value:

"Work Online"
"Work Offline"

which would govern whether the link becomes active or not.

How do I handle this?

Thanks,
newbieweb

0
Comment
Question by:newbieweb
  • 7
  • 3
  • 2
12 Comments
 
LVL 7

Accepted Solution

by:
Jacobfw earned 300 total points
ID: 36495729
Excel provides ways to talk directly to your DB (either OEM Data connection or ODBC).
What version of Excel are you using?

In Excel 2010, use the DATA -> Connections to setup the link to your database and configure a new connection to your database.  Or Data -? From Other Sources and use the connection wizard.

You can choice to download your data into the spreadsheet and refresh it using the tool bar, or create refresh button in the spreadsheet to facilitate your users.

Jacob

0
 

Author Comment

by:newbieweb
ID: 36495900
Excel 2007
0
 

Author Comment

by:newbieweb
ID: 36495911
I'd like to refresh the data only when the user has selected "Work Online". Otherwise, I'd like to use stale data from the last time they were Online.
0
 

Author Comment

by:newbieweb
ID: 36495948
Rather than using ODBC, I would probably get a direct database connection to an Oracle database on our server. Otherwise, each machine would need to have ODBC configured.

0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 300 total points
ID: 36496127
0
 

Author Comment

by:newbieweb
ID: 36496157
I can't watch video's at work. Bummer.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 200 total points
ID: 36496169
This is possible. LinK your "Work online" button to a macro that refreshed that connection.
I use Oracle Drivers. Either these drivers or ODBC.

I used: http://www.experts-exchange.com/Database/Oracle/Q_27036375.html

You are given the option to refresh the data. If the connection is not available ,like for example you send the data to another company, the data remains in the sheet as well as the connection details. (So be careful not to store usernames and passwords).

Each time you refresh Excel will prompt you for the connection details.

A>
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 300 total points
ID: 36496172
That is ok, it was a product demo.

You will still need to install some type of connector for Oracle.
Do you Micosoft ODBC for Oracle installed?

0
 

Author Comment

by:newbieweb
ID: 36496247
Interesting point...

Is there a way to keep the username and password out of the Excel VBA? I do not see how, but I am very new to VBA.

> Do you Micosoft ODBC for Oracle installed?

Not yet, I have nothing installed. I am trying to grapple with what's the best direction to follow.
0
 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 200 total points
ID: 36496353
This : http://www.experts-exchange.com/Database/Oracle/Q_27036375.html
does not use VBA. There is a dialog box whixh asks you to remember the password, just uncheck it.
0
 

Author Comment

by:newbieweb
ID: 36496558
I think the choice here is to try and encrypt the password. We would not want to require users of Excel to ever know the password, yet we'd want the addition of a file (say a text file) to give than person full access.

A simple encryption algorithm would do.

I will post a different question to learn more about how to do that in VBA.

0
 

Author Closing Comment

by:newbieweb
ID: 36496592
Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now