Adodb - Cannot connect to Excel 1

[MakeItSo]

Hi friends,

I am trying to connect to an Excel file using ADODB / Jet.
I have no idea why.
Here's the relevant code part:

$strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . $dbPath . ";Excel 8.0;HDR=Yes;";
//...
$conn = new COM("ADODB.Connection") or die("Cannot connect to Excel");
echo $strConnection;
$conn->Open($strConnection)or die("Cannot connect to Excel");

I see the connection string, hence the Connection object itself works. I've already added the "extension=php_com_dotnet.dll" part to php.ini.
On a previous php page, I already connect to an access database using COM, hence I know it works.
The path to the Excel file is correct, too. It is the absolute path to the Excel, i.e.:

$dbPath = realpath("../../../[mydirectory]/[myfile].xls");

resulting in this output:
Data Source=C:\inetpub\wwwroot\[mydirectory]\[myfile].xls;
I've also tried

$dbPath=str_replace("\\","/",$dbPath);

just in case the backslash cause any problems although I don't know why it should, it doesn't with the access db.

[mydirectory], [myfile] only masked here, they're normal, explicit folder/file names.

I've given IIS_IUSRS and IIS_WPG sufficient rights to the folder.
Any idea what is wrong here?

Using: PHP 5 under Windows 7, IIS7. No WAMPP/XAMPP whatever.

Thanks for any input!

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

that all seems to have got there in one post.

 

[MakeItSo]

First of all: thanks for your efforts and patience, jpadie.
Greatly appreciated!

Now, that I do not understand:

Since connecting to Access mdb worked in the login verification page, it MUST work on this page too - but it doesn't!

I don't see that this is necessarily true. you are assuming a lot of the underlying programs.

What out of the ordinary do I assume?
I have one php file that opens a connection to an Access database, queries the stored login credentials and if successful, redirects to the main page, which is in one of the subfolders.
There, using the same technology, I want to establish a connection to a database on the same hierarchy level with the same security settings, created with the same Access version.
Why shouldn't I assume that this ought to work??

I understand if PHP - which is not Windows specific - might have trouble with one driver and not with the other.
However, working with one driver just fine and then refusing to work with said driver a fraction of a second later is beyond anything I will accept from modern programming technology!
And given the wide-spread usage of PHP I simply refuse to believe that it could be THAT unreliable.

However, typing this lot gave me an idea: Actually, I AM using the very same driver.
Maybe it is caused by some objects not being properly released / disposed by the previous page.

I'll dig into that and give it a try.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

However, working with one driver just fine and then refusing to work with said driver a fraction of a second later is beyond anything I will accept from modern programming technology!

in one instance you are trying to access an MDB using a JET driver. in the second you are trying to use the same driver to access an excel file. I don't see these as necessarily equal.
Also I had understand your initial comment to relate to being able to link the table in access to excel. programme to programme access may be different.

the difficulty here is that you are trying to debug php commands that use COM. there is almost no useful debug information being provided by COM. that's the headache. and why I originally suggested using odbc with a DSN instead. Much much more reliable and lots more good information given back for debugging.

And given the wide-spread usage of PHP I simply refuse to believe that it could be THAT unreliable.

php is exceptionally stable and reliable. far more so than any MS office product that I have ever used (and I helped design at least one of them...)

i understand that you want to crack the nut and make your ideas work. My warning remains however: because of the paucity of debugging info via COM you may not know if anything goes wrong later, in your production environment. please consider another approach.

 

[MakeItSo]

I will implement another approach soon.
But not before I cracked that nut.

in one instance you are trying to access an MDB using a JET driver. in the second you are trying to use the same driver to access an excel file. I don't see these as necessarily equal.

That was initally the case.
Now I am trying to access an mdb using JET in BOTH cases.
And the second case still fails.

Also I had understand your initial comment to relate to being able to link the table in access to excel. programme to programme access may be different.

Nope. What I did was create an Access mdb and linked the Excel table in it. However, I am not yet connecting to the table in the mdb yet, I only try to connect to the mdb, which should work regardless of what tables are in it.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

OK, I have absolutely no clue, why the ADODB connection works in the verification page, but I have now decided to not give a hoot.
Obviously PHP does not like COM(ADODB) very much, so I decided to give PDO a try, as recommended by jpadie.
Voilà.
I've changed this

$conn = new COM("ADODB.Connection")
$conn->Open($strConnection)or die("Cannot connect to Excel");

to this

$conn = new PDO("odbc:DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=" .$dbPath . "; Uid=; Pwd=;") or die ("Cannot connect to Excel");

And it works with both variants: Excel and Access.

Now I'll translate all of these

$rs = New COM("ADODB.Recordset");

And following recordset operations into

$rs=$conn->query($strSQL);

It'll be a nice coding exercise!
For switching to a solution based on mysql or sqlite, I think I will best construct that from scratch rather than modify the existing pages.

Thanks for your help, jpadie!
And also thanks for the mysql solution snippets. Those will surely come in handy later!
}

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

the solution i posted above was for sqlite, not mysql. but it will work with mysql provided you change the setup routine (the sql create syntax is different) and the DSN for the connection.

the solution was 'complete' in that it provided methods for importing, adding, retrieving and updating translations. i didn't see the point in deleting a translation but that could be easily added.

glad you got it working.

 

[jpadie]

forgot to mention that of course the solution also exported to excel. And to html tables, csv, and xml. the former displayed on screen, the latter two (and excel variants) all spawned a file download.

 

[MakeItSo]

Interesting!
I can only star one of your posts!
I thought both, the PDO recommendation and the sqlite snippets deserved it.
Anyway: thanks.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Little update on this in case anyone tries to translate a somewhat complex asp page into php:

DON'T!

Rewrite the whole shebang, it's not worth the effort trying to achieve such a thing in any way comparable to what you did in ASP.
There is no such thing in php as applying a filter to a recordset or using the "find" method to jump to a certain record in a recordset - not with pdo at least.


In my opinion, PHP may be stable but it is also rather rudimentary. It is what you get for wanting a cross-platform language: an ugly, clunky, elaborate PITA.
I'll ditch this project at this stage and php probably with it, and move over to Python or Ruby.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

In my opinion, PHP may be stable but it is also rather rudimentary

wow. most developers would say the opposite I think.

DON'T!

I agree. But ... most people find porting from asp to php very straightforward. I have done a number of sites. Direct ports tend to take only a few minutes per script, with the rest of the time being spent actually improving the code.

There is no such thing in php as applying a filter to a recordset or using the "find" method to jump to a certain record in a recordset - not with pdo at least.

ummm ... yes there is. The world is your oyster. You can write whatever filter you want and go to whatever record you want.

for the 'jump' use PDO::STATEMENT->fetch($style, $orientation, $offset). (

http://fr2.php.net/manual/en/pdostatement.fetch.php)

set the offset to the record you want to go to. job done....

for the 'filter' why would you ever want to filter a recordset in an interpreted language like php or asp etc? that's what database servers are for. the best answer in any language would be to redo the query. much faster, much less resource intensive and cross platform.

to do so in any interpreted language the interpreter will HAVE to buffer the whole recordset (horribly inefficient), then apply a (probably) bubble search across the result set. This is the work of moments to write in php but you will be lucky to find examples since there are no real use cases where it is a good idea to do so.

function filter($pdoStatement, $column, $value){
 $result = array();
 while( $row = $pdoStatement->fetch(PDO::FETCH_ASSOC) ):
   if(isset($row[$column]) && $row[$column] == $value):
     $result[] = $row;
   endif;
 endwhile;
 return $result;
}