July 8, 2009 at 2:18 pm
I'm trying to convert a SQL pass through query in access 07 into SQL Server 05. The pass through query comes from a data warehouse, and I want to create the same query in SQL Server 05 and run everything from one location (pull records from data warehouse, delete, create tables, make backup tables, etc. Thanks to the input I've received I have converted delete, update, and make table queries. I'm running into an additional problem with the data warehouse address it is at the end of one of the pass through queries like this:
from "RWGT2200"."MILDAND"."LMHARTVW02" T1
of course I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name "RWGT2200"."MILDAND"."LMHARTVW02" T1'
July 8, 2009 at 2:23 pm
How are you connecting Access 07 to SQL 2005?
Are you using an Access Project (ADP) or are you using linked tables to SQL?
regards graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 8, 2009 at 3:04 pm
Graham,
I connect in Access 07 to a data warehouse using ODBC.
I have linked tables from access db to SQL Server.
I need to be able to copy or recreate the pass through queries since they pull a large number of records from the data warehouse and update tables. I'm trying to do this on the sql server.
July 8, 2009 at 3:04 pm
I thought I would be able to link the pass through query just like you can link the table to sql server.
July 8, 2009 at 3:30 pm
jrw39 (7/8/2009)
Graham,I connect in Access 07 to a data warehouse using ODBC.
I have linked tables from access db to SQL Server.
I need to be able to copy or recreate the pass through queries since they pull a large number of records from the data warehouse and update tables. I'm trying to do this on the sql server.
Ok..the data warehouse tables live on SQL...assume this is SQL 2005 and you have a local Access 07 database in which you have created linked tables to the data warehouse....is this correct?
Have you tried creating an an Access project (adp) to the data warehouse..if so then I believe that you wil be able to create views on the SQL server from within the ADP...the syntax may well be different from that which you are used to in Access...but that can probably be easily sorted.
can you provide an example of the type of query you are trying to run where you are experiencing problems
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2009 at 9:57 am
Here's a sample of the code and the error message is beneath it
select T1."OAD1LDID" as "Load Id",
T1."OAD1SHPID" as "Shipment Id",
T1."OAD1ACCCD" as "Acs Code",
SUM(T1."OAD1CHGAMT") as "AP Est Acs Amt",
SUM(T1."OAD1BILAMT") as "AR Est Acs Amt"
from "RWGT2200"."MILDAND"."LMHARTVW02" T1
group by T1."OAD1LDID",T1."OAD1SHPID",T1."OAD1ACCCD"
order by 1 asc,2 asc,3 asc
Msg 208, Level 16, State 1, Line 1
Invalid object name 'RWGT2200.MILDAND.LMHARTVW02'.
From should be the ODBC connection do you have a sample of the format that SQL Server 2005 uses? I think that would solve my problem.
July 9, 2009 at 1:18 pm
try this
http://www.aspfree.com/c/a/Microsoft-Access/On-Using-Passthrough-Queries-in-MS-Access/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply