Question on Updating Access Tables with SQL Table Data

[Scyclonic]

Hello Everyone,



I am at a bit of a loss as to the best way to approach this issue. I have numerous SQL tables that I am running multiple queries off of and manipulating the specific data inside of temporary access tables. The downside of using linked SQL tables is the delay and excessive workload. Something that can be done in SQL takes 3-4 seconds, while in access using the same table and a similar set of queries, is taking upwards into 10+ minutes.

With that said, I have imported in each of the tables that I use, into access. Leaving me with 1 linked table and a corresponding access table as well. (dbo_Respondent and dbo_RespondentA) for example. What I am wondering is, would it be possible to poll the SQL linked tables for updated information and then populate it into the access tables.

Say for example, two weeks have went by and I am about to start running queries. Can I pull just the two weeks worth of data into my access tables through a query?

Any suggestions would be greatly appreciated.

Thanks.
Steven

[lfpm062010]

This is one way to speed up Access using linked tables (SQL SERVER, ORACLE, ....).

Using the "Pass Through" query will speed up your query. It is by passing Access and using the database to do the work.
If you allow Access to do any handling, it will slow down tremendously.

This is a link about "Pass Through" query.
http://www.techonthenet.com/access/t...h/basics01.php

Hope this helps.

[Scyclonic]

Hello again,

I gave this a shot and think it may do the trick, though I am running into an issue. How do I pass a parameter to this type of query.

In Access, I had it prompt the user for a value (I intend to actually make it a value obtained and stored as a temp variable from a form using a macro) that they enter. Using this query, however, I do not think I can do the same thing.

Here is my new query at the moment. It is erroring on the parameter since I havent passed it anything.

==========

SELECT dbo_Respondent.code1 AS VendorID,
Max(dbo_Respondent.RespondentID) AS RespondentID,
Max(dbo_Respondent.DateCreated) AS DateCreated,
Max((dbo_Respondent.Complete+0)) AS Complete,
(Case
When Max(dbo_StatusCode.StatusCodeID)=12 then 12
When Max(dbo_StatusCode.StatusCodeID)=11 then 11
When Max(dbo_StatusCode.StatusCodeID)=10 then 10
When Max(dbo_StatusCode.StatusCodeID)=9 then 9
When Max(dbo_StatusCode.StatusCodeID)=8 then 8
When Max(dbo_StatusCode.StatusCodeID)=6 then 6
When Max(dbo_StatusCode.StatusCodeID)=5 then 5
When Max(dbo_StatusCode.StatusCodeID)=4 then 4
When Max(dbo_StatusCode.StatusCodeID)=3 then 3
When Max(dbo_StatusCode.StatusCodeID)=2 then 2
When Max(dbo_StatusCode.StatusCodeID)=7 then 1
When Max(dbo_StatusCode.StatusCodeID)=1 then 1
End)
AS StatusCodeList,
'Desc',
'DescFull',
Max(dbo_Respondent.Demograph) AS Demograph
INTO tmp_WhyTerm
FROM (dbo_Respondent INNER JOIN dbo_RespondentStatus ON dbo_Respondent.RespondentID = dbo_RespondentStatus.RespondentID) INNER JOIN dbo_StatusCode ON dbo_RespondentStatus.StatusCodeID = dbo_StatusCode.StatusCodeID
WHERE (((dbo_Respondent.StudyID)=@UserStudyID) AND ((dbo_Respondent.StudyWaveID)=@UserStudyWaveID))
GROUP BY dbo_Respondent.code1
ORDER BY Max((dbo_Respondent.Complete+0)) DESC

[lfpm062010]

I can think of 2 ways to do this.

1) Using VBA and and create the query with the value from parameter.
2) Create a table (SQL SERVER table in your case) and store the value and JOIN/LOOKUP/IN the value in the query.

[JoeM]

In the past, I have used a Form where users can enter the parameters they want, and then I have VBA code take some parameters and build the SQL code to apply to the pass-through query. That is the approach I would probably recommend.

[Scyclonic]

Hmm... so instead of saving a query and just running it from a macro. I will need to use VBA code to grab the user entered variable, write SQL, and perform a pass-through query there?

Kind of makes a long chain of queries that do need to run seem a bit rougher as a result.

[JoeM]

Kind of makes a long chain of queries that do need to run seem a bit rougher as a result.

Not sure what you mean there.
The result is one single query. Every query is really just stored SQL code. This just creates the SQL code dynamically "on-the-fly", based on your parameters, using VBA code.

The big difference is you are going to have a much more efficient query written that is going to request just the information you need, instead of what you were doing, and bringing ALL of the information back into Access and querying it there.

[Scyclonic]

What I mean by this is that I have access queries running from 01 through 09C basically, there are multiple queries that I have a macro set up to fire one after another in order to pull the data, massage it and format it the way I need it, and to run multiple filters and inserts.

So for this query specifically, what would I be looking for in terms of setting up the VB code for it?

I am not too familiar with how I'd be able to redesign this through accesses VBA side. I've more or less only used the Access utilities themselves such as Query Builder, etc.

[JoeM]

Here is a link to a question I helped someone out with to show how you can build the SQL code on-the-fly from Form selections: http://www.mrexcel.com/forum/microso...ml#post3099809

The key is if you create a sample of what one of the queries may look like in Query Builder, you can switch to SQL View of that query to see what the SQL code needs to look like. So this is what you will be creating in VBA, and then assigning to a pre-existing query using QueryDefs.

[lfpm062010]

Have you consider to use the second approach? In access, there is more than one way to fix a problem.

Depends on the situation, I choose which way to solve the issue.

[Scyclonic]

So far, playing around with this query (using preloaded variables at the moment), I had it run successfully. The only downside is that it created the temporary table in SQL instead of Access. Is there any way to change that or will I just need to move all my temp tables into SQL when I run these type of queries.

[lfpm062010]

The 2 ways of speed things up as I mentioned in my first reply. There might be others that I am not aware of.

1) To modified your query using VBA to put your parameter(s) in the query and run the query.
2) You use the temp table (on SQL SERVER) and use it as your parameter table.

If you allow access to pass your parameters, then you will have to wait for it to complete.

[Scyclonic]

Yeah, working on moving it to VBA now.

My only issue at the moment is writing the connection to the SQL database. I tried looking at a few examples of the 'process' it uses but am not quite understanding it.

For example, I am passing the two variables I need to the public function.

I have a string value set up with my SQL query that does include my form variables, so I am good on that end. Now I just need help opening the door to the SQL server, running the query, and closing up the connection.

Anyone able to provide me with a small simple example of how to write this connection?

Thanks again for all the help so far

[JoeM]

What I usually do is create a "manual" pass-through query that already has the ODBC connection set-up in the Properties of that query.
So this query then already has all the connection information I need.
I then just use VBA to assign the SQL code I created to this pre-existing pass-through query (using QueryDefs), and run that query.

[Scyclonic]

Hmm... would you be able to provide me with screenshots on how you set that up?

I am not 100% certain that I follow what you are saying here.

So, you have a query that is pass-through (with connection to database) and written out in SQL. Then you have VBA that calls this query in access, and runs it? (If so, how do we pass it the parameters it needs, and inside the query itself, what do we put for these parameters?)