Access 2003 Migration ---> SQL2K

[SQLNewBee]

Would love it if someone with a few years experince would point me in the
right direction.
MY IT staff and I have developed a complex MS Access database with a robust
Front End. Howvever,
inherent in MS Access's limitations, the application over the LAN has begun
to slow down significantly because we use cmplex queries and a
lot of controls on our forms.
We are looking to migrate the back end to MS SQL Server. Ive gotten past
the basic tables replication.
My Question is this: Once the tables have been imported into SQL, what
would be the next and best logical progression of
Migration. Is there any resource or refernce out there that outlines each
phase or layer of migration.
best practices, things to watch out for, that sort of thing. Im going into
this blindly and could use all the help I can get.

 

[Tony Toews]

My Question is this: Once the tables have been imported into SQL, what
would be the next and best logical progression of

The next thing would be to migrate the queries over as views and
stored procedures.

One tip, although I haven't done this myself, is to use ADPs to work
with views as ADPs will store the table layout in query view which
Enterprise Mgr doesn't.

Migration. Is there any resource or refernce out there that outlines each
phase or layer of migration.
best practices, things to watch out for, that sort of thing. Im going into
this blindly and could use all the help I can get.

Now I never did complete that migration due to a PHB but everything I
came across is at my Random Thoughts on SQL Server Upsizing from
Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

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

 

[SQLNewBee]

Thanks Tony

my thought were mst definetly along those lines. One more question for you.
My programmer has some very complex forms. Many of the field on these forms
have queries nested within to populate the combo or liust box which is then
stored in other tables as the form is closed. How best can I handle these
types of forms. Similarly, the databse is mainly used to cost our product;
commodity based pricing - the calculations are a nightmare and slows forms
tremendously. How have you handled forms in the past?

 

[Tony Toews]

my thought were mst definetly along those lines. One more question for you.
My programmer has some very complex forms. Many of the field on these forms
have queries nested within to populate the combo or liust box which is then
stored in other tables as the form is closed.

I'm not at all sure what you mean by "stored in other tables as the
form is closed." Are you adding items to combo boxes during data
entry without saving the data to tables?

How best can I handle these types of forms.

No idea yet. But I'd also say see what happens. If performance is
the same as before then don't touch it for now. If performance sucks
then you have to take a look at it.

Similarly, the databse is mainly used to cost our product;
commodity based pricing - the calculations are a nightmare and slows forms
tremendously. How have you handled forms in the past?

It's quite possible that this kind of logic could be put into T-SQL so
that the calcs reside on the server. Also SQL Server 2005 supports
..Net so you might be able to move VBA function logic up to the server.
However how efficient this would be compared to T-SQL I have no idea.

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 Toews]

The next thing would be to migrate the queries over as views and
stored procedures.

BTW I used some code to move most of my queries to SQL Server as
views. Except those that had embedded functions which failed. The
code was much more complex than the following as I kept track of which
queries converted fine and which didn't. Given that I had stacked
queries I had to convert the lower down queries first and move up.

strNewSQL = adhReplace(Q.SQL, vbCrLf, " ")
strNewSQL = Left(strNewSQL, InStr(strNewSQL, ";") - 1)
strNewSQL = ConvertTrueFalseTo10(strNewSQL)
Set myquerydef = dbsPermanent.CreateQueryDef("")
'Q.Name & " DAO Test")
myquerydef.ReturnsRecords = False
myquerydef.Connect = strConnect
myquerydef.SQL = "CREATE VIEW [" & strQueryName & "]
AS " & strNewSQL
myquerydef.Execute
myquerydef.Close

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