Hi there
I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K.
Wish to provide some knowledge gained back to community - hopefully
help others.
1.Using Upsizing wizard - will either work or not. For me it did not.
Why ?
1.Bad data (e.g. classic "01-01-200")
2.Permissions
3.other reason
Approach Used
1.DTS
Tips
A.Plan what you are doing e.g. documentation
e.g. List of tables, no of records in each table add further column
for each table combined of all data in that table(all rows)(if you
wish)
used sql query
SELECT DISTINCTROW Msysobjects.Type, Msysobjects.Name,
Msysobjects.Database, '' AS Completed
FROM Msysobjects
WHERE (((Msysobjects.Type)=1 Or (Msysobjects.Type)=6) AND
((Msysobjects.Name) Not Like "m*"))
ORDER BY Msysobjects.Name;
B.Go threw your list, tick off tables upsized ok - ones did not, leave
to end.
C.Ones failed - error message not helpful - check table data in access
- usually main cause
D.Do not assume - if table upsized everything ok
i.Check data size e.g.
a.no of records
b.manual check of data (open table see data)(simple check)
c.can compare actual data (last column added within access via table
upsized in sql - need to use "linked servers" see under security
folder - use queries compare difference - (more complex) - if many
records to check
d.if few bad data - quicker to change actual data by hand or scripting
- depends on data rejected
e.Check the data type for text fields - within sql e.g. varchar +
nvarchar - need to change default
Key :- Document every stage - you may have to do it again !!
Next
Add your links between tables
See above query - helpful, as it includes paths
Next
Add your triggers
Summary :-
1.Good documentation
2.Planning
