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.
Nothing prevents you from creating a local index on an attached table, like this:
' 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) " & _
CurrentDb.Execute c_SQL, dbFailOnError