467,225 Members | 1,319 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,225 developers. It's quick & easy.

Upsizing MS Access - lessons learnt - Part 1 (Tables)

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
Nov 12 '05 #1
  • viewed: 1613
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Big Time | last post: by
1 post views Thread by Calum Chisholm | last post: by
2 posts views Thread by Big Time | last post: by
12 posts views Thread by John | last post: by
3 posts views Thread by Devonish | last post: by
reply views Thread by Adict | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.