Create an index in existing database

[blip]

Hello,



I have an Access database where searching in a particular field is too slow. I'm looking to create an index for it; This operation works if done manually in the MS Access tool and does improve access time (go table layout, field settings, enable index).

But I'm stuck trying to get a programmed solution with Microsoft's DAO classes (in C++) working. My code boils down to this:

  CDaoIndexInfo       IndexInfo;
  CDaoIndexFieldInfo  FieldInfo;

  FieldInfo.m_strName = "Id";
  FieldInfo.m_bDescending = false;

  IndexInfo.m_bClustered = false;
  IndexInfo.m_bForeign = false;
  IndexInfo.m_bIgnoreNulls = false;
  IndexInfo.m_bPrimary = false;
  IndexInfo.m_bRequired = false;
  IndexInfo.m_bUnique = false;
  IndexInfo.m_lDistinctCount = false;
  IndexInfo.m_nFields = 1;
  IndexInfo.m_pFieldInfos = &FieldInfo;
  IndexInfo.m_strName = "Id";

  try
  {
    if (!GetTableDef()->CanUpdate())
    {
      Error("Cannot update DB?");
    }

    GetTableDef()->CreateIndex(IndexInfo);

I do this right after opening the database (CDaoDatabase).

GetTableDef() returns a CDaoTableDef object representing the table I want to change.

What happens is this:

1. CanUpdate() returns true, indicating the table definition should be modifiable
2. CreateIndex() throws an exception

Error 3211- The database engine could
not lock table

... indicating it is already in use or locked by a different process or operation.

I'm stuck.

The docs indicate that CanUpdate() shouldn't even return true because this is not a newly created DB but an existing one.

Where do I start? Change the CDaoDatabase Open() operation? Associate the CDaoTableDef in a different manner? Etc.

Thanks a lot for your time

[PianoMan64]

Hey blip,

Since I'm not a C++ programmer, I'm a little limited there, but I'm a VBA programmer within MS Access.

One of the MAIN rules that you are violating here is that when you open a query, by default it is in a datasheet view within MS Access. there is an method within DAO library that is called CreateQueryDef(). The method take two parameters, 1) Name of the query you're creating, 2) MS Access SQL Statement. (Please note that the first parameter needs to be a name that is unique within the MS Access Database.)

This is where you are getting your error from.

I have a Function that is written in VBA that checks the existance of all MS Access Object types. It takes two parameters. 1) Object type as String. 2) Object Name as String.

Code below:

Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
 
     Set db = CurrentDb()
     ObjectExists = False
 
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
 
End Function

Then you would need to use this function when creating your modified Query. Code example below:

Dim MyDB as DAO.Database
 
set MyDB = CurrentDB()
 
If ObjectExists("Query", "QUERYNAME") Then
     DoCmd.DeleteObject acQuery, "QUERYNAME"
End if
 
strSQL = "<<MS Access SQL Select Statement Go Here>>"
Set qry = MyDB.CreateQueryDef("<<QueryName Go Here>>", strSQL)

Hope this helps, If this is solved, then mark it as solved.

Thanks,

Joe P.