Link to home
Start Free TrialLog in
Avatar of jumpmanjay
jumpmanjay

asked on

PHP getting a list of MS Access DB tables

hey, i need my php webpage to read in a specific access database and give a listing of the tables in that database.

here is the code i tried:

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("path/db.mdb") ." ;DefaultDir=". realpath("path");
$db_connection->open($db_connstr);
$rs = $db_connection->execute("SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Type)=-32764)) OR (((MSysObjects.Type)=-32756)) OR (((MSysObjects.Type)=5)) OR (((MSysObjects.Type)=1))");
$rs_fld0 = $rs->Fields(0);
while (!$rs->EOF) {
  echo "$rs_fld0->value<br>\n";
  $rs->MoveNext();
}
$rs->Close();
$db_connection->Close();

but it gives me this error:

Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'. in c:\www\analyzelog.php on line 113

this was pretty easy with asp, but i cant find anywhere how to do it with php. thanks!
Avatar of aymansoft
aymansoft

you can to connect MS Access from ODBC Function which is easy to use

the first use dsn from control panel
go to control panel -> Administrative Tools -> Data Sources (ODBC) -> add dsn file MS access and name the dsn for example (con)
$link=odbc_pconnect("con","","");
$sql="select * from students";
$q=odbc_exec($sql);

$row=odbc_fetch_array[no];
....
..
.
Avatar of jumpmanjay

ASKER

the ms access file is always dynamic. its always from a file that is uploaded...so that doesnt really help me.

...plus it doesnt get me a list of the tables in the db, which is what i really need. ;)
What you are trying to do is...reading the System Files, from the SYSTEM Database (generally found in C:\Program files\Microsoft office\Office\System.mdw or "C:\Program Files\Common Files\SYSTEM" folder or C:\Documents and Settings\<user>\Application Data\Microsoft\Access). For this, you need to give the correct permission to the user, so that user may read system tables. If you want to read/view all the Access Object in your php script, you have to give read permission to Access MSysObjects table.
So, once the permission is given (most likely IUSR permission set to read) your problem will be solved.

Hope this helps.

Regards
WC
Hmnnn...
After posting this I thought of trying it out & as it appears you have a simpler solution :)

Open the MS Access database you are working on & alter a few settings:
(1) Tools->Options->View->Show & check "System object"
(2) Tools->Security->User and Group Permissions->Select MSysobjects under Object Name List & select "Read Data"

Now try running your php script. I have been able to reproduce the error that you mentioned & now it works fine. Sorry for the initial post ...without testing :(

Regards
WC
thanks for the info...unfortunately, it is a little bit more complex than that.

basically, here are the related steps-

client (or us) collects log files, and saves them to our computer
we upload the zipped log files to the internal website
internal website stores the file, and when an IT user clicks on "analyze log file", the website unzips the file, and knows where to get the *.mdb file.
the website then opens the *.mdb file (which is an audit trail of events)
within the mdb file, there are tables...which the table names are dates...so ideally, i want to read these dates, so i know which tables to grab data from.
then the user will determine which table (date) to look at, and the website will then grab that information.

i have all steps of the the equation except for reading the dates (the table names)...and it has to be automated. hope this helps narrow down exactly what i need.
and by the way, like i said, ive done this exact script with asp, and was able to read the table names without a problem (without modifying permissions either on the file or folders)

...maybe there is another way for me to read the table names from access databases within php?
So, the problem is in the fact that you have a set of *.mdb files...so not possible to open a specific mdb file & alter the settings.
Hmnn...
The reason for such setting is to prevent unwanted entry to the system objects...but your requirement asks for a step further. Let us see if we can figure something out.

Regards
WC
yep, thats exactly it. hopefully we can get this! :)
Well...
I am changing the connection string to something like:
**************************
$db_connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db;Jet OLEDB:System Database=C:\Documents and Settings\<user>\Application Data\Microsoft\Access\System1.mdw;User Id=admin;Password=";
**************************

Here, you try with your own System Database path & see whether this works or not. I managed to run this...let us see what happens at your end.

Best wishes

Regards
WC
In fact...you may skip the "User Id=admin;Password=" part...as long as the System Database path is correct, this should work.

Regards
WC
my system doesnt even have the system1.mdf (or system.mdf) file, so im getting an error. let me try something really quick...
voila! i got it. i just used some of the old syntax from the asp code (openschema(20) and ("TABLE_NAME")), and it worked like a charm.

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; Dbq=". realpath("path/db.mdb") ." ;DefaultDir=". realpath("path").";";
$db_connection->open($db_connstr);
$rs = $db_connection->OpenSchema(20);
$rs_fld0 = $rs->Fields("TABLE_NAME");
while (!$rs->EOF) {
  echo "$rs_fld0->value<br>\n";
  $rs->MoveNext();
}
$rs->Close();
$db_connection->Close();
is there a way for me to assign partial points in appreciation of you helping out?
ASKER CERTIFIED SOLUTION
Avatar of Arijit S
Arijit S
Flag of India 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