UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Can I Get All Table Names From Mysql Db?, Access 2007    
 
   
panoss
post Sep 25 2015, 11:29 AM
Post#1



Posts: 46
Joined: 6-November 14



From Access 2007 I connect to MySQL (database='joomla343', table='category') with DAO in this way:

CODE
    strOdbcCon = "ODBC;" & _
                  "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
                  "SERVER=localhost;Port=3306;" & _
                  "DATABASE=joomla343;" & _
                  "USER=root;" & _
                  "PASSWORD= "

     strSql = "SELECT * FROM [" & strOdbcCon & "].category "

     Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
     Set rs = db.OpenRecordset(strSql, dbOpenDynaset)


It works fine.

But how do I get the names of all the tables in this MySQL db?
In MySQL the names can be retrieved with query "SHOW TABLES".

I tried:
strSql = "SHOW TABLES [" & strOdbcCon & "] "
strSql = " [" & strOdbcCon & "] SHOW TABLES "

They both fail.
Is there a way to get all table names from MySQL db?
Go to the top of the page
 
DanielPineault
post Sep 25 2015, 12:01 PM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



How about using the TableDefs collection?
Go to the top of the page
 
panoss
post Sep 25 2015, 12:09 PM
Post#3



Posts: 46
Joined: 6-November 14



In the way I connect to MySQL, I get a recordset, e.g from a table.
In this way, I don't thin I can not use TableDefs.

Unless there is a better way to connect to MySQL using DAO (ADO doesn't work in this pc), I'm looking forward to hearing from you.

(a command like Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC) raises error: Runtime Error 3847 ODBCDirect is no longer supported. That's why I use this way to connect to MySQL)
Go to the top of the page
 
rabroersma
post Oct 2 2015, 08:44 AM
Post#4


UtterAccess VIP
Posts: 1,293
Joined: 1-January 07
From: Whittier, California, USA


Another way is to query Mysql's information schema for it's table names:

https://dev.mysql.com/doc/refman/5.0/en/tables-table.html
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 01:10 AM