How to retrieve data for a webpage from an MS Access database with password

Question

I have an MS Access database file that I want to copy into a MySQL to serve up on a webpage, the problem is the database is passworded. What I want to do is upload the file to the server then either strip the password or open it using the password so I can then copy it across to MySQL. The password is known and cannot be removed at source. I would like to do this with PHP if possible.

This is a recurring event, at max twice a day.

Having contacted my hosting the only way to use odbc is to upgrade to dedicated hosting at 10x the price of my current hosting. Looks like this one is a no go unless I can get at the data another way.

The database file is create on a set of electronic scales, I want to upload the file directly from the scales via a microcontroller to the server. — LogicTom, Oct 08 '11 at 16:28

score 1 · Accepted Answer · answered Oct 07 '11 at 14:30

To open it, the password should be passed along in the connection string... For PHP using odbc_connect, the syntax is available here. Since you say the password is known, this should work.

To remove it completely, you'd want to just open it in Access and save a copy without the password. I'm not sure that this can be automated easily. If you need to access the data and transfer it repeatedly, I'd say stick with the password int he connection string.

Example from the article linked to:

<?php
// Microsoft SQL Server using the SQL Native Client 10.0 ODBC Driver - allows connection to SQL 7, 2000, 2005 and 2008
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);

// Microsoft Access
$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password);

// Microsoft Excel
$excelFile = realpath('C:/ExcelData.xls');
$excelDir = dirname($excelFile);
$connection = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=$excelFile;DefaultDir=$excelDir" , '', '');
?>

Thanks, unfortunatly odbc_connect() isn't supported on my hosting at this time. Are there any alternatives? — LogicTom, Oct 07 '11 at 15:36
http://code.activestate.com/recipes/123709-microsoft-access-database-connectivity/ - or just google "PHP Access Connection String" for others. — David, Oct 07 '11 at 15:47

score 1 · Answer 2 · answered Oct 07 '11 at 15:52

Here is the DSN - Less connection code sample :

<?php
$db_connection = new COM("ADODB.Connection");

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("../databases/database.mdb") ." ;DefaultDir=". realpath("../databases");
$db_connection->open($db_connstr);
$rs = $db_connection->execute("SELECT * FROM Table");
$rs_fld0 = $rs->Fields(0);
$rs_fld1 = $rs->Fields(1);
while (!$rs->EOF) {
  print "$rs_fld0->value $rs_fld1->value\n";
  $rs->MoveNext(); /* updates fields! */
}
$rs->Close();
$db_connection->Close();
?>

How to retrieve data for a webpage from an MS Access database with password

2 Answers2