Passing view to local table (1 Viewer)

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Hello,
New guy here. Very comfy with Access apps but just getting into SQL server. I have backend data set up and the App uses linked tables to pass data to local tables, e.g., I'm running local queries to grab data from linked tables to store them in local tables for doing calcs, etc. I'm wondering if using Views would help speed this up. For example, can I use a view to grab the same data and more quickly show the data. Not sure how to pass parameters to the View, and how to post the data to the local table. Also, if I show a View directly in Access, is the data editable?
Thanks for any help!!!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:42
Joined
Jan 20, 2009
Messages
12,849
Why do you pass the data to a local table for calculations?

A view behaves exactly as a table provided it doesn't include aggregates. Editing depends on the permissions.

Views don't support parameters.

One way to make something like a parameterised query on the server available in Access is to use a Pass Through query. Use VBA to edit the SQL Property of the Querydef. (Remember PassThrough queries must use the server SQL syntax.) However this is not usually necessary except for very complex queries because Access will pass the local query to the server if it can be translated via the ODBC layer.
 

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
Just to add to the previous response, views cannot be edited in Access unless they have a primary key. How you link your view will determine whether the PK is carried across to Access
 

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Thanks for the responses!
More detail:

primary table has clients (client name is primary key let's say)
"sub" table 1 has client info (client name + month + information, with client/month = key)
"sub" table 2 has user info (client name + username to know which user has which client)

I have a main form that lists clients associated with a user, and a subform pulling from "sub" table 2. I use master/child so that user can select client on the main form and it shows info related to that client on subform. However, the mainform doesn't pull directly from any tables. Rather, in my attempt to speed up the connection to SQL server, I fill a local table in the front-end with the user's clients. But I just use Access queries to do that (via VBA).

Sounds like a pass through MIGHT speed that up? But can a pass through send data from a remote table to a local table and will that still be faster?

Thanks!
 

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Also, allow me to ask this. I have the subforms looking at the SQL server via ODBC... I'm presuming that's as fast at that aspect will get? With several subforms, it takes a few seconds for the main form to come up but I think that's just b/c I'm looking at so many tables.
 
Last edited:

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Sorry one last question... I think I have a pass-through query working to select data [looks like a very promising route!] ... can I use it to UPDATE data?
 

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Thanks. So I still have the question of using a pass-through query, in VBA, to update a local table.

Actually - let me re-phrase that:
I have a select pass-through query working in VBA... I use a rset = grab a qdf. Can I grab the recordset results of the qdf or rset and using VBA then insert into a local table?
 
Last edited:

Minty

AWF VIP
Local time
Today, 14:42
Joined
Jul 26, 2013
Messages
10,354
You can carry out pretty much any SQL statement / action in a pass-through query.

If it's a complex repetitive task you could set up a stored procedure with parameters passed to it from Access, to do the leg work if required.
 

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Well not having much luck. When I tried to have the SQL insert records into a local table, that didn't work.

I went a simpler route. I have a passthrough query in Access saved. I then run a local query to insert those records into a local table. When I do it manually in Access, it works. When I run the query in VBA, no luck. It just doesn't append the records, with no error.

SOLVED: I had a primary key issue.
 
Last edited:

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
But as a rough rule, looks like pass-through queries work best when reporting back info, versus if I want an updateable data set.
 

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Thanks everyone again. Mostly got things working as I need them.

Trying now to use a pass-through to update the remote table. No reference to a local table. What's wrong with this code?

Code:
Function fnUserSet(pUserName As String, pField As String, pValue As Variant) As Variant

    Dim sql As String, qdf As DAO.QueryDef, rsetLookup As DAO.Recordset
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = gsConnection1
    
    sql = "UPDATE dbo.usystblUsers SET usystblUsers.[" + pField + "] = '" + cstr(pValue) + "' "
    sql = sql + "WHERE dbo.usystblUsers.Username ='" + pUserName + "'"
    qdf.sql = sql
    qdf.Execute <-- I get an error: Cannot execute a select query
    Set qdf = Nothing
    
End Function
 
Last edited:

CedarTree

Registered User.
Local time
Today, 10:42
Joined
Mar 2, 2018
Messages
404
Think I got it... needed to explicitly add "qdf.ReturnRecords = False".
Can I thank myself? :)
 

Users who are viewing this thread

Top Bottom