none
creating external database links in ado RRS feed

  • Question

  • Never mind, it all works, i was passing the incorrect database name. Oops. Smile Yay!



    Sorry this is probably the wrong forum for this, but here's the situation. I'm trying to migrate a databases backend from Access to Mysql while keeping Access as the front-end. In order to do so I've written a set of 19 python files to do the task at hand. I've installed the ODBC driver MySQL ODBC 3.51 Driver, and am trying to get the script to link the tables:

    My problem is that I can't seem to find the relevant connection string (connstring in the examples below), I've tried with and without ODBC, tried with and without DSN, I can't seem to get one to work. The way the script is below I get "ODBC--Call Failed", without the ODBC line i always get "Unable to find ISAM", could someone help me please? I'm at wits end trying to figure this out, and this is the last thing I need before I can deploy the databases.


    #!/usr/bin/env python
    import sys
    import string
    import os
    from entry import *
    from removecomments import *
    import AccessDBHelper
    import configurebase

    class SetupAccessTables(configurebase.configurebase):
        def __init__(self):
            configurebase.configurebase.__init__(self)
            self.setuptables()

        def setuptables(self):
            for file in self.files:
                filename = "Databases\\" + file.getValue() + ".mdb"
                print 'Filename is: ' + filename
                dbconn = AccessDBHelper.AccessDb(filename, "admin")
                connstring = 'ODBC;Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver};Server='+self.GetConfig('mysqlhost').getValue()+';Port='+self.GetConfig('mysqlport').getValue()+';Database='+file.getValue()+';User='+self.GetConfig('mysqluser').getValue()+';Password='+self.GetConfig('mysqlpass').getValue()+';Option=16426;'
    #                        connstring = 'ODBC;DSN=myodbc;DATABASE='+file.getValue()+';UID='+self.GetConfig('mysqluser').getValue()+';PWD='+self.GetConfig('mysqlpass').getValue()
                tables = dbconn.getTableNames()
                for table in tables:
                    dbconn.runQuery("DROP TABLE " + table)
                    dbconn.createODBCTable(table, connstring, table)
                relations = dbconn.getSynonymNames()
                for rel in relations:
                    dbconn.runQuery("DROP TABLE " + rel[0])
                    dbconn.createODBCTable(rel[0], connstring, rel[2])
    #                dbconn.createODBCTable(rel[0], 'ODBC;DATABASE='+file.getValue()+';DSN=myodbc;OPTION=0;PORT=0;', rel[2])
                dbconn.closeConn()

    ''' Snippet from AccessDBHelper.AccessDb
        def createODBCTable(self, name, DSN="", remotename="" ):
            table = Dispatch('ADOX.Table')
            cat = Dispatch('ADOX.Catalog')
            cat.ActiveConnection = self.connAccess
            table.Name = name
            table.ParentCatalog = cat
    #        table.Type = 'PASS-THROUGH'
            print "DSN = " + DSN + " Remotename = " + remotename
            if(not DSN == ""):
                    table.Properties("Jet OLEDB:Create Link").Value = True
                    table.Properties("Jet OLEDB:Link Provider String").Value = DSN
                    table.Properties("Jet OLEDB:Remote Table Name").Value = remotename
            cat.Tables.Append(table)
            return table

    The exact error for the above (with or without Provider=) is:
    com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft JET Database Engine', u'ODBC--call failed.', None, 5003146, -2147467259), None)



    With: connstring = 'DATABASE='+file.getValue()+';DSN=myodbc;OPTION=0;PORT=0;' (Which is exactly what it looks like in the database file created in access) I get:

    com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft JET Database Engine', u'Could not find installable ISAM.', None, 5003170, -2147467259), None)


    The existing database has a link to mysql already in it (in access 2000 format created in Access2007) and the table definition looks like:
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Temporary Table Value:False
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Table Validation Text Value:
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Table Validation Rule Value:
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Cache Link Name/Password Value:False
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Remote Table Name Value:tbl_account active wo
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Link Provider String ValueBig SmileATABASE=accounting 7_7;DSN=myodbc;OPTION=0;PORT=0;
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Link Datasource Value:
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Exclusive Link Value:False
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Create Link Value:True
    Table Name: tbl_account active wo Table Type: PASS-THROUGH Property: Jet OLEDB:Table Hidden In Access Value:False

    Sunday, November 30, 2008 2:30 AM

Answers