Blog of spblinux

Adding a web frontend (LAMP server) to a local windows application with mssql database (Windows 2008R2 server with mssql 2008). ms access 2003 installed on windows 2008 server is used to synchronize data from mssql to mysql.

1. mssql database -> ms access 2003 (passthrough query using odbc)
2. ms access -> mysql (passthrough query using odbc with mysql tunneled by ssh)
3. windows task scheduler runs ms access (vba code to run queries)

Other software which has to be installed:

• MySQL Connector/ODBC 5.1 (32 bit)
• plink.exe, putty.exe (PuTTY)

Configure odbc:

ms access 2003 (usually) is a 32 bit application, stored in program files (x86), so 32 bit odbc connectors have to be used (reference).
To setup 32 bit odbc dsn files open %windir%\SysWOW64\odbcad32.exe (on 64 bit windows searching for odbc opens 64 bit odbc).

• Create a file dsn for mssql
• Run PuTTY, connect to the mysql server host with port forwarding: local 3306 to 127.0.0.1:3306 on mysql server.
  Then create a file dsn for mysql: tcp/ip 127.0.0.1, port 3306
• Run ms access and create linked tables with create table wizard: linked table / file type odbc / open file dsn.
  Select source table(s) in mssql and destination tables in mysql (activate save password option).
• Tools / database tools / linked tables manager has to be run if the layout of a linked table has been changed on the server.

Passthrough queries in ms access:

• Create a new query, do not add tables into the query, switch to SQL mode and set the type of the query to SQL / Pass-Through
• Open the properties window and set ODBC-Connection to
  ODBC;Description=…;DRIVER=…;SERVER=…;UID=…;PWD=…;DATABASE=…
  and additionally for mysql
  PORT=3306;CHARSET=utf8;DFLT_BIGINT_BIND_STR=1
  (The field values, except PWD (password), can be found, if the odbc file dsn is opened with a text editor)
• To run passthrough queries automatically the odbc password has to be stored in clear text(!);
  so both database servers, mssql and mysql, should have a user and password only used for odbc.
• To test the SQL code for passthrough queries the statements should be run on the server:
  mssql with SQL Server Management Studio and mysql with PHPMyAdmin

Transfer of data from mssql to mysql: tblSrc to tblDest

• on mysql server create tblDest_tmp with same layout as tblDest and create a linked table tblDest_tmp in ms access
• in ms access setup a mysql passthrough query with name step1 and SQL code
  TRUNCATE TABLE tblDest_tmp
  (set property return records to false)
• setup a mssql passthrough query with name qrySrc_mssql and SQL code which converts the data of tblSrc to the layout of tblDest, e.g.
  SELECT tblSrc.phonenumber AS phone FROM yourdb.dbo.tblSrc
• setup a ms access append query with name step2 which uses source “table” qrySrc_mssql and destination table tblDest_tmp, e.g.
  INSERT INTO tblDest_tmp (phone) SELECT phone FROM qrySrc_mssql;
  (it is possible to choose fields in ms access design view mode)
• setup a mysql passthrough query with name step3 which uses tblDest_tmp to update tblDest, e.g.
  INSERT INTO tblDest (phone) SELECT t.phone FROM tablDest_tmp AS t
  ON DUPLICATE KEY
  UPDATE phone=t.phone
  (set property return records to false)
• to transfer data start ssh port forwarding and run the queries
  step1 (which removes all data from tblDest_tmp)
  step2 (which copies data from qrySrc_mssql (fetching data from tblSRc) to tblDest_tmp)
  step3 (which updates tblDest with data from tblDest_tmp)
  stop ssh port forwarding
  (bug: if port forwarding has been stopped, ms access has to be closed and reopened;
  else the queries fail with an odbc error (mysql); some reinitialization of odbc seems to be needed.)

Preparations to run the queries automatically by VBA:

• Simple preliminary setup: create a form and use the create button wizard to put 3 buttons on the form
  which run the queries step1, step2, step3.
• Two other buttons can be created which start and stop ssh port forwarding:
  with the create button wizard choose run application and choose as command line
  “your_path\plink.exe” -L 3306:127.0.0.1:3306 -i “your_path2\keyfile.ppk” -ssh -2 -l your_user -N your_server.com
  and (assuming a non administrative user is logged in and only one instance of plink.exe runs in the account of this user)
  taskkill /f /im plink.exe
  With PuTTYgen an openssh public key can be converted into ppk format used by plink.exe.
  To be able to run these commands by windows task scheduler all network drive mappings have to be replaced by unc names
  (e.g. \\yourbox\tools\plink.exe, not N:\tools\plink.exe)

Using VBA code to run queries:

• to make it easier to maintain the SQL code of the queries, VBA makes temporary copies of existing queries
  (vba code based on inspiring work of mdlueck)
  Sub deleteQDF(name)
  On Error Resume Next
  CurrentDB.QueryDefs.Delete(name)
  End Sub
• passthrough query: (step1 and step3)
  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim strQryNameBe As String
  strQryNameBe=”yourTmpQuery”
  deleteQDF(strQryNameBe)
  Set daoDB=CurrentDb()
  Set  daoQDFbe=daoDB.CreateQueryDef(strQryNameBe)
  With daoQDFbe
  .Connect=daoDB.QueryDefs(“yourExistingQuery(replace by step1 or step3)”).Connect
  .SQL=daoDB.QueryDefs(“yourExistingQuery(replace by step1 or step3)”).SQL
  .ReturnRecords=False
  .Execute dbFailOnError
  .Close
  End With
  deleteQDF(strQryNameBe)
  Set daoDB=Nothing
  Set daoQDFbe=Nothing
• append query (step2; with passthrough query qrySrc_mssql as source):
  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim strQryNameBe As String
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameFe As String
  strQryNameBe=”yourTmpQuery”
  strQryNameFe=”yourTmpQuery2″
  deleteQDF(strQueryBe)
  deleteQDF(strQryNameFe)
  Set daoDB=CurrentDb()
  Set  daoQDFbe=daoDB.CreateQueryDef(strQryNameBe)
  With daoQDFbe
  .Connect=daoDB.QueryDefs(“qrySrc_mssql“).Connect
  .SQL=daoDB.QueryDefs(“qrySrc_mssql“).SQL
  .ReturnRecords=False
  .Close
  End With
  Set  daoQDFfe=daoDB.CreateQueryDef(strQryNamefe)
  With daoQDFfe
  .SQL=daoDB.QueryDefs(“step2“).SQL
  .Execute dbFailOnError
  .Close
  End With
  deleteQDF(strQryNameBe)
  deleteQDF(strQryNameFe)
  Set daoDB=Nothing
  Set daoQDFbe=Nothing
  Set daoQDFfe=Nothing
• run application (start plink.exe for ssh port forwarding; run taskkill to terminate plink.exe; use Chr(34) to insert double quotes into commandline):
  Dim strAppName as String
  Dim qq
  qq=Chr(34)
  strAppName= qq & “path1\plink.exe” & qq & ” -L 3306:127.0.0.1:3306 -i ” & qq & “path2\key.ppk” & qq & ”  -ssh -2 -l dbUser -N dbserver.com”
  Call Shell(strAppName, 1)
  …
  strAppName=”taskkill.exe /f /im plink.exe”
  Call Shell(strAppName,1)

Autostart version of ms access database mdb file:

• Evaluate command line argument:
  open mdb database with:
  “yourPathToMsOffice\msaccess.exe” “yourPathToMdbFile\yourDb.mdb” /cmd autostart
• Create a macro with name AutoExec by new macro wizard of ms access, choose macro type run code and write to field function name:
  AutoExec(“autostart”)
  and create a VBA function with VBA-Editor (inside a standard module of VBA editor; not inside a form_module)
  Function AutoExec(ByVal strCmd As String) As Boolean
  If strCmd=Command Then
  ‘ autostart VBA code
  …
  DoCmd.Quit
  End If
  AutoExec=True
  End Function
• The code inside the if condition of the VBA function Autostart(strCmd) gets only executed
  if the command line of ms access.exe ends with /cmd autostart. (DoCmd.Quit closes the ms access application.)
  Else the macro AutoExec calls the VBA function AutoExec(strCmd) and returns immediately.

Use Windows Task Manager to run the queries:

• For security reasons a normal non administrative user account should be used to autorun the queries.
  This user needs the privilege to login as batch user (reference):
  Start / Administrative tools / Local Security Policy
  Security Settings / Local Policies / User Rights Assignment / Log on as a batch job
  click right mouse button and choose Properties
  Click Add user or Group to give the privilege to the user.
• Batch user login does not map network drives. Use UNC names instead:
  replace N:\yourSharedFolder by \\yourServer\yourShare\yourSharedFolder
  (e.g. ssh port forwarding: path of plink.exe and path of ppk file in VBA code)
• Batch tasks do not display any window. So code run by task manager has to be checked on the desktop.
  Single stepping through VBA code inside VBA editor is recommended.
  Batch tasks are only shown in task manager if show all users is active.
• Open Windows task manager with administrative privileges, else new tasks cannot be created.
  Use right mouse click run now to immediately try out a scheduled task.
• Create a new task (not simple task):
  – define user to run the task
  – run independently of user login (=run with batch login) and store password
  – run with high priority
  – trigger by timer, e.g. daily
  – action: run program:
  “C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE”
  use arguments
  “yourPath\yourDatabase.mdb” /cmd autostart
  working directory can be left blank
  – properties: stop after 1 hour and force exit

Watch how local windows application data automagically displays in web frontend.