php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #68578 MS-Access's Linked tables are Not supported on PHP
Submitted: 2014-12-09 16:24 UTC Modified: 2015-01-25 04:22 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: rajendradewani at gmail dot com Assigned: ab (profile)
Status: No Feedback Package: ODBC related
PHP Version: 5.6.3 OS: Windows 7
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2014-12-09 16:24 UTC] rajendradewani at gmail dot com
Description:
------------
HI,
I have a ms-access file which has linked table. The linked table is connected to a ODBC source (e.g MS-SQL or 3rd Party ODBC Driver).
WebPage(php) Apache, connecting to a ODBC DSN of MS-Access, MS-Access is having linked tables. Linked tables as from another ODBC source.
On the PHP Page, When I am trying to connect the ms-access odbc dsn , I am able to connect.
But when I query any of the linked tables, PHP is not able to query and is failing.

Does php has capabilities to read/write to linked tables in ms-access file.
Or it is issue with MS-Access
Or is it I am missing something.

Microsoft says, its an issue of PHP
https://social.msdn.microsoft.com/Forums/office/en-US/04d10d5d-698b-404b-8eef-7b16d83ac99b/does-msaccesss-linked-tables-are-supports-on-php?forum=accessdev



Regards
Rajendra Dewani


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-12-09 17:25 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2014-12-09 17:25 UTC] ab@php.net
Could you please provide a short worky reproduce case including a PHP snippet and an Access database file?

Thanks.
 [2014-12-09 18:13 UTC] mattficken@php.net
-Package: Website problem +Package: ODBC related
 [2014-12-09 18:13 UTC] mattficken@php.net
Sounds like this isn't a problem with the php.net web site.


PHP doesn't have its own driver for the MS-Access database. You are either using the odbc or pdo_odbc extension to use an MS-Access driver provided by Microsoft that you've already installed on your Windows box. Neither the odbc or pdo_odbc extension know about `linked tables` or other features specific to a database provider (MS-Access, etc...), they simply provide a consistent API to access different database providers and pass requests along. Its unlikely the problem is there, but with some info, we could tell.

As with any issue, you need to take some time to provide us a repro case: a sample of the PHP code and/or the MS Access file and/or information about which MS-Access driver you are using before we can take time to help.

To tell which MS-Access driver you are using, copy the DSN information from the 'Set up data sources (ODBC)' applet in the Windows Control Panel.
 [2014-12-10 13:16 UTC] rajendradewani at gmail dot com
-Status: Feedback +Status: Open
 [2014-12-10 13:16 UTC] rajendradewani at gmail dot com
Create a ms-access file.
Then create a linked table ( you can create linked table of any source, I have used QODBC driver, but it does not matter).
then create a System DSN which points to ms-access file.

then i am using below code, i did debug and found its able to connect but when querying the linked table it fails, when I query a physical/local table "localtable" which is on the ms-access file, the code works.


$oConnect = odbc_connect("myAccessDSN", "", "");

#Set the SQL Statement
$sSQL = "SELECT TOP 50 ListID, FullName, CompanyName FROM Customer";
#$sSQL = "SELECT TOP 50 ListID, FullName, CompanyName FROM localtable";

#Perform the query
$oResult = odbc_exec($oConnect, $sSQL);
 [2014-12-12 08:05 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2014-12-12 08:05 UTC] ab@php.net
Hi,

we need a worky code and db file, not just a description.

Thanks.
 [2014-12-23 19:07 UTC] ab@php.net
-Status: Feedback +Status: No Feedback
 [2014-12-23 19:07 UTC] ab@php.net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.


 [2014-12-24 08:54 UTC] rajendradewani at gmail dot com
-Status: No Feedback +Status: Closed
 [2014-12-24 08:54 UTC] rajendradewani at gmail dot com
Here is the requested information.

Please note, you will have to create ms-access dns manually.
https://dl.dropboxusercontent.com/u/5177330/temp/todelete/PHP%20Issue.zip

Regards
Raj
 [2014-12-24 08:54 UTC] rajendradewani at gmail dot com
-Status: Closed +Status: Assigned
 [2014-12-24 08:54 UTC] rajendradewani at gmail dot com
Here is the requested information.

Please note, you will have to create ms-access dns manually.
https://dl.dropboxusercontent.com/u/5177330/temp/todelete/PHP%20Issue.zip

Regards
Raj
 [2014-12-30 10:09 UTC] rajendradewani at gmail dot com
-Status: Assigned +Status: Open
 [2014-12-30 10:09 UTC] rajendradewani at gmail dot com
Any updates on the reported issue.
Please do let me know if any additional details is required.
 [2015-01-12 15:42 UTC] rajendradewani at gmail dot com
Any updates on the reported issue.
Please do let me know if any additional details is required.
 [2015-01-13 14:59 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2015-01-13 14:59 UTC] ab@php.net
Hi,

thanks for the update. And the holidays are passe :) I've tried your snippet, took one from before:

oConnect = odbc_connect("QB Access", "", "");
$sSQL = "SELECT TOP 50 ListID, FullName, CompanyName FROM Customer";
$oResult = odbc_exec($oConnect, $sSQL);

Unfortunately the issue is still blended by something else, namely here's the error message i see:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'QuickBooks Data QRemote' failed., SQL state S1000 in SQLExecDirect in C:\php-sdk\php56\vc11\x64\php-src\68578\other.php on line 10

I see from your screenshot that you have several other data sources which probably communicate with each other. But I see anyway that the .accdb file you've supplied is way too complicated. As with the current test data the actual issue isn't reproduceable, I would like to ask you to simplify the DB file. Please note - we don't need your whole db file(s), it would be much better you to create a completely synthetic case. Like  - one normal table, one linked table, some lines of the data. Btw. maybe that warning above already reveals you something why that error could be happening?

Thanks.
 [2015-01-13 15:02 UTC] ab@php.net
Ah, man ... i just see the linkedtable2.png and that warning is the exact issue. Ok. But you see that i don't have that data source at all, and i have the same warning. I guess the issue is in the .accdb file when it tries to connect to another data source.

Thanks.
 [2015-01-13 15:16 UTC] rajendradewani at gmail dot com
-Status: Feedback +Status: Open
 [2015-01-13 15:16 UTC] rajendradewani at gmail dot com
Hi,

Thanks for the update.
As For the data source ( linked table to work ) you will have to install QuickBooks ( Preferred QuickBooks Enterprise - or my try trial edition of enterprise USA) and QODBC ( www.QODBC.com).  Just install QuickBooks + QODBC and your ms-access file should get connect and should get data from QuickBooks.


I have verified the ms-access file, if I open the linked table from ms-access I am able to connect to the source and it shows the table result in ms-access.

Also, I have verified the ms-access file using it via asp.net, when i connect to the ms-access file using the same DSN which i used for PHP, i am able to query linked table and able to get result on web page.
 [2015-01-13 19:58 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2015-01-13 19:58 UTC] ab@php.net
Hi @rajendradewani,

thanks for the further info.

I just wonder why I get the same warning if there's definitely no another ODBC data source, that's why I suspect the db file itself. Of course it might lay on PHP, especially after you say it works with pure Access or ASP. Or maybe the DB file is missing something, can't tell yet.

But for the reproducer - the way you suggest to do it is really not the way. I'll explain why

- we need a synthetic reproduces so we can create a phpt case from it
- the setup probably takes much longer than the fix itself

That's why we always ask for "short self enclosed snippets". For one - it really spares time for the fixing itself, and also - often the reporter finds completely other cause of the issue after working on such a snippet (or the opposite). Therefore I'd like to ask again for a short PHP snippet (well, we have it), two DB files, two DSNs, two tables (one local and one linked). Basically - as few dependencies as possible.

Thanks.
 [2015-01-13 19:59 UTC] ab@php.net
-Assigned To: +Assigned To: ab
 [2015-01-25 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Oct 22 04:01:24 2020 UTC