Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Indexing - What to do?


    I have ODBC access to a progress database which is managed by a third party. I only have the drivers to query this database through Microsoft Access. There is a table which stores revenue data which I would like to analyze, but it is completely un-indexed and is about 125 million rows.

    As I see it, I have two options:

    1.) Contact the third party and request the table gets indexed
    2.) Attempt to Insert the entire table into my own Microsoft Access table and maintain it myself

    Before deciding what to do, I wanted to pose a few questions to the community here to hopefully understand the situation better.

    1.) Is there any reason why this table would not currently be indexed? It has roughly 200,000 writes once per day. As far as I know, the table is only used for our monthly financial reporting.

    2.) Is a select * into NewTable sort of query to create an offline-version of this table feasible in Access? I've never dealt with a table of this size which is completely unindexed.

    Any insight would be great.



  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Nothing prevents you from creating a local index on an attached table, like this:
    Sub CreateIndex()
    ' dbo_CF_Data --> Name of the attached table.
    ' PrimaryKeyIndex --> Name of the index.
    ' SysCounter --> Name of the indexed column.
      Const c_SQL As String = "CREATE UNIQUE INDEX PrimaryKeyIndex " & _
                                "ON dbo_CF_Data (SysCounter ASC) " & _
                                "WITH PRIMARY;"
      CurrentDb.Execute c_SQL, dbFailOnError
    End Sub
    Have a nice day!

Posting Permissions

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