Excel Database Protection
Excel Database Protection
Excel Database Protection
Excel Database Protection
Excel Database Protection
Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection
Excel Database Protection Excel Database Protection
Excel Database Protection
Go Back  Xtreme Visual Basic Talk > > > Excel Database Protection


Reply
 
Thread Tools Display Modes
  #1  
Old 04-19-2004, 03:22 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Question Excel Database Protection


Hello all,

Is it possible to set the .xls file (excel workbook) with this functions:
1) Only VB.Net (Ado.net) can access the file;
2) File cannot be deleted without password;
3) File cannot be opened through Excel without password.

Thank you in advance,
Artour.

Last edited by ai_ess; 04-19-2004 at 09:52 AM.
Reply With Quote
  #2  
Old 04-19-2004, 09:20 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Only #3 can be done directly, that I know of... Saving a Workbook with a Password is pretty easy, and would look as follows:
Code:
oWB.SaveAs("Book1.xls", "Password")
This would also, pretty much, take care of issue #1, as well.

#2 is very interesting. Sounds more like a file-system or network dministration setting. I've not heard of such a thing, but it could exist. There are, of course, Read-Only settings and the like, but Cannot-Delete-Without-Password, I have never heard of, although it does sound like a pretty good idea!

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 04-19-2004, 09:50 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Hmm...I know how to save the Excel file with password using Excel itself, but, the file then cannot be opened through VB.net....But i think you can add the password somewhere in the connection code...

My connection string looks smth like this:
Code:
Dim stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=C:\data.xls;" & _
                              "Extended Properties=""Excel 8.0;HDR=YES"";"
Maybe someone knows where to add the [password] parameter...?
Reply With Quote
  #4  
Old 04-19-2004, 12:31 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Are you saying that ADO.Net cannot access a Workbook that has been Saved with a Password?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 04-19-2004, 04:49 PM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

Excel is per se not a database and therefore there exist some limitations when working with it via ADO / ADO.NET.

Following line will therefore not work:
Quote:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Week.xls;" & _
"Jet OLEDBatabase Password=XL-Dennis;" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
I haven't test it with the native ISAM-driver for Excel but it will propably not work.

What do You want to achieve with Your questions?
Reply With Quote
  #6  
Old 04-19-2004, 07:04 PM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Something like a password database..

On the startup i need VB to get the usernames and password and check if login password and username are correct. and aslo able to register new users.

So i need the excel database not read the information through Excel so people cant just open the file and see/modify all the account data...

Thank you.
Reply With Quote
  #7  
Old 04-20-2004, 04:07 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

I would advise You to solve the whole password-procedure in VB and not involve the Excel-workbook for it. This seems to be the only workable way.
Reply With Quote
  #8  
Old 04-20-2004, 08:08 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Do you mean not to use database? But I need somewhere to store permanent information on accounts...I thought there is a way to protect this kind of database...but oh well, i am sorry that it is not possible
Reply With Quote
  #9  
Old 04-20-2004, 08:57 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

Since we can't unlock a protected Excel-workbook via ADO/VB.NET we must solve the logon in a different way.

It means that You should keep Your workbook as it is BUT create a procedure in VB.NET that handle the logon-procedure instead.

BTW, per se Excel is a spreadsheet-software. Therefore we can't work with Excel in terms of a real database in a similar way we work with Jet Database, SQL Server, MySQL, Oracle etc.
Reply With Quote
  #10  
Old 04-20-2004, 09:40 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Yes I understand what you are saying.
Its just that the users can open the database through excel and read/modify password data, which is violates the main function of password database. And from what you have said it is impossible to prevent that from happening using Vb/Ado.Net and Excel, right?
But can anyone think of a way in which I can at least increase the security level...? If not then I guess I will have to learn other types of database connections...
Reply With Quote
  #11  
Old 04-20-2004, 10:05 AM
reboot's Avatar
rebootExcel Database Protection reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

What database is this you keep talking about? Surely you aren't referring to an excel spreadsheet as a 'database'?
Reply With Quote
  #12  
Old 04-20-2004, 10:35 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

Reboot see post #3 above and the OP made his choice in an earlier thread.
Reply With Quote
  #13  
Old 04-20-2004, 11:16 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

One way from preventing the end-user to find the file is to change file-extension, like week.xls to week.gas.

Then You change the connectionstring to:

Code:
Dim stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Week.gas;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";"
Reply With Quote
  #14  
Old 04-20-2004, 11:47 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ai_ess, the other way to go would be to save the Workbook with a Password, which would have the effect of encrypting your data. ADO would not be able to access the data and neither would anyone else be able to even open the Workbook without the correct password.

However, the coding to get the data out, would then involve Automation, specifically using the oExcel.Workbooks.Open() command, making use of the Password parameter.

If you are not already familiar with how to do this, I would have a read in the Automating Office Programs with VB.Net tutorial.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 04-21-2004, 06:02 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

I am sorry if I am annoying with my lack of knownledge in both Visual Basic and especially databases..

All i am trying to do is make a simple accounts database and I have chosen Excel because the data is easy to interpret and I am familiar with it. This project is my first database project and as I have already written 2000+ lines of code with a concept of integrating ADO.NET and MS Excel 2000, I just want to make the 'database'/'spreadsheet' more like a protected password-database. After this I look forward to learning more about databases and I would really like you, experts, an advice what simple-powerful concepts/connections/combinations I need to use for VB.NET database applications.

Mike R, at the moment I am using ADO.NET connections to connect to my Excel spreadsheet, so I am not very sure how I include the commands such as [ oExcel.Workbooks.Open() ]...? If its possible it would be great..

XL-Dennis, thanks for your suggestion, thats a good & simple idea and I will sure use it.

Reboot, I again appologize for my lack of knowledge in this field and I hope I will learn not to make 'stupid' comments like that as I progress with my learning of VisualBasic and whole computer science...Sorry again..
Reply With Quote
  #16  
Old 04-21-2004, 06:13 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

Have faith

We all have been new to all the stuff we nowdays handle (quite) comfortable.

reboot expressed a skeptical of using Excel as a database and as You have notice Yourself it lack some properties that we usually associate with "real" databases.

As for Mike's suggestion it is a total different approach that the one You have choosen to develop so at this stage keep it as it is.

I strongly believe You can get a workable solution although the security-level is not as high as You may want to have it
Reply With Quote
  #17  
Old 04-21-2004, 06:23 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Thank you Dennis.

I think it was good/useful to experiment and understand the limitations of this kind of 'database connection' with Excel.

What would you guys use for a simple 'passwords/accounts' database with VB.NET ?
Reply With Quote
  #18  
Old 04-21-2004, 06:28 AM
reboot's Avatar
rebootExcel Database Protection reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

If you want security, Excel will provide virtually none, Access will provide minimal security, and MSDE will get you very strong security.

If you want to use Excel as a simple database, that's perfectly fine, you just have to realize that it is a very limited solution. Just because you want to do certain things with it doesn't mean you can. And all the expert advice in the world won't change that.
Reply With Quote
  #19  
Old 04-21-2004, 06:39 AM
ai_ess ai_ess is offline
Freshman
 
Join Date: Mar 2004
Posts: 41
Default

Yeah, okay. sorry I wasnt aware of any of these facts thats why I asked questions...I am really enthusiastic on learning new database types...Its just that I am not sure if I can learn any database connection with my limited knownledge, so thats why I have asked what would be the best balance between functionality and simplicity of use...
Reply With Quote
  #20  
Old 04-21-2004, 10:43 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

..and when You feel You want to "upgrade" Yourself to SQL Server You may start here:

http://www.microsoft.com/sql/msde/default.asp

where You can download & try a free desktop-version of MS SQL 2000
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching an Access database from Excel curt_grymala Word, PowerPoint, Outlook, and Other Office Products 6 02-17-2004 02:53 AM
Loading the data from Excel to Oracle Database shabeer12002 Word, PowerPoint, Outlook, and Other Office Products 1 12-03-2003 04:21 AM
Searching in Excel Database alzaman Database and Reporting 8 08-03-2003 02:24 AM
Excel Database Need Help Database and Reporting 6 02-03-2003 08:13 AM
Adding password protection to database while creating it! seidenstud Database and Reporting 2 01-03-2003 12:38 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel Database Protection
Excel Database Protection
Excel Database Protection Excel Database Protection
Excel Database Protection
Excel Database Protection
Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection Excel Database Protection
Excel Database Protection
Excel Database Protection
 
Excel Database Protection
Excel Database Protection
 
-->