Access for MySQL data reports only

[JohnMontey]

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.

 

[Banana]

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?

IMHO, this is the best way as there is no way to make a Access file
read-only. An alternative is to not link the table but rather import the
table or use a query that connects to the source 'just in time', but
either approach requires some setup to automate and still does not
change the fact that one could take the connection string and use it in
unintended fashion.

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?

They are live copy, and Access sends updates immediately back to the
source so it's near-instanteous. You do need to refresh records you are
currently looking at but otherwise it also handles refreshing for you as
well.

If you want to know more, there's more info that discuss ODBC at high
level:

tinyurl.com/ODBCGuide

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.

As you've already found, the answers are in forums as well newsgroup
(here). If you've searched and not found a specific answer to your
question or maybe an answer was not quite what you were looking for, do
feel free to post your question. As long it's specific questions we
certainly can help.

 

[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.

 

[Armen Stein]

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.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[JohnMontey]

Thanks Duane and Banana. You two were very helpful. I'm quite impressed with
this discussion group.

 

[David W. Fenton]

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.

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.