List:MySQL ODBC« Previous MessageNext Message »
From:Al McNicoll Date:April 27 2006 8:41am
Subject:RE: VIEW not working with myODBC in XP and Access 2003
View as plain text  
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@stripped] 
Sent: 27 April 2006 00:48
To: myodbc@stripped
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




Thread
VIEW not working with myODBC in XP and Access 2003Daevid Vincent27 Apr
Re: VIEW not working with myODBC in XP and Access 2003Daniel Kasak27 Apr
RE: VIEW not working with myODBC in XP and Access 2003jbonnett27 Apr
RE: VIEW not working with myODBC in XP and Access 2003Al McNicoll27 Apr
RE: VIEW not working with myODBC in XP and Access 2003Randall Price27 Apr