Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Jacobfw
Jacobfw
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of curiouswebster

ASKER

Excel 2007
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.
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.

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I can't watch video's at work. Bummer.
SOLUTION
Avatar of Anil
Anil
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Thanks.