Re: Access using SQL Server only DB

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 10/19/04 

Date: Tue, 19 Oct 2004 18:22:37 -0400

I recommend you re-write that report to use a SQL Pass Through Query so you
can do the join on the server and then get back a read only result set. This
is one of the optimizations I was referring to.

Try not to join linked tables in your local queries. Always try to send them
to SQL Server as SPT queries instead.
This way Access does not try to download huge amounts of data to resolve the
join. 

-- 
Joe Fallon
Access MVP
"folke" <folke@discussions.microsoft.com> wrote in message 
news:4617FBDF-CBAA-4563-A44D-76EB92A99B64@microsoft.com...
> Thanks Joe,
> that was very helpful. It all works except for one report. Unfortunately 
> the
> only thing I see when I run it is "An error Occured". Is there a log
> somewhere? I'm a bit suspicious because the report is getting info from 
> two
> tables that have a relation where the parent and child table are related 
> via
> a column that is NOT Primary Key in the parent. As you know there is no 
> way
> of creating such a relationship i SQL Server!
>
> "Joe Fallon" wrote:
>
>> I recommend you do NOT use an .adp.
>>
>> Use the same .mdb you started with.
>> Make a copy of it.
>>
>> Now check that all the data is in SQL Server.
>> Then delete all local Access tables (that are now in SQL Server.)
>>
>> Now link to SQL Server and link to the tables you just moved there.
>> If you do the link manually you have to remove the dbo_ prefix from each
>> table name so that they are named the exact same way as when they were 
>> local
>> Access tables.
>>
>> Now your app should work.
>> All queries and forms and reports and code will use the linked tables the
>> same way they used the original ones.
>>
>> Then you need to optimize your app so it works better with SQL Server.
>> (You can do this 1 step at a time.)
>> But at least now you should have a functioning app.
>>
>> BTW - even MS recommends linked tables over .adp projects.
>> -- 
>> Joe Fallon
>> Access MVP
>>
>>
>>
>> "folke" <folke@discussions.microsoft.com> wrote in message
>> news:F13C6677-DAF6-4A62-BAEA-3CD7D1D6BAA4@microsoft.com...
>> >I have converted a project from Access DB to use SQL Sever DB. But I 
>> >still
>> > have the .mdb tables left as TAB1_local. How can I convert from Access 
>> > to
>> > an
>> > .adp project using ONLY SQL server tables. I tried but then the forms 
>> > do
>> > not
>> > work! Just saying "An Error Occurred" when I activate the form(s). 
>> > Maybe
>> > it
>> > is ok to have the local tables left but they disturb me - I hate them 
>> > when
>> > I
>> > see them!
>> > -- 
>> > Folke
>>
>>
>>