Newbie - step1 - importing MS access tables

Sorry - this question has probably been asked many times before as it
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
Tony
With as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA
|||
>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.