Solved

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

Posted on 2013-12-10
8
1,033 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 84

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
 
LVL 34

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 34

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now