tool to re-reference forms after usizing?

[Jon]

Hi

I am in the process of upsizing an access database to sql server.

The reason for this post is because after upsizing i would like to rename
columns, tables and queries in the sql database as originally the field names
were put together with no logic at all and are an absolute mess. I understand
that i would have to then change all the queries etc.. to show the new names.

What i would like to aviod if possible is having to go into each form,
report, marco and re-select the fields. Are there any mapping tools out there
which can do this for me? dont mind buying something if there is.

also, is it possible to produce an extract of report/query names from an
access database?

Thanks,

Jon

 

[Alex White MCDBA MCSE]

Hi Jon,

Although I have never tried this it should work, upsize your database with
the original field names, then make sure 'auto correct' is on in the
options, then changes a couple of the field names and see if the field names
change in the queries that are linked to that recordset, there are quite a
few problems with 'auto correct' so you may have to compact and repair
after, then switch 'auto correct' off, that is the quick and easy way (never
tried it myself), the 'proper' way is to completly review your table
structures, probably a bit of normalising etc, create the new structure and
dependant on how many databases need to be imported either manually import
each one or write an import routine that maps the old fields to the new
fields. The rule of thumb that I use is importing the data/converting is
10-15% of the work, other 85-90% being repointing queries/forms/modules etc.
to the new structures.

What is the reason for the upgrade to SQL?

 

[Douglas J. Steele]

While the AutoCorrect feature may do it, there are also 3rd party products
that are known to work.

Three that I'm aware of are
Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/

 

[Jon]

Hi there,

thanks for those, dont suppose you have tried any and which is the most
accurate?

The reason we are upgrading is the database was originally built in access
97 about 8 years ago for 4-5 users. since then it has grown and grown and
bits have been bolted on and now it about 30-40 users accessing on a daily
basis. unsurprisingly access is a bit unhappy with this and we may be taking
on a few more users. Now we need to get it on a more stable platform which
runs quicker and upsizing to sql seemed the logical choice as we support a
few third party systems running sql server as well.

trouble is its proving to be a bit tricky and with 400 odd forms, 700
reports and about 1000 queries i dont really want to be going through them
and re-referencing everything by hand!

cheers,

Jon

 

[Douglas J. Steele]

I've always been partial to Find & Replace, but I know others who swear by
Speed Ferret (which I hate to admit that I've never been able to get to
work!) I don't think there's going to be a difference between the two in
terms of accuracy.

The tricky thing with any such tool, though, is ensuring that you are very
explicit in telling it what to change. If you've got tblCustomer and
tblCustomerAddress, remember that changing tblCustomer to something else
will also change the beginning of tblCustomerAddress!

 

[Jon]

Hi Doug,

Thanks for that. I had a look at the rickworld.com one and it seemed pretty
good, tried the speed ferret as well and couldnt get it to work either!

The thing I liked about rickworld was that you could import a text file of
the items you want to change. admittedly you have to pay for it (37$ or
something) but as we have 1300 columns to rename seems worth it. you also
have the option to
'match whole word/name only' which will hopefully get around the partial
word changing thing.

Is this the one you use? if so is the payment site secure? not sure if i
want to put my CC details on it!

Cheers,

Jon

 

[Douglas J. Steele]

My acquisition of Rick's Find & Replace predates the existence of his
payment site!

If you're concerned, there are instructions at
http://www.rickworld.com/orders.html for how to avoid posting your credit
card number.