accessing databases on different servers

catreya

Hello

I haven't come across this problem in this forum and hence am posting it (I hope I didnt miss it). Anyways, my ISP's php-mySQL serves most of my database and web data. There is one particular table that I need to access - a "mdb" file from my in-house server. It does not have a domain but just an IP. In order to test the php script I created a database on my test machine, created a DSN and I got the desired result. Here is the script:

....
$connection = odbc_connect('p1', 'root', '');
$sql = odbc_exec($connection, "select * from availableInv order by ItemCode");
.....

But when I changed the connection to the remote server and include its login and password, I get this warning

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in c:\inetpub\wwwroot\PBI\testAccess.php on line 10

Can someone help me out on this please? It is possible at all to connect to another physical server? If yes, how do I go about doing it? Thanks

rachel2004

Wouldn't you also have to create a DSN on the remote Microsoft db as you did on your development db?

catreya

We have configured the DSN on the db server.

On another note, all references that I have come across on the web to connect to an Access file assumes the .mdb file is local - (all address begin with c:...etc\someFile.mdb.) Also there is no mention anywhere of how to connect to a remote Access file i.e. the odbc connect strings do not consider a host name or a server name.

That makes me feel that an access file cannot be accessed via odbc remotely. For example I tried out this script

$db =& ADONewConnection('access');
	$dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\northwind.mdb;Uid=Admin;Pwd=;";
	$db->Connect($dsn);

But I still get errors. the value of dbq i entered as database@host

IS there anyone out there who thinks otherwise?

rachel2004

From the error message you posted, it doesn't seem that you have configured the DSN on the remote db server. If you have, then you may not be using the name (the DSN) correctly. You may want to investigate there to be completely confident that the problem doesn't lie there. Otherwise, you could be spinning your wheels.

When you connect to the remote Access file, you're not actually "connecting" to Access itself. You'll be connecting to the host platfrom (almost surely MS Windows of some version) on which there needs to be a DSN, ODBC to the Accesss mdb file.

rachel2004

I wanted to also mention (as a workaround), unless you absolutely need to use Access, just export your mdb file as a CSV (comma separated values). Once it is in that format, you could import the CSV file to MySQL. That way, you keep everything in MySQL which is better for scalability than Access.

catreya

Unfortunately I have to use the Access mdb table. I am wondering if I am giving my connect statement correctly

....
$connection = odbc_connect('IPaddress//mySQLInventory.mdb', 'username', 'password');
$sql = odbc_exec($db, "select * from availableInv order by ItemCode");

i also tried the following without success:

include('..\\adodb\\adodb.inc.php'); 
	$db =& ADONewConnection('odbc_access');
	$dsn = "Driver={Microsoft Access Driver (*.mdb)};Server=IP address;Database=mySQLInventory;";
	$db->Connect($dsn,'username','password');

stevesweetland

did you get this working?