From: JohnMontey on 4 Jan 2010 21:35
Hi. I'm a new Access user without much knowledge of VBA. My company has asked
me to use Access to make sales reports from our MySQL database. So far I have
successfully connected to the MySQL database via an ODBC connection and
linked up the tables. I've done lots of research on using Access as a MySQL
front-end, but I'm looking for clarification on a couple of things.
1. Can I make the Access database read-only without programming?
Since we don't want to overwrite MySQL data, we're creating another MySQL
user account with read-only privileges. Is this a good way to go, or is there
a better (and non-programmatic) way to protect the MySQL data from user error?
2. Exactly how do linked tables work?
It's not clear to me if the linked tables are copies of the MySQL data, or
if they are live links that are showing me the MySQL data in real-time. Would
the Access database need to be refreshed to show new MySQL data?
3. Additional resources for using Access front-end/MySQL back-end?
Most of the information I've found are in forums. The MySQL website offered
some limited documentation, and the Microsoft documentation focused mostly on
SQL Server. Please let me know if you know of any other resources.
Thank you for your time.
From: Armen Stein on 5 Jan 2010 09:36
On Mon, 4 Jan 2010 19:45:02 -0800, Duane Hookom
>In addition to Banana's comments, I would use pass-through queries to
>retrieve data. These would perform much better and are always read-only. I
>generally use a little DAO code to change the SQL property of the saved P-T
>queries to apply a filter for a report.
>You might want to search the web for DSN less connection strings to MySQL.
>This would make distribution of your application a bit easier.
In addition to Banana and Duane's comments:
I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at our J Street Downloads page:
http://ow.ly/M2WI. It includes some thoughts on when to use SQL
Server, performance and security considerations, concurrency
approaches, and techniques to help everything run smoothly.
Although it was written for SQL Server databases, many of the
principles mentioned (like passthrough queries) will apply to other
back-end servers like MySQL.
Microsoft Access MVP
From: David W. Fenton on 5 Jan 2010 22:05
=?Utf-8?B?RHVhbmUgSG9va29t?= <duanehookom(a)NO_SPAMhotmail.com> wrote
> In addition to Banana's comments, I would use pass-through queries
> to retrieve data. These would perform much better and are always
If you're going to use passthroughs, why not just use regular
queries set to open a snapshot, which is read-only? I see no reason
why one would be preferable to other except in the type of case
where'd you be forced to use a passthrough in the first place.
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/