?
Solved

PHP getting a list of MS Access DB tables

Posted on 2005-05-12
14
Medium Priority
?
657 Views
Last Modified: 2013-12-12
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!
0
Comment
Question by:jumpmanjay
  • 7
  • 6
14 Comments
 
LVL 1

Expert Comment

by:aymansoft
ID: 13992211
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
 

Author Comment

by:jumpmanjay
ID: 13992370
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
 
LVL 10

Expert Comment

by:iamanindian
ID: 14035379
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Expert Comment

by:iamanindian
ID: 14035461
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
 

Author Comment

by:jumpmanjay
ID: 14035570
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
 

Author Comment

by:jumpmanjay
ID: 14035600
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
 
LVL 10

Expert Comment

by:iamanindian
ID: 14035738
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
 

Author Comment

by:jumpmanjay
ID: 14035993
yep, thats exactly it. hopefully we can get this! :)
0
 
LVL 10

Expert Comment

by:iamanindian
ID: 14043789
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
 
LVL 10

Expert Comment

by:iamanindian
ID: 14043888
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
 

Author Comment

by:jumpmanjay
ID: 14044371
my system doesnt even have the system1.mdf (or system.mdf) file, so im getting an error. let me try something really quick...
0
 

Author Comment

by:jumpmanjay
ID: 14044387
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
 

Author Comment

by:jumpmanjay
ID: 14044390
is there a way for me to assign partial points in appreciation of you helping out?
0
 
LVL 10

Accepted Solution

by:
iamanindian earned 1500 total points
ID: 14044497
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question