Solved

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

Posted on 2013-12-10
8
1,074 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 100 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 38

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 38

Accepted Solution

by:
PatHartman earned 400 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

717 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