• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Linking an Excel page to a database

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
newbieweb
Asked:
newbieweb
  • 7
  • 3
  • 2
5 Solutions
 
JacobfwCommented:
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
 
newbiewebAuthor Commented:
Excel 2007
0
 
newbiewebAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
newbiewebAuthor Commented:
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
 
JacobfwCommented:
0
 
newbiewebAuthor Commented:
I can't watch video's at work. Bummer.
0
 
akajohnCommented:
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
 
JacobfwCommented:
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
 
newbiewebAuthor Commented:
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
 
akajohnCommented:
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
 
newbiewebAuthor Commented:
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
 
newbiewebAuthor Commented:
Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now