Need direction with SQL statement to access SQL Server tables

[thevillageinn]

My head is spinning with ideas, but I'm having a hard time putting any of them together.

My DB is something like this (acc97):
4 tables - 2 are SQL Server tables, 1 is a local table, and the last is the table I want to insert the data into (also local).

I have a connection string for the SQL Server database, but I'm having trouble figuring out how to reference those two tables in the SQL statement and insert the data into my local table.

Currently, the SQL Server tables are linked via ODBC DSN, but that has to go.

My tables are as follows:
dbo_Customer_File_Copy - SQL Server table
dbo_User_Id_File_Copy - SQL Server table
112999 - local table
Cdr_UserCostMoreThan50 - intended destination

And now for my SQL from the query builder...

SELECT dbo_Customer_File_Copy.CustNo, dbo_Customer_File_Copy.Nam, Count([112999].BillingNo) AS NumberOfCalls, [112999].BillingNo AS UserId, Sum((Val([AccessCost])/10000)+(Val([OrigTrnspCost])/10000)+(Val([TermTrnspCost])/10000)+(Val([EgCost])/10000)) AS Cost, dbo_Customer_File_Copy.ServDat, dbo_Customer_File_Copy.LstStmntDat, dbo_Customer_File_Copy.LstStmntAmt, dbo_Customer_File_Copy.Bal INTO Cdr_UserCostMoreThan50
FROM 112999 INNER JOIN (dbo_Customer_File_Copy INNER JOIN dbo_User_Id_File_Copy ON dbo_Customer_File_Copy.CustNo = dbo_User_Id_File_Copy.CustNo) ON [112999].BillingNo = dbo_User_Id_File_Copy.UserId
GROUP BY dbo_Customer_File_Copy.CustNo, dbo_Customer_File_Copy.Nam, [112999].BillingNo, dbo_Customer_File_Copy.ServDat, dbo_Customer_File_Copy.LstStmntDat, dbo_Customer_File_Copy.LstStmntAmt, dbo_Customer_File_Copy.Bal
HAVING (((Sum((Val([AccessCost])/10000)+(Val([OrigTrnspCost])/10000)+(Val([TermTrnspCost])/10000)+(Val([EgCost])/10000)))>50));

I really appreciate anyone sticking with me through that SQL Statement. I don't have much experience with how to parse or split the statement into something Access will like.

Many thanks in advance
-Dan

 

[cmmrfrds]

The sql statement is too detailed for my taste - my eyes are tired, but I can comment on the server versus local issue. This is probably one case where the easiest solution is to continue with linking the SQL Server tables, that way you can join them in your app. As far as I know, you cannot join an SQL Server table to a local Access table through ADO. The solution from an ADO standpoint is to make the Access tables linked servers in SQL Server. That way you can tell SQL Server to join the Access tables with the SQL Server tables and return the resultset.

Say the table and server names are
bigtuna sqltab1
bigtuna sqltab2
accessServer table1
accessServer table2
the database in bigtuna is mydb

The table naming convention in the sql would be

Select * from bigtuna.mydb.dbo.sqltab1 a, accessServer...table1 b where a.key = b.key

I have a SQL Server in my office so I have used Access as well as Excel as linked servers in SQL Server. If you don't have control over your SQL Server this may not be available to you, but a DBA could set it up.

 

[tlbroadbent]

I'm not sure what your question is exactly. Are you having some specific problem or error occur? Are you interested in making this query faster? Do you just want to know if your design is okay?

As cmmrfrds mentioned you can't create Joins unless the SQL Server data is available in the Access database via a linked table or a pass-through query. As the query only selects from the SQL data, I recommend a pass-through query rather than linked tables.

Further, I recommend that the pass-through query select from a View in SQL. The View should Join the two SQL tables and only select the columns that will be required in the Access query. In addition, any summarization that can be done on the SQL tables should be done in the View. If SQL Server does more processing Access will need to do less. There will be less network traffic. The process will run many times faster - guaranteed. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[thevillageinn]

thank you both for your responses.

I guess I am trying to find out what is the best way to take that Access Query and turn it into something that I can run without having to have the SQL Tables linked via ODBC. But since I guess I can't do that, I will either try to figure out the pass-through query, or just deal.

Yes, the query is very slow. It examines somewhere fairly close to a million records in the access local table, and somewhere near 200k in the SQL tables. The calculations (sums) are made with data from the local table, and I'm not even sure whether I can do that with SQL via VBA.

Anyway, I will see what I can learn about pass-through queries, and probably posting more questions on this whole process later.

Thanks again.
-Dan

 

[cmmrfrds]

Don't give up yet there are alternatives. The key question is, do you need to join the LOCAL access table to an SQL SERVER table? If so, then look at an alternatives to the linked tables in Access. A couple of ways to speed this up significantly, one is to create a temporary table in SQL Server, another is to use the access mdb as a linked server on the sql server side. Maybe somebody else has more alternatives.

The temporary table. If the values for the joined keys are fairly spare in the local table then use the temporary table. For example, if you only have 5000 values in the 200000 row local table, then create a temporary table of these 5000 values and join on then in an sql server stored procedure by building a temporary table of the values. You can do this with ADO as long as you keep the connection open. This is your best option since there in no need for intervention on the sql server side.

As I mentioned in the previous post, access table as linked server. On sql server and in Enterprise Manager there is a Security tab that allows you to link access as a server to sql server. This would be the fastest for you as all the work would be done on the sql server to join and process the data.

 

[thevillageinn]

I could also do this in phases...?

phase 1 - do the sum calculations into the access table

phase 2 - bring in relevant data from the "user id" table

phase 3 - bring in related records from the "customer" table.

I think I will just jump in and see what I can do.

My biggest question now is how do I reference the SQL server tables in the SQL statement? Do I just set a variable = to the SQL connection I make? ie. if I use the variable name 'cnFileCopies' can I just use that name in the SQL Statement?

"Select * From cnFileCopies Where CustNo Like [accessLocalTable].[CustNo]"

Something like that? (My syntax is probably not 100% but I could get there eventually)

Thanks again everyone, for your assistance. I really appreciate it.
-Dan