Solved

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

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

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 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 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