I also have not tried using MySQL views in Access but I have done it
before using MSSQL and other databases. So, like John B. below, I
wanted to try this out.
I am using:
Windows XP Pro SP2
MySQL ODBC 3.51.12.00
MDAC 2.8 SP1 on Windows XP SP2
Access 2003 (11.6566.6568) SP2
I created a view in MySQL Server and verified it is working. Then I
launched Access and created a new blank database. I created a new ODBC
System data source using MySQL ODBC and verified it connects. Then in
Access I linked in all the tables and this new (and only) view from
MySQL database (selecting the save password checkbox in the link tables
dialog in Access). The view prompted for a unique key when linking but
I clicked Cancel on that. All the tables and this view show up in
Access as linked.
So I open each table and all tables except one are displaying the MySQL
data fine. That one is showing #Deleted in each column. This is the
only large table I have, the other ones are less than 100 records but
this one has 25000 records. Don't know if that is the problem here.
Now, I try to open the view and I get the same error as Daevid Vincent
is getting below:
"Could not execute query; could not find linked table"
So what does this mean -- I'm not sure but I am using a lower version of
MySQL than what both John B. and Daevid Vincent are using.
Just wanted to through my 2-cents in on this and see if others are able
to make this work.
From: jbonnett@stripped [mailto:jbonnett@stripped]
Sent: Wednesday, April 26, 2006 9:52 PM
To: daevid@stripped; myodbc@stripped
Subject: RE: VIEW not working with myODBC in XP and Access 2003
I have never played with views in MySQL so I had a try and created a
view and was able to get to it OK in Access. I am using XP Pro, MySQL
5.1.9, Access 2003, MyODBC 3.51.11, MDAC 2.8
From: Daevid Vincent [mailto:daevid@stripped]
Sent: Thursday, 27 April 2006 9:18 AM
Subject: VIEW not working with myODBC in XP and Access 2003
I have a critical problem that I hope there is a simple solution for.
I've just spent a couple days converting a very messy "hack" to populate
table using a much more elegant VIEW solution now.
Everything is going great, except now the whole point of this VIEW is so
that people using MS Access (or other ODBC) can use the VIEW.
It doesn't work!?!
I've followed all of this:
I can import/link any other non-VIEW table.
I've given FULL permissions to my ODBC user in mysql.mysql.user (and
appropriate) places just in case.
Access pops up an error box that says "Could not execute query; could
find linked table"
I'm using these versions:
root@daevid111:/lockdown# mysql --version
mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc