Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Transfering a table with indexing

    I am using Access 2003.

    I am using the following code to make some data local from a serving database:

    Code:
        Set DB1 = DBEngine(0).OpenDatabase(right(CurrentDb.TableDefs("Results").Connect, Len(CurrentDb.TableDefs("Results").Connect) - 10))
        
        SQL = "SELECT DISTINCT Samples.SeriesID, Samples.TrialID, Series.Year, Samples.CropID, ResultDetails.MPTVID, ResultDetails.MethodID, ResultDetails.ProductTested, ResultDetails.Technique, ResultDetails.Version INTO ZZGUIFields IN 'C:\Annual Database\Annualdb Interface.mdb' " _
            & "FROM (Samples INNER JOIN Series ON Samples.SeriesID = Series.SeriesID) INNER JOIN (ResultDetails INNER JOIN Results ON ResultDetails.ResultDetailID = Results.ResultDetailID) ON Samples.SampIDBarcode = Results.SampleID " _
            & "WHERE Not Samples.LabNumber Is Null"
        On Error Resume Next
        CurrentDb.TableDefs.Delete "ZZGUIFields"
        On Error GoTo 0
        
        DB1.Execute SQL
    This is useful to me but if I use this technique to transfer a single table, it does not keep the indexing.

    Is there a way to transfer a table with indexing intact from a remote access database to a local access database?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Why not just add an Order By to the end of the SQL statement? Unless you specifically need the field to be indexed, adding the Order By will sort the field like indexing it would do.

    SQL = "SELECT DISTINCT Samples.SeriesID, Samples.TrialID, Series.Year, Samples.CropID, ResultDetails.MPTVID, ResultDetails.MethodID, ResultDetails.ProductTested, ResultDetails.Technique, ResultDetails.Version INTO ZZGUIFields IN 'C:\Annual Database\Annualdb Interface.mdb' " _
    & "FROM (Samples INNER JOIN Series ON Samples.SeriesID = Series.SeriesID) INNER JOIN (ResultDetails INNER JOIN Results ON ResultDetails.ResultDetailID = Results.ResultDetailID) ON Samples.SampIDBarcode = Results.SampleID " _
    & "WHERE Not Samples.LabNumber Is Null Order By Samples.SeriesID"
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2006
    Posts
    72
    Quote Originally Posted by pkstormy
    Why not just add an Order By to the end of the SQL statement? Unless you specifically need the field to be indexed, adding the Order By will sort the field like indexing it would do.

    SQL = "SELECT DISTINCT Samples.SeriesID, Samples.TrialID, Series.Year, Samples.CropID, ResultDetails.MPTVID, ResultDetails.MethodID, ResultDetails.ProductTested, ResultDetails.Technique, ResultDetails.Version INTO ZZGUIFields IN 'C:\Annual Database\Annualdb Interface.mdb' " _
    & "FROM (Samples INNER JOIN Series ON Samples.SeriesID = Series.SeriesID) INNER JOIN (ResultDetails INNER JOIN Results ON ResultDetails.ResultDetailID = Results.ResultDetailID) ON Samples.SampIDBarcode = Results.SampleID " _
    & "WHERE Not Samples.LabNumber Is Null Order By Samples.SeriesID"
    Does ORDER BY create an index when selecting into a table? I did not know that. If so, this is the answer to my question.

    [EDIT]
    Sorry somehow I did not read everything you said. The problem is that the table has a lot of records and I have to do select distincts on it which takes an amount of time. I may have the wrong understanding of indexes but I am hoping that if I can keep the index on and transfer a table that I will be able to query it faster.
    Last edited by Access Junkie; 10-30-08 at 02:22.

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I have observed quite often that when I alter a table by deleting a field, Access sometimes tells me that it is having to delete one or more indexes. It does not do this every time. I deduce from this that when you run a query with joins or ordering, Access will create an index on the fly in order to faclitate the query execution. It is a moot point as to whether all of such indexes are retained permanently. It could be, for instance, that they disappear when you invoke the Repair and Compact Utility.
    One way you could check this would be to run the Anayse/Documenter option in the Database Utilities (with the appropraite settings) and then examine the output to see which fields are listed as being indexed. By doing this at intervals, you can check the permanence of such indexes.
    My other big database experience is in Informix, where there is a DDL statement which says CREATE INDEX ON (list of fields). I have not checked to see whether there is a corresponding feature in Access.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You might also consider just linking to an Excel/external file versus having to "transfer" the file. I'm not sure what you can do regarding indexes with the file linked into MSAccess but it might save you some export time if you just created the external file and then linked it into MSAccess. Then you could run your queries on the file in MSAccess to update the linked table (but you may get slower results with a linked file on a large dataset versus if that table was in MSAccess.)

    I've done this in the past where I wanted to transfer data from one mdb on one server to another remote server and I couldn't get directly to the data table in the mdb that I wanted to for some reason or another.
    Last edited by pkstormy; 10-31-08 at 23:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •