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!
jumpmanjayAsked:
Who is Participating?
 
iamanindianConnect With a Mentor Commented:
Well...thanks for the appreciation, I am not too clear about this process...so perhaps our Moderator needs to help on this issue.

However, looking at your solution..."TABLE_NAME" wasn't the cause of the problem (it would have thrown error..but wouldn't have resolved the permission issue)...OpenSchema did the trick.

Good to know that your system is working...that is what matters.
CHEERS!

WC


0
 
aymansoftCommented:
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];
....
..
.
0
 
jumpmanjayAuthor Commented:
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. ;)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
iamanindianCommented:
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
0
 
iamanindianCommented:
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
0
 
jumpmanjayAuthor Commented:
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.
0
 
jumpmanjayAuthor Commented:
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?
0
 
iamanindianCommented:
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
0
 
jumpmanjayAuthor Commented:
yep, thats exactly it. hopefully we can get this! :)
0
 
iamanindianCommented:
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
0
 
iamanindianCommented:
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
0
 
jumpmanjayAuthor Commented:
my system doesnt even have the system1.mdf (or system.mdf) file, so im getting an error. let me try something really quick...
0
 
jumpmanjayAuthor Commented:
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();
0
 
jumpmanjayAuthor Commented:
is there a way for me to assign partial points in appreciation of you helping out?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.