Sql-server – Connect to Microsoft Access database via Microsoft SQL Server / MySQL

ms accesssql server

A client has an Microsoft Access database where they save their products.
To access the database now you have to login via VPN and open Microsoft Office Access.
I need to access this database from a PHP script running on another server.

I've done some research and found that it's not possible to connect to an Access database like a MySQL database.
So what I wonder is if it may be possible to set up an Microsoft SQL Server on the same server that uses the Microsoft Access database. So I can connect to the Microsoft SQL Server with the PHP script. Or if it's possible MySQL.

They are running Microsoft Windows Server 2008 and Microsoft Office Access 2003.

Best Answer

An access database is not a server, it's just a file. To access it, your server will need access to the filesystem on which it resides. Or, if you want to set up an SQL server on the same machine as the access database, it will still need access to the folder in which the file is stored.

A user logged into a VPN may not have the same drive mappings or file system permissions as a server process running on the same machine, so you will need to reflect this in the path to the file you use in the code.

Related Solutions

Sql-server – access 2003 to sql server 2008

You can use the Import/Export wizard with SQL Server. I came across a very good write-up (nice pretty pictures) here on it.

Sql-server – How to connect to a database which is a linked server via ODBC

You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.

Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need to leave the database name out. A query using a four-part name for Oracle might look like this:

SELECT name FROM OracleSvr..joe.titles WHERE id = 101

where "OracleSvr" is the name of the linked server.

Alternatively, here is an example of a statement that uses OPENQUERY. It should do the same thing, some people find this style easier to read.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

OPENQUERY is documented here.

No matter which style of query you choose, you would connect via your Python program to the SQL Server and then run the query.

You can do this via ODBC, you don't need to specify the database name when setting up the connection, you do it at query time as described above.

Best Answer

Related Solutions

Sql-server – access 2003 to sql server 2008

Sql-server – How to connect to a database which is a linked server via ODBC

Related Question