Not a valid Password

Nov 3, 2014 at 11:57 PM
Hi There,
This seems like just the simple tool I need to open and query Access Databases that come my way now and then.

I receive an error when I try to run a simple query to list the tables in the database.
SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

"There was an error while executing the query"
System.Data.OleDb.OleDbException: Not a valid password

I know what the password is but have no idea how to enter it using this tool.
Any Idea's

Thanks
David
Nov 4, 2014 at 2:11 AM
Quick and dirty fix
I took a look at the source and could see that there is no provision for passing a password in.
I found another program I have that can open the problem data base and I stole the connection string out of that.
It appears to me that my other program uses a much later version of Access database engine. Perhaps it can also open older versions too.
I edited the QueryRunner.cs file and recompiled the project in VS 2008. There was some automatic conversion but it completed without error.

I broke the code below into a couple of lines to make it a bit more readable.
And just like that it worked. Well my original query didn't work and reported that I don't have access to the MySysObjects table.
I just came up with a more appropriate query.

The next step is to choose a way to pass in the credentials instead of hard coding it. I'll see if I can get back to this after I have solved my immediate problem.
(the one I needed the analyser for :) )

I hope this helps someone else one day

QueryRunner.cs

<SNIP>
// Before
// OleDbConnection conn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"{0}\";", filename));

// After
string filePath = string.Format("\"{0}\";", filename);
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + "; Jet OLEDB:Database Password='MyPassword';";
OleDbConnection conn = new OleDbConnection(connectionString);

</SNIP>
Nov 4, 2014 at 5:28 AM
Ok Here is a much better way to specify your database settings outside the program.

I changed the OleDbConnection line in the source file QueryRunner.cs to look like this then recompiled

OleDbConnection conn = new OleDbConnection("File Name = .\Database.udl");

Next I created a text file in the same directory as the exe file named database.udl

The text in the file will look something like this.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Projects\FileSmartImport\TestData\Circulate\CircurlateTestData1\circulate.dtl;Persist Security Info=False;Jet OLEDB:Database Password="MyPassword"


There are heaps of resources on the net for constructing these connection strings and you will be able to connect to anything that MDAC supports.
You can just double click on the database.udl file and the built in editor will let you modify and test your settings.
The only thing to be aware of is that if you save a password it will be stored in clear text in the udl file.
Considering this is just a query analyser it shouldn't be a big deal.

I'll see if I can contract the original author and add my updates to the code and publish it here.
If not I'll publish it somewhere and link to it. (me thinks this project has been quiet for a very long time :)

David