Use Microsoft Access to query your drupal database
Hope this helps someone. I used ODBC to MySQL over SSH port fowarding on a Windows client. Here goes:
1) Download and install cygwin (www.cygwin.com) on your windows machine; make sure to include OpenSSH, tcp_wrappers, and zlib. There’s a nice set of instructions here: http://pigtail.net/LRP/printsrv/cygwin-sshd.html. NOTE: You can also use Plink.
2) Download and install MySQL Connector / ODBC 3.51 on your machine, located here: http://dev.mysql.com/downloads/connector/odbc/3.51.html. I downloaded the Windows Zip/Setup.EXE
3) I needed to comment out the line “skip networking” in /etc/my.cnf. I did this by 1) SSH into my server, 2) type cd /etc and press enter, 3) type pico my.cnf and press enter, 4) put a “#” in front of the phrase “skip networking” 5) Save 6) Restart MySQL server.
4) Now you’re ready to set up an SSH tunnel from your local machine to the host. Open Cygwin and use the following command:
ssh -N -f -L 3307:localhost:3306 user@mydomain.com.
5) Setup the ODBC connection. In Vista, goto Control Panel -> Administrative Tools -> Data sources and click Add. Select MySQL ODBC 3.51 Driver, click finish, and fill in the fields like such:
Data source name: You pick!
Description: You pick
Server: localhost
User: the name that you use to login to your server
Password: ******** Note: Your password needs to be 8 characters or less.
Database: the name of the database to connect to.
On the 2nd page of the dialog, enter port 3307
Click Test and you should be able to connect!
6) To connect MS Access to your database(s), goto external data -> import from ODBC, and when it says select data source goto machine data source. You should see your data source name. Click on that and follow the prompts. You’re good to go!
Sharing the DRUPAL database from Ms Access
More than that, you can use the Drupal database in Ms Access applications. You can do the following:
- Open an ODBC connection in the Access code and send SQL statemets to the Drupal database
- Create table LINKS in Access and manipulate the tables as Access tables. Is a powerful way of creating forms and manipulate the database.
More info in:
http://drupal.org/node/221388
--------- An example of ODBC connection:
' Create object ODBCDirect Workspace.
public pubDrupalConection as Connection
Set pubWrkodbc = CreateWorkspace("NewODBC", "", "", dbUseODBC)
Set pubDrupalConection = pubWrkodbc.OpenConnection("connection1", dbDriverNoPrompt _
, False, "ODBC;DSN=mysql_drupal_anlave")
pubDrupalConection.QueryTimeout = 10
--------- An example of use:
Public Function DRUPALEjecutaConsultaAccion(strQuery As String)
'Execute action querys.
'strQuery: an String containing the SQL action statement
Dim query1 As QueryDef
Set query1 = pubDrupalConection.CreateQueryDef()
query1.SQL = strQuery
query1.Execute
query1.Close
Set query1 = Nothing
End Function