An Access Front-End to MySQL - Limitations

(Page 3 of 4 )

First and foremost we know that data can be maintained using Access as a front-end. This is nothing to sniff at but what else can we do?

Let’s try out some queries developed for use with a MySQL database.

Look at the one below for example:

 SELECT DISTINCT tblusers.usercode as uc, monthbooked,
 daybooked, fldrate
 FROM tblrates, tblbookings, tblusers
 WHERE tblusers.usercode = tblbookings.usercode
 AND tblbookings.monthbooked = tblrates.fldmonth
 AND tblusers.billing = 1
 AND ISNULL(tblbookings.dateinvoiced)
 ORDER BY uc, monthbooked, daybooked

This query executes within MySQL but when tried from Access, returns an error on the aliased field (“uc”) in the “ORDER BY” clause. This is remedied rather easily by replacing the aliased field with its actual name, “tblusers.usercode”. A promising start but the query:

 SELECT username FROM tblonline
 WHERE fldwhen > (SUBDATE(CURTIME(),
 INTERVAL '10' MINUTE))

needs to be rewritten to use the Access-specific function “Datediff”. In fact no MySQL-specific functions may be used. Remember, you are dealing with linked tables from within Access, and this is going to limit the ability of the driver to support MySQL query syntax. It is for this reason that data definition queries on the linked tables, such as the one below, will not execute.

 ALTER TABLE tbllinks ADD COLUMN fldtype varchar(35)

You can however create local tables from remote ones, and in some situations this can prove useful.

Likewise none of the metadata methods of MySQL such as “SHOW” or “DESCRIBE” will work.

It quickly becomes clear that linked tables are treated as though they were Access tables, so knowledge of Access SQL syntax is an absolute must if you wish to create queries.

With large tables there may also be performance issues to consider when using Access as a front-end. Clicking on a linked table and bringing all the data across the network/internet will definitely slow performance.

Next: State of the Union >>
 

More Microsoft Access Articles
More By Peter Lavin

Comments On: An Access Front-End to MySQL

· One other thing worth mentioning is that if you use pass thru queries you can use...    · Thanks for the article. I also found that an effective way to transfer a single...    ·     ·     · Question?? I am trying to add records to a Mysql table using access. The primary...    · RTFM ;-) *kidding*Have one more dummy column with TIMESTAMP as the data type,...    · I have Access set up as a front end to a MySQL database. I have a form in Access...    · I have Access set up as a front end to a MySQL database. I have a continuous...    · The problem was fixed. When I created the column with TIMESTAMP as the data...   >>> Post your comment now!