>> Importing from SQL

[Jonathan]

Hi, using Access 2003. I want to import data from SQL. If I import to tables
that do not have any indexes the process takes a little over 2 hours with the
database building to 575 MB. When the Access database has indexes in the
tables the transfer fails because the database builds to over 2 GB during the
transfer. Unfortunately the data for now must be in Access format.

I tried to add the indexes after the transfer. I created a table in which I
cache the indexes and indexes' fields. My idea being to delete indexes before
the data import and then re-create after the import. I am hoping that I am
doing something wrong; rather than that this is not possible.

In the following code snippet I use a loop to append fields to an index
fields collection and then append the index to the collection of table
indexes. The problem is an error is triggered by the last line when appending
the index.

For fieldLoop = 0 To UBound(aAllIdxField())
Set idxField = idx.CreateField(aAllIdxField(fieldLoop))
idx.Fields.Append idxField
Next fieldLoop
tdf.Indexes.Append idx

I think the error is that the index fields collection already exists in the
table fields collection. The error is 3409 (Invalid field definition
'[Building_Name]' in definition of index or relationship.) in procedure
CreateTableIndex.

This field is an existing field containing data in this table. The index
name is not the same as the field name. The table does not have any existing
index.

So the question is... how can I add an index using VBA to an existing table
using fields that contain data?

Any ideas or suggestions appreciated

Many thanks,
Jonathan

 

[Jonathan]

A further question... am I expending effort for no gain? that is, I am using
queries to actually select record(s), are query order by settings more
efficient than table indexes and so I should not have any indexes in my
tables?

Many thanks,
Jonathan

 

[Mary Chipman [MSFT]]

How are you importing the data? 2 hours is way too long for what isn't
a large amount of data. Are you inserting one row at a time? If so, a
better solution would be to write a pass-through query that selects
the data in the source table, and then use the pass-through query as
the source for a maketable query. It should only take seconds, or a
few minutes at most, to dump the data into this table, unless you have
a really slow network connection. You can then work with the local
table, either appending the data to another table, or by creating the
necessary primary keys and indexes on the new table.

--Mary

 

[Jonathan]

Hi Mary,

you are right about it taking too long even though this is from sql to
access on a stand-alone machine. I am using sql dts. The 2 hours is into
access tables without indexes. It takes all day, with indexes and then fails!

I will give your pass-through query a go and see whether that works.

 

[Jonathan]

Works now using following...

For fieldLoop = 0 To UBound(aIdxFieldList())
fieldName = aIdxFieldList(fieldLoop)
If Len(fieldName) > 0 Then
With idx
.Fields.Append .CreateField(fieldName)
End With
End If
Next fieldLoop
tdf.Indexes.Append idx

Thanks,
Jonathan

 

[Mary Chipman [MSFT]]

What's in the DTS package? Are you doing a transform or some kind of
data logic on each row? That could be what's slowing it down. You need
to pick this thing apart and eliminate processing one row at a time,
no matter how it's being done. The indexes aren't your problem here--
indexes on the destination table do slow down inserts, but not 2 hours
worth.

--Mary

 

[Jonathan]

Thanks for the suggestion, I'll check it again.

Jonathan