CreateTableDef not Updatable, How?

[nik]

Hi,

In trying to relink some tables to MySQL ODBC tables with the username
and password fields, I keep ending up with Updatable=False tables,
even though the MySQL tables are Updatable, and if I create the linked
tables manually, they are Updatable.

Here is the basic code (VB2007 and MySQL):

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dbSQL As DAO.Database
Dim dbLocal As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String
sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"

Set dbSQL = DBEngine.OpenDatabase("", False, False, sConnect)
Set dbLocal = CurrentDb()

dbLocal.TableDefs.Delete mdlConst.sLOCALLINKTABLENAME
Set tdf = dbLocal.CreateTableDef(mdlConst.sLOCALLINKTABLENAME, _
dbAttachSavePWD, _

mdlConst.sREMOTELINKTABLENAME, _
sConnect)

dbLocal.TableDefs.Append tdf
dbLocal.TableDefs.Refresh

Set tdf = Nothing
dbSQL.Close
dbLocal.Close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

All of the tables in dbSQL are Updatable = True
None of the tables that I create using CreateTableDef are Updatable,
and since that property is readonly, I can't change it.

How do I create an Updatable table? An Updatable linked table?

My first attempt to just reset the connect property hit a wall:
http://groups.google.com/group/micr...634c7a0bfe?lnk=gst&q=nikbaer#db64c7634c7a0bfe

Thanks,
Nik

 

[Douglas J. Steele]

Do the tables to which you're linking have primary keys defined (or at least
unique indexes)?

ODBC-linked tables aren't updatable unless they do.

 

[nik]

Do the tables to which you're linking have primary keys defined (or at least
unique indexes)?

ODBC-linked tables aren't updatable unless they do.

Thanks for the suggestions, but I saw that hint in another thread and
I probably should have added that all of my BE tables already have
primary keys defined.

The tabledefs of my dbSQL, which is connected to the BE, are
updatable. The local tables, in my dbLocal are updatable, until I
replace them with the newly created tabledef, so I think it is
something to do with my CreateTableDef command, is there a parameter
for updatable?

Thanks,
Nik

 

[Douglas J. Steele]

No, there's no parameter for updatable.

 

[nik]

No, there's no parameter for updatable.

Given that the TableDefs available when I open the BE MySQL database
are updatable, and that the MySQL tables have primary keys, is there
any other reason why my CreateTableDef command creates un-updatable
tables???

 

[nik]

No, there's no parameter for updatable.

OK, it looks like the TableDefs.Append command is changing the
TableDef from Updatable=True to Updatable=False, I opening the BE
MySQL database and am just using the connection string, ie.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dbLocal As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String
sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"

Set dbLocal = CurrentDb()

dbLocal.TableDefs.Delete mdlConst.sLOCALLINKTABLENAME
dbLocal.TableDefs.Refresh
Set tdf = dbLocal.CreateTableDef(mdlConst.sLOCALLINKTABLENAME, _
dbAttachSavePWD, _

mdlConst.sREMOTELINKTABLENAME, _
sConnect)

'''''' tdf.Updatable =
True !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
dbLocal.TableDefs.Append tdf
''''' tdf.Updatable = False, and the new TableDef in the database
- dbLocal.TableDefs(19).Updatable= False ?????????????
dbLocal.TableDefs.Refresh

Set tdf = Nothing
dbLocal.Close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[nik]

OK, it looks like the TableDefs.Append command is changing the
TableDef from Updatable=True to Updatable=False, I opening the BE
MySQL database and am just using the connection string, ie.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim dbLocal As DAO.Database
    Dim tdf As DAO.TableDef
    Dim sConnect As String
    sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
                "SERVER=" & mdlConst.sMYSQLSERVERNAME &";" & _
                "PORT=3306;" & _
                "DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
                "USER=" & mdlConst.sMySQLUSERNAME & ";"& _
                "PASSWORD=" & sPassword ' & ";"

    Set dbLocal = CurrentDb()

    dbLocal.TableDefs.Delete mdlConst.sLOCALLINKTABLENAME
    dbLocal.TableDefs.Refresh
    Set tdf = dbLocal.CreateTableDef(mdlConst.sLOCALLINKTABLENAME, _
                                                    dbAttachSavePWD, _

mdlConst.sREMOTELINKTABLENAME, _
                                                    sConnect)

     '''''' tdf.Updatable =
True !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
     dbLocal.TableDefs.Append tdf
     ''''' tdf.Updatable = False, and the new TableDef in the database
-  dbLocal.TableDefs(19).Updatable= False ?????????????
     dbLocal.TableDefs.Refresh

    Set tdf = Nothing
    dbLocal.Close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I found a different method that actually works... Instead of creating
and appending a TableDef use DoCmd.TransferDatabase.

DoCmd.TransferDatabase(TransferType, DatabaseType, DatabaseName,
ObjectType, Source, Destination, StructureOnly, StoreLogin)

My code now looks like:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dbLocal As DAO.Database
Dim sConnect As String
sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"
Set dbLocal = CurrentDb()

dbLocal.TableDefs.Delete mdlConst.sLOCALLINKTABLENAME
dbLocal.TableDefs.Refresh
DoCmd.TransferDatabase acLink, "ODBC Database", _
sConnect, acTable, mdlConst.sREMOTELINKTABLENAME,
mdlConst.sLOCALLINKTABLENAME

dbLocal.Close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


For more info see:
'http://www.blueclaw-db.com/transferdatabase-docmd.htm

Thanks for the help,
Nik