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 a
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:
http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html

I can import/link any other non-VIEW table.

I've given FULL permissions to my ODBC user in mysql.mysql.user (and other
appropriate) places just in case.

Access pops up an error box that says "Could not execute query; could not
find linked table"

I'm using these versions:

Windows XP
http://dev.mysql.com/downloads/connector/odbc/3.51.html

root@daevid111:/lockdown# mysql --version
mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 4.3



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: VIEW not working with myODBC in XP and Access 2003

Daevid Vincent wrote:
> I can import/link any other non-VIEW table.
>
> I've given FULL permissions to my ODBC user in mysql.mysql.user (and other
> appropriate) places just in case.
>
> Access pops up an error box that says "Could not execute query; could not
> find linked table"
>

I've done some *limited* testing of MySQL-5.0.x with Access. From
memory, it ( views, not stored procedures ) worked OK for me. To be
honest, I can't remember whether I linked the view as you do with normal
tables or not. I assume you can select from the view in the 'mysql'
command-line client thing? You can try creating a pass-through query
query and putting in the SQL:

select * from SOME_VIEW;

I uninstalled MySQL-5.0.x a fair while back, so I can't do any further
testing at the moment, but if you can't get the above pass-through
method to work, then you are *really* out of luck.

Also keep in mind that version 3.51.12 of MyODBC has some issues. There
was a link posted a fair while back to a 3.51.12-2 version which seems
to work well for those who tested it. Otherwise I think most people are
waiting on the all-new 5.0 release ( of MyODBC ), which will add support
for MySQL.5.0.x.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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

HTH

John B.

-----Original Message-----
From: Daevid Vincent [mailto:daevid@daevid.com]=20
Sent: Thursday, 27 April 2006 9:18 AM
To: myodbc@lists.mysql.com
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
a
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:
http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html

I can import/link any other non-VIEW table.

I've given FULL permissions to my ODBC user in mysql.mysql.user (and
other
appropriate) places just in case.

Access pops up an error box that says "Could not execute query; could
not
find linked table"

I'm using these versions:

Windows XP
http://dev.mysql.com/downloads/connector/odbc/3.51.html

root@daevid111:/lockdown# mysql --version
mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline
4.3




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: VIEW not working with myODBC in XP and Access 2003

Hi there,

A couple of things to ask that might help locate the problem:

1. Have you made sure that the full-permissions ODBC user has those
permissions from all hosts? For instance, user root@localhost might have
different permissions from root@% (ie root connecting from other machines).
Make sure that you've set the extra permissions from all hosts.

2. What happens if you create a new blank query in Access, then go to
Query-=>SQL Specific=>Pass-through and enter "SELECT * FROM my_view WHERE
1", then view the results (including giving it the correct data source when
it prompts you)? If you actually don't have permission, the query should
tell you...

3. If you're "populating a table" (ie INSERTing data) using this VIEW then
presumably you're just executing an SQL query: INSERT INTO my_table SELECT
my_cols FROM my_view or similar? Are you using ADODB.Connection.Execute SQL
from Visual Basic, or are you running some sort of Access action query?

Al McNicoll
Integritec Ltd



-----Original Message-----
From: Daevid Vincent [mailto:daevid@daevid.com]
Sent: 27 April 2006 00:48
To: myodbc@lists.mysql.com
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 a
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:
http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html

I can import/link any other non-VIEW table.

I've given FULL permissions to my ODBC user in mysql.mysql.user (and other
appropriate) places just in case.

Access pops up an error box that says "Could not execute query; could not
find linked table"

I'm using these versions:

Windows XP
http://dev.mysql.com/downloads/connector/odbc/3.51.html

root@daevid111:/lockdown# mysql --version
mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 4.3





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: VIEW not working with myODBC in XP and Access 2003

Hello,

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 5.0.18-nt
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.

Thanks,
--Randall Price



-----Original Message-----
From: jbonnett@sola.com.au [mailto:jbonnett@sola.com.au]=20
Sent: Wednesday, April 26, 2006 9:52 PM
To: daevid@daevid.com; myodbc@lists.mysql.com
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

HTH

John B.

-----Original Message-----
From: Daevid Vincent [mailto:daevid@daevid.com]=20
Sent: Thursday, 27 April 2006 9:18 AM
To: myodbc@lists.mysql.com
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
a
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:
http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html

I can import/link any other non-VIEW table.

I've given FULL permissions to my ODBC user in mysql.mysql.user (and
other
appropriate) places just in case.

Access pops up an error box that says "Could not execute query; could
not
find linked table"

I'm using these versions:

Windows XP
http://dev.mysql.com/downloads/connector/odbc/3.51.html

root@daevid111:/lockdown# mysql --version
mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline
4.3




--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3DRandall.Price@vt.edu


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: VIEW not working with myODBC in XP and Access 2003

------=_Part_100851_7108174.1147912280822
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I' have encountered the same problem like Daevid Vincent posted.

I'm getting the "Could not execute query; could not find linked table"
message in Access when I'm trying to open a linked-table which pointing to =
a
VIEW in MySQL.

I noticed that I'm getting the above message, if the table in the
select_statement of the VIEW has an alias. Otherwise, it will show up the
records.

But this still introduces a different problem, the records displayed are th=
e
records from the table itself and not the records that you have specified i=
n
your VIEW select_statement.


I hope this sheds some light on this issue.

Thanks

------=_Part_100851_7108174.1147912280822--