Set subdatasheet name to none when upsizing to SQL?

M

Maurice

Hi, any thoughts on this one appreciated...

Situation:

Access 2007 front_end - linked tables to SQL 2005 on Vista SP1 client.
Performance is dramatic so i'm investigating all possibilities here.

All proposed options in other threads are applied. Installed every hotfix
available. When I look at the table design I see that subdatasheet name is
set to [auto]. So to be sure that this would not be a bottleneck I tried
setting this to [none]. You've guessed it not possible in a linked table. So
I did the following:

I created a new empty database. From the old database I created a maketable
query from the linked table and ran it to create a table in the newly created
db. All goes well so far. In the new database I set the subdatasheet name
option to [none]. Save the table, check it to see if it actually saved it,
correct is saved as [none]. So at that point I decide to upsize only this
table to see what happens. Upsize went well and when I open the table and
look at the subdatasheet name property it is set back to [auto]. So on one
hand we try to apply as much tips as we can and on the other hand this
property is set back to [auto].

So question is... can this be set to [none] once upsized or am I stuck with
this perfomance issue?

Thanks for your time
 
T

Tony Toews [MVP]

Maurice said:
Access 2007 front_end - linked tables to SQL 2005 on Vista SP1 client.
Performance is dramatic so i'm investigating all possibilities here.
Click to expand...

What do you mean by performance is dramatic? Presumably you mean
poor. What have you measured? Have you setup views on the SQL
Server box? Does the SQL Server box have sufficient RAM?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
M

Maurice

Crystal,

I thought I had taken care of that one, but i'll check again just to be
sure. Thanks for the pointer.
--
Maurice Ausum


strive4peace said:
Hi Maurice,

in order to get changes of setting SubDatasheet to [None] to "stick",
you need to turn off AutoCorrect

Failures caused by Name AutoCorrect
http://allenbrowne.com/bug-03.html


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*



Hi, any thoughts on this one appreciated...

Situation:

Access 2007 front_end - linked tables to SQL 2005 on Vista SP1 client.
Performance is dramatic so i'm investigating all possibilities here.

All proposed options in other threads are applied. Installed every hotfix
available. When I look at the table design I see that subdatasheet name is
set to [auto]. So to be sure that this would not be a bottleneck I tried
setting this to [none]. You've guessed it not possible in a linked table. So
I did the following:

I created a new empty database. From the old database I created a maketable
query from the linked table and ran it to create a table in the newly created
db. All goes well so far. In the new database I set the subdatasheet name
option to [none]. Save the table, check it to see if it actually saved it,
correct is saved as [none]. So at that point I decide to upsize only this
table to see what happens. Upsize went well and when I open the table and
look at the subdatasheet name property it is set back to [auto]. So on one
hand we try to apply as much tips as we can and on the other hand this
property is set back to [auto].

So question is... can this be set to [none] once upsized or am I stuck with
this perfomance issue?

Thanks for your time
Click to expand...
Click to expand...
 
M

Maurice

Hi Tony,

I'm converting a 97 db to 2007 for a client of mine. He just wants to
convert it and no tuning. This has purely to do with speed of migration. I
told him the database is not well setup but taking it up from the ground the
way it should be would cost him a fortune. This is a ypical db that has been
around for years and does what it should do work as expected. There are
several views, sp's from serverside. Strange thing is that when we put our
laptops next to each other and he's running his version 97-SQL2000 and I'm
running mine 2007-SQL2005Express when opening his form with data takes him
about one second where as mine takes about 10 seconds. I have to say there is
a ridiculous amount of VBA code behind this db which makes it hard to read.
What strikes me is that the 'old' combination works very fast and my
combination takes up a lot of time. So that's where i'm looking for what
could be troubling this combo.
 
D

David W. Fenton

he's running his version 97-SQL2000 and I'm
running mine 2007-SQL2005Express when opening his form with data
takes him about one second where as mine takes about 10 seconds.
Click to expand...

What happens with A97 to SQL 2005? Or A2K3 to SQL 2005?

Access 2007 is widely reported to be substantially slower than
earlier versions, so it might be a good idea to figure out whether
the problem is with Access or with the new version of SQL Server.

Another issue might be whether you're using ACCDB for the front end
or MDB. Dunno exactly how that might play into the mix, but MDB will
be processed with the older version of Jet, which might be better
optimized than the new one, i.e., ACE.
 
M

Maurice

Well David you might just have made a good point there. I never thought of
keeping the original mdb and just use the compatibility option there. I'll
definitely give that one a try and see if that makes any difference.

Thanks for the tip ;-)
 
H

Howard Burgman

Maurice said:
Well David you might just have made a good point there. I never thought of
keeping the original mdb and just use the compatibility option there. I'll
definitely give that one a try and see if that makes any difference.

Thanks for the tip ;-)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word VBA - Want to test if Full Name is NOT Present in document 0
Name of file opened, being viewed 2
unwanted subdatasheet in form datasheet view 0
Subdatasheet Name keeps resetting to [Auto] 6
Trying to create a report that will list unmatched records by Ticket Primary Key 0
"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
Object dependencies vs. relationships 4
Append Query Include Subdatasheet 2

Top