DataBase - MS SQL Express 2008

Asked By Peter

21-Apr-10 01:21 PM

Hi all,
in order to increase performance of my Access 2007 application i choose to
split the backend to a SQl Express 2008 Database engine. However..this seems
not to function..i receive an error message saying - The Upsizing Wizard does
not work with the version of Microsoft SQL server to which your project is
connected..I cannot find anything related to this issue on the Microsoft
Update Website..

Any suggestions?

Thanks!

22-Apr-10 09:00 AM

I am not sure, but I doubt Access 2007 can control SQL 2008. Access has never
worked with SQL versions that came out later than the Access version. You
could:

1. Use SQL Express 2005 and then update the db to SQL Express 2008 after the
conversion.
2. Use SQL Server tools to import the Access db, except I am not sure if the
tools are included in the SQL Express edition. You can buy the SQL 2008
developer edition for about $50 which includes all tools, or maybe a trial
version would work if it is a one-time conversion.
3. You could manually create the db structure in SQL Server and then import
the data.

But if the only reason you are moving the backend is to improve performance,
you may be disappointed. Access is very fast running on a stable LAN with a
reasonable number of users. SQL Server offers less corruption, better
security, more robust recovery, effective WAN access, and enhanced data
engine features. But performance of equally well-designed structures may not
change unless you have particular performance issues now.

If your current Access db is not yet split, you might want to first split it
into an Access backend and see how performance goes. Assuming you have been
running an unsplit db from your local drive, and you split the backend onto
a network drive, you may lose a little performance, but it should not be
much for most applications.

22-Apr-10 09:13 AM

hi Peter,

hmm, I have not tried the Access 2007 Upsizing Wizard, but the 2003
version works.

You can use instead of the wizard the SSMA tool:


http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en


mfG
--> stefan <--

22-Apr-10 10:34 AM

Access 2007 works best with SQL 2005, and there is an express edition
of that (with free SSMS Express)





o
ems
does
s

22-Apr-10 05:32 PM

Thank you all for some very usefull suggestions. The current Access 2007 is
split and we are 10 users. The performance issue is most related to WAN
connection...i iwll see what i can do with all your kind information..once
again..Thanks You!

23-Apr-10 12:29 AM

But, once you are past the upsizing issue it will work very well with any other
version of SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

23-Apr-10 12:30 AM

it is my understsnding this tool does a better job than tthe built-in wizard.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

23-Apr-10 07:17 AM

If your purpose is supporting WAN users, you should definitely consider
changing form record sources from something like:
Select * From MyTable
to retrieving a single row at a time as requested by the user. Unless the
row count is pretty small, retrieving all rows becomes a serious time delay
on a WAN.

I use a record source like this so the form opens with the appropriate
metadata but no actual data:
Select * From MyTable Where 1=0

Most forms have some kind of control(s) for a user to select a row to be
viewed. Instead of finding a row in the form's existing recordset based on
those conditions, you change the form's record source to specify those
conditions.

24-Apr-10 11:09 AM

wow only a retard would run jet over a WAN.

ADP runs soooooooooooo much better, OMG





ay
n
7
nce
You
er
f
8
ith
a
ay
lit
een
be
ose
is

24-Apr-10 11:11 AM

moving to ADP is what you need to do.. Jet just is not reliable enough
for multiple users

and 3 additional layers of BS is not necessary

File, New, Project (new data)
or
File, Connection, Servername



o
ems
does
s