Access and SQL

I have an access database that is currently at the 2GB limit that I would
like to convert to SQL. I used DTS to load the database into SQL and created
an .adp project in Access to get at it. The problem occurs when I try and
open the .adp project it kills whatever PC I am on because it loads the
entire 2GB database into memory on the client. The database only has one
table inside it .. but that table contains pictures which constitute the 2GB.
Why does it try and load the entire table into memory, when before I
converted it to SQL it loaded just fine? Any suggestions on improving the
performance?
I suspect this is really an Access issue so you would probably get a better
answer if you post in an Access NG. But do you issue a SELECT with no WHERE
clause anywhere? Do you have Primary Key constraints defined on the table?
Andrew J. Kelly SQL MVP
"Scott T" <ScottT@.discussions.microsoft.com> wrote in message
news:28282103-A602-48A8-AACB-8BCADE1336E2@.microsoft.com...
>I have an access database that is currently at the 2GB limit that I would
> like to convert to SQL. I used DTS to load the database into SQL and
> created
> an .adp project in Access to get at it. The problem occurs when I try and
> open the .adp project it kills whatever PC I am on because it loads the
> entire 2GB database into memory on the client. The database only has one
> table inside it .. but that table contains pictures which constitute the
> 2GB.
> Why does it try and load the entire table into memory, when before I
> converted it to SQL it loaded just fine? Any suggestions on improving the
> performance?
|||Whenever you upsize an Access/Jet application to SQL Server, you have
to be aware of the fact that data which used to reside on the client
computer now consumes both server and network resources. When
constructing your Access front-end, you need to follow the golden rule
of "fetch only needed data". Never do a 'select * from' or open the
table directly in the UI. FWIW, you would be better off storing the
pictures on a network drive and only storing the path to the file in
the database. OTOH, if you had done that to begin with, then you
wouldn't have had a problem with your Jet database exceeding 2GB.
If you don't want to store the pictures on the file system, then in
your application, only load a single picture on demand, not all of
them at once.
--Mary
On Tue, 10 Jan 2006 13:10:02 -0800, "Scott T"
<ScottT@.discussions.microsoft.com> wrote:

>I have an access database that is currently at the 2GB limit that I would
>like to convert to SQL. I used DTS to load the database into SQL and created
>an .adp project in Access to get at it. The problem occurs when I try and
>open the .adp project it kills whatever PC I am on because it loads the
>entire 2GB database into memory on the client. The database only has one
>table inside it .. but that table contains pictures which constitute the 2GB.
>Why does it try and load the entire table into memory, when before I
>converted it to SQL it loaded just fine? Any suggestions on improving the
>performance?