[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Largish MySQL DB with MS Access front end.  Pass-through to local table

Posted on 2013-12-10
8
Medium Priority
?
1,093 Views
Last Modified: 2013-12-11
I am using MySQL 5.1.70 and ODBC MySQL driver 5.2a with Access 2010.

I have a relatively large backend database of contacts in MySQL which will be accessed by multiple users.

Local users are restricted in terms of which contacts they are allowed to see (dependent upon what groups they belong to)  so I want to get the MySQL server to return only the relevant records not the entire record set.

I want to copy this data into a local table when the database opens.

So this is how I think it should work:
1.  Create the SQL (I'm happy with this bit)
2.  Create the connection string (also think  this is fine)
3.  Run the SQL to create a local recordset (DAO or ADO not sure it matters a lot).
4.  Append the returned rows into table (not very clear how to achieve this.

If this makes sense then I would value some help with steps 3 and 4 please.
0
Comment
Question by:markremms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 39708855
You could use linked tables, and then simply filter the results in the FE, based on the user's group affiliation. Access form's can do this with their Filter property:

Me.Filter = "GroupID=4"
Me.FilterOn = True

If you would still prefer to use local tables, then:

Are you using linked tables? The simplest way to create a local table is by linking the remote, and then running a query directly against that link table. So if I have a linked table named "tCustomer", and I want to add records to a Local table named "tContacts", I'd do this:

Currentproject.Connection.Execute "INSERT INTO tContacts(Col1, Col2, Col3) (SELECT Col1, Col2, Col3 FROM tCustomer WHERE SomeField=SomeValue)"

If you're not using linked tables, then the only recourse is to open a Recordset to the remote MySQL table, loop through the records, and insert them one-by-one.
0
 

Author Comment

by:markremms
ID: 39708909
I want to avoid linked tables for a couple of reasons:
1.  Linked tables would be slower (I think)
2.  Linked tables give more opportunity for accessing inappropriate data for cleverer users by directly accessing these tables)
0
 

Author Comment

by:markremms
ID: 39708930
Sounds like the process I am talking about would be slower in the first place but then the database would run more quickly.  Is that fair comment?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Expert Comment

by:PatHartman
ID: 39709983
The problem with copying data is that as soon as you copy it, it is OLD and possibly different from what is in the actual database and you would not be able to update the remote table unless you wrote code to do it.

My recommendation is to use linked tables.  Bind your forms to queries that include criteria that limits the rows based on the group of the logged in user.  Forms bound to queries of linked tables work fine.  The key is to not bind your forms directly to tables.  You bind your forms to queries and the queries ALWAYS have criteria that limits the data selected.  So, if the user wants to see cust1 then the query should only bring down cust1 from the server rather than the other 20,000 customers.

Users should NEVER see anything except forms and reports.  They should be given compiled databases - mde or accde and preferably, they should have only the runtime engine rather than the full version of Access.
0
 

Author Comment

by:markremms
ID: 39710049
That's interesting. So a local query based upon a linked table gets executed on the server and therefore only "brings down" the relevant records?  That's is not how I thought it worked at all!
0
 

Author Comment

by:markremms
ID: 39710050
By the way I'm totally with you on the forms/reports only interface.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1600 total points
ID: 39710322
That's is not how I thought it worked at all!
That is a common misconception.  Access makes every effort to "pass through" ALL queries.  You can defeat it though so you need some understanding of what is going on.  Functions can get in the way.  The server doesn't know anything about VBA so user defined functions (UDF) and VBA functions can't be executed on the server.  They must be executed locally so if the function is in the select clause, Access sends the query to the server sans the VBA function.  Then when the resultset comes back, Access applies the function to only the returned rows.  If the function is in the Where clause, Access would send whatever part of the query it could and would apply the function on the result but in this case, Access may have to request huge amounts of data so this is the situation you need to watch for.  If you end up with functions like this, the best solution is to build them on the server in T-SQL and use them as stored procedures.  But ordinary, run of the mill queries really do get executed by the server.

Real pass-through queries have slightly less overhead since Access doesn't even need to think about them.  It just sends the entire query to the server and waits for an answer.  In most cases, the Access query will work just fine and you have the added advantage of being able to bind it to a form and update it via the form.  If you were to use pass-through queries, you would not be able to update them via the form and you can't use them at all as the RecordSource for subforms.
0
 

Author Closing Comment

by:markremms
ID: 39710918
Nice to really learn something from an answer - Pat your explanation is clear and to the point.  Thanks.  Also thanks again LSM for responding so quickly!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question