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    
post Sep 25 2015, 11:29 AM

Posts: 50
Joined: 6-November 14

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

    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
post Sep 25 2015, 12:01 PM

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

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

Posts: 50
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
post Oct 2 2015, 08:44 AM

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

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

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st May 2018 - 09:09 PM