Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-12-10
8
Medium Priority
?
1,100 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

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 40

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

580 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