By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.