Connecting to Access 97 Database

Talk about working with different database types

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Connecting to Access 97 Database

Postby AndyL » Jan 21st, '18, 11:31

I have a requirement to connect to an Access 97 database. I can't update it or change it to something else. I'm connecting from a PC running Windows 10, Apache/2.4.7 (Win32) PHP/5.5.8.
The connection I am using is:

$sql="select * from SOCList";
$default_order="";
$dgrid->DataSource('PDO', 'odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\\XX\\XX\\XX\\XX\\XX\\data.mdb', $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);

I don't get any errors from the connection.

All I get is this:

POST:

GET:

FILES:

Array
(
)


Errors:

1)


Warnings:

1) Field myfieldname, used in the list of fields in View Mode was not found in SELECT SQL! Please, check carefully your code syntax and field name, it may be case sensitive!

DataGrid v7.9.9 | Extension: PDO | Database: odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\xxx\xxx\xxx\xxx\xxx\data.mdb | Total running time: 0.036792 sec.


There is data in the database. I have a primary key (called idx) that is an integer, autoincrement. If I try a sql command like

$sql="select idx,myfieldname from SOCList";

I get the same result.

Does anyone have any ideas how to connect to an Access database?

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: Connecting to Access 97 Database

Postby administrator » Jan 23rd, '18, 10:36

What error you get?
Check this:
viewtopic.php?f=33&t=1519

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: Connecting to Access 97 Database

Postby AndyL » Jan 23rd, '18, 17:53

I don't get any errors. The entire output is what you see in my first post.
I did look at the link you provided. I thought I had the correct syntax. The example shows:

New syntax:
$dgrid->DataSource("PDO", "odbc", $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);

[provided by Curzio Gallorini]


So my syntax was:

$dgrid->DataSource('PDO', 'odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\\xxx\\xxx\\xxx\\xxx\\xxx\\data.mdb', $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);

I presume that this is correct? In the example, it just says "odbc" as the second paramter, but doesn't tell you what you need to put there. Can you give an example of connecting to an Access database?

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: Connecting to Access 97 Database

Postby AndyL » Jan 23rd, '18, 21:10

OK, so this bit of code works:

$database_name="D:\\xxx\\xxx\\xxx\\xxx\\xxx\\data.mdb";
try {
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)};
Dbq=$database_name ; username=; password=;");

$db ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Success";
}
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}


"Success" is printed out.

So it would seem that the PDO driver is working correctly on my machine.

$dgrid->DataSource('PDO', 'odbc:DRIVER={Microsoft Access Driver (*.mdb)};Dbq=D:\\xxx\\xxx\\xxx\\xxx\\xxx\\data.mdb', $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);
gives the errors:
1) Line: 865 SQLSTATE
[HY000] SQLDriverConnect: -1811 [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.

So it would point to the way the grid sets up a new data source.

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: Connecting to Access 97 Database

Postby administrator » Jan 24th, '18, 09:39

What is 1) Line: 865 SQLSTATE for you? Did yo try to debug it?

Also try to use the latest version 8.2.7
Does it work for you?

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: Connecting to Access 97 Database

Postby AndyL » Jan 24th, '18, 13:09

I'd love to try 8.2.7, but my lifetime update says "Wait on Next Release".

I've requested the latest version.

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: Connecting to Access 97 Database

Postby administrator » Jan 25th, '18, 14:40

Contact our support: https://apphp.com/support/ and they will send you latest version.

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: Connecting to Access 97 Database

Postby AndyL » Jan 25th, '18, 21:14

Ok, so I have the latest version. There isn't much difference between that and the one I had, to be honest. Here is my PDO connection string:

$DB_USER='';
$DB_PASS='';
$DB_HOST='';
$DB_NAME="D:/data.mdb";
$sql="select * from SOCList";
$default_order="";
$dgrid->DataSource('PDO', 'odbc', $DB_HOST,$DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);

And I modified datagrid.class.php around line 832
}else if($db_driver == 'sqlsrv'){
$host_parts = explode(':', $DB_HOST);
$DB_PORT = (isset($host_parts[1])) ? ';port='.$host_parts[1] : '';
$DB_HOST = (isset($host_parts[0])) ? $host_parts[0] : $DB_HOST;
$db_conn = new PDO('sqlsrv:Server='.$DB_PORT.';Database='.$DB_NAME, $DB_USER, $DB_PASS);
}else if ($db_driver == 'odbc'){
$db_conn = new PDO($db_driver.':DRIVER={Microsoft Access Driver (*.mdb)};Dbq='.$DB_NAME, $DB_USER, $DB_PASS);

}else{
$db_conn = new PDO($db_driver.':host='.$DB_HOST.';dbname='.$DB_NAME, $DB_USER, $DB_PASS);

You can see that I basically hard coded the Access driver.

Now I get the following:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 0 [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)' in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php:2512 Stack trace: #0 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(2512): PDO->query('SET character_s...') #1 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(2260): DataGrid->SetEncodingOnDatabase() #2 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(1640): DataGrid->GetDataSet(' ORDER BY 1 AS...', '', '', '', false) #3 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\MeyerApphp.php(74): DataGrid->Bind() #4 {main} thrown in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php on line 2512

It seems to be where the SQL command SET character_set_client=utf8; is sent to the driver. It looks like this is not valid syntax for access.

Adding this at line 2497:

case 'odbc';
case 'pgsql':
break;
default:

Now gives:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IM001]: Driver does not support this function: driver doesn't support meta data' in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php:5914 Stack trace: #0 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(5914): PDOStatement->getColumnMeta(0) #1 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(4658): DataGrid->GetFieldOffset('wmcoilnumber') #2 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php(1878): DataGrid->SortColumns('view') #3 C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\MeyerApphp.php(74): DataGrid->Bind() #4 {main} thrown in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php on line 5914

So I think that whatever SQL is passed to the driver, is rejected by the Access driver. I guess that the SQL dialect that Access speaks (which is none standard) is not supported by the grid.

Looks like I may have to find another grid that supports it.

administrator
Site Admin
Site Admin
Posts: 5981
Joined: Jan 7th, '09, 23:18
Contact:

Re: Connecting to Access 97 Database

Postby administrator » Jan 28th, '18, 19:29

It seems to be where the SQL command SET character_set_client=utf8; is sent to the driver. It looks like this is not valid syntax for access.

Try to disable or comment this command:

AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: Connecting to Access 97 Database

Postby AndyL » Jan 30th, '18, 12:14

administrator wrote:
It seems to be where the SQL command SET character_set_client=utf8; is sent to the driver. It looks like this is not valid syntax for access.

Try to disable or comment this command:


Tried that. Then I got:

Notice: Undefined variable: meta in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php on line 5915

Notice: Undefined variable: meta in C:\Web\Apache\htdocs\mpc_reportsCopy18Jan\datagrid\datagrid.class.php on line 5915
+ Add New View Delete

No data found


Retrieve data for current mode #2:
select sql (total: -1 | time: 0.3127 sec.) select * from SOCList WHERE 1=1 ORDER BY 1 ASC

Retrieve data for current mode #2:
select sql (total: -1 | time: 0.3036 sec.) select * from SOCList WHERE 1=1 ORDER BY 1 ASC

POST:

GET:

FILES:

Array
(
)


No data was found. I ran the select command on the same database using another tool, and it does produce a recordset.


Return to “Databases: different types implementation”