Migrating from Access to SQL Server with a web front end

Afternoon all,

Apologies for cross-posting but as my query covers both Access and SQL
Server I thought I'd send it both!

I have inherited a project to migrate a fairly complex series of
Access databases into a single proper SQL database with a web front
end.

Its quite a nasty job as people are working on a variety of data sets
at several Universities around the world and the data has got very
messy; hence the requirement to put it all on one live web enabled
database server and provide a web-based front end (particularly as
some users insist on using Macs so can't run Access as a front end
anyway).

If anyone could give me hints on how to perform such a migration or if
anyone knows of any good books or other documents on this I'd be
grateful for assistance.

Many thanks

Rich May
Museum of LondonFirst off, congratulations - this is a great move to make.

As far as converting the tables, use DTS (Data Transformation Services) in
sql server. It is pretty much wizard driven and works really well. As for
the queries, you would do best to forget you even have them in Access and
rewrite everything using Stored Procedures. My favorite book for "stepping"
between access and sql server is "Microsoft Access Developer's Guide to SQL
Server" by Chipman and Baron. It's wonderful.

As for your web front end... that is where the headache starts. You can
pretty much choose two paths if you want to stay on the Microsoft path. You
can do it using ASP or ASP.NET. Both will run on IIS (.net needs framework
installed).

If you are serving to Macs, you may deliver your app better using ASP
because you will be able to better control the rendered output (very good
for Netscape). I use Visual Basic to build "ActiveX DLL" components to
alleviate the complexity of the ASP pages so you would want to be proficient
in Visual Basic and instantiating COM components. Next, you need to take
ADO (ActiveX Data Objects) head on and learn it well, that is what you will
use to connect to sql, execute queries, and work with data.

If you are using IE on all clients and have control over them, you will find
that Visual Studio.NET does a great job at deploying web applications in
ease and speed, once you pass the high learning curve. I VERY highly
recommend "Programming Microsoft Visual Basic .Net" by Francesco Balena
(1600 pages).

If it all sounds complex, don't get me wrong... it is. If you need help,
let me know, we do this for a living.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||several choices, depending on what you're comfy with ..
1) sql server, IIS & ASP
2) mySQL, apache, php

here's a RAD tool that I found the other day to create web pages for
either option (I haven't tried it yet)
http://www.citybusinesslogic.com/apwab.php

and somewhere I've got vba code to convert ms-access tables to mysql
format

rmay@.museumoflondon.org.uk (Rich May) wrote in message news:<e12c70e0.0311140943.722ed0ba@.posting.google.com>...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||Hi Rich,

I think there are 3 options. a) Access' Upsizing Migration Wizard
b)Sql Server's DTS and c) setting Access as a linked server and
programmatically importing the data. I believe the main problem
you'll run into is that Access' datatypes are not 100% compatible with
Sql server's. - Louis|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311142220.41dff853@.posting.google.com>...
> Hi Rich,
> I think there are 3 options. a) Access' Upsizing Migration Wizard
> b)Sql Server's DTS and c) setting Access as a linked server and
> programmatically importing the data. I believe the main problem
> you'll run into is that Access' datatypes are not 100% compatible with
> Sql server's. - Louis

Another problem I have seen with the upsize wizard is it makes all
columns with the type nvarchar i.e. unicode which doubles the space
requirements.

Duncan|||"duncan" wrote

> Another problem I have seen with
> the upsize wizard is it makes all
> columns with the type nvarchar i.e.
> unicode which doubles the space
> requirements.

First, I don't think that is true -- all _text_ columns, perhaps, but
certainly not numeric and date/time columns.

Secondly, were you under the impression that Access does NOT use unicode? It
has since Access 2000. There is an option for "unicode compression" to
reduce the impact; I don't know if there is such an option in SQL Server,
but would think that it would have a similar feature.

Larry Linson
Microsoft Access MVP|||Is Cold Fusion a viable alternative to ASP in this kind of situation?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In Ms SQL...

Char is fixed length and pads with spaces
NChar is fixed length and pads with spaces, plus is unicode
VarChar is variable character (with max length spec)
NVarChar is variable (maxlength), plus is unicode

Since I don't deploy to any other language and don't plan to, I never use
the N' datatypes.

Also in SQL, the "SmallDateTime" field type is 4 bytes and the "DateTime" is
8 bytes). If it's 4 bytes in Access, then the equiv is "SmallDateTime".

In SQL Numeric DT's, there is tinyint, smallint, int, real, bigint, float,
money, smallmoney, and decimal. All with their mix of capacity.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Larry Linson" <bouncer@.localhost.not> wrote in message
news:bpytb.58064$p9.53071@.nwrddc02.gnilink.net...
> "duncan" wrote
> > Another problem I have seen with
> > the upsize wizard is it makes all
> > columns with the type nvarchar i.e.
> > unicode which doubles the space
> > requirements.
> First, I don't think that is true -- all _text_ columns, perhaps, but
> certainly not numeric and date/time columns.
> Secondly, were you under the impression that Access does NOT use unicode?
It
> has since Access 2000. There is an option for "unicode compression" to
> reduce the impact; I don't know if there is such an option in SQL Server,
> but would think that it would have a similar feature.
> Larry Linson
> Microsoft Access MVP|||Sure, I looked into it some time ago when I was a beginner, but then I
discovered the unimaginable amount of documentation and books from multiple
authors in Microsoft products and I didn't even give it a second thought.
To me, no product is satisfactory if I have to spend days looking for
answers, and especially when they aren't there to be found.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"John Callaghan" <jackit2@.yahoo.com> wrote in message
news:3fb88be3$0$194$75868355@.news.frii.net...
> Is Cold Fusion a viable alternative to ASP in this kind of situation?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||In my experience, the data conversion part of a project like this is almost
trivial compared to the front-end application challenge.

Access forms and reports can be incredibly complex, with subforms, simple
data binding, no-code dynamic behavior (record navigation, filtering,
sorting, finding, column-reordering, all those are built in to Access, not
at all in asp), and the list goes on and on. If your Access front-end
application is complex, it could be impossibly time-consuming to do in asp,
slightly less so in asp.net.

You would need some other tool for reporting (Crystal or others), I've made
crude html reports in asp and it is nearly impossible to do even the easy
stuff Access reports can do.

As a example, I wrote a full maintenance application for a 30 table database
(with lots of RI between tables), with multiple subforms, dropdown
navigation, and lots of other features, all in one day of work.. ASP would
have taken weeks to do even close to the same functionality.

So overall, my suggestion is if the Access forms are complex, plan for 1000
to 2000% more development time over Access forms. You will definitely need a
better reporting tool than html/asp. Hope this helps,

Steve Nyberg

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of Londonsql

Migrating from Access to SQL

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to

also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)

and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".

But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )

This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report

SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.

-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."

SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]

Incidently i'm not seeing a need for the inner join above...|||Quick guess :

SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :

Are you running these queries in MSSQL or MSAccess ?|||Found my error :

SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table

sometimes it is just those rows which have a match that you are interested in

example: say you are the HR department and you have an employees table

suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)

not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)

now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address

what does your query look like?

that's right, an inner join, selecting only from the left table

:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Middle Search Support

Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:

WHERE lastName like '%smith%'

It is desired that each of these names be returned:

Smith
Smithson
Nesmith

What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:

(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th

The where now becomes

WHERE lastNameSub like 'smith%'

This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?

Dave

Hello Dave,

I'm afraid you can't within the core engine alone. Obviously a good indexing strategy will help you, but if we're talking about improving the substring/like search on a character column, apart from indexing, you don't have many options.

As you've discovered, using 'smith%' is much faster then '%smith%' as the optimiser has the opportunity to perform an index scan in the first instance - it does not in the second.

If you are performing a lot of these types of searches, I would look at full-text search:

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.sql-server-performance.com/tb_search_optimization.asp

Cheers,

Rob