Results 1 to 4 of 4

VBA Create index on linked table

  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Brooklyn Hts, NYC
    Posts
    333

    VBA Create index on linked table

    maybe I'm exhausted


    ...I thought I had gotten this code to work; but I must have been disillusioned;
    it certainly does not now
    (although it doesn't throw any errors, it does not create the index)
    any suggestions would be greatly appreciated

    with much thanks in advance,
    mark


    Code:
        
    ' ADD INDEX to the table
    'AddIndexToTable(ByVal TblName As String, IndexName As String, IsPrimary As Boolean, IsUnique As Boolean, ParamArray FldNames()) As Boolean
    
        vTableName = "tbeInstallationNotes_EOS"  'table is LINKED, but not at the time it is being updated; it is the same name locally and remote
        vIndexName = "InstallationNote_ID"
        'is Primary = true
        'is Unique = true
        vFldName1 = "InstallationNote_ID"
        result = AddIndexToTable(vTableName, "InstallationNote_ID", True, True, "InstallationNote_ID")
    _____________
    Code:
    Function AddIndexToTable(ByVal TblName As String, IndexName As String, IsPrimary As Boolean, _
                             IsUnique As Boolean, ParamArray FldNames()) As Boolean
        Dim Idx As Index
        Dim Td As TableDef
        Dim DbPath As Variant
        Dim db As Database
        Dim FldNum As Integer
    
        On Error Resume Next
    
        'get back end path of linked table
        DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And Type=6")
        If IsNull(DbPath) Then
            Set db = CurrentDb    'if local table
        Else
            Set db = OpenDatabase(DbPath)    'if linked table
            If Err <> 0 Then
                'failed to open back end database
                Exit Function
            End If
            'in case back end has different table name than front end
            TblName = DLookup("ForeignName", "MSysObjects", "Name='" & TblName & "' And Type=6")
        End If
    
        'get table
        Set Td = db.TableDefs(TblName)
        If Err <> 0 Then
            'failed to get table
            GoTo Done
        End If
    
        With Td
            On Error Resume Next
            Set Idx = .Indexes(IndexName)    'test for existence
            If Err = 0 Then GoTo Done
    
            If Err > 0 Then    'create index
                On Error Resume Next
                Set Idx = .CreateIndex(IndexName)
                With Idx
                    For FldNum = 0 To UBound(FldNames)
                        .Fields.Append .CreateField(FldNames(FldNum))
                    Next
                    .IgnoreNulls = True
                    .Primary = IsPrimary
                    .Unique = IsUnique
                End With
                .Indexes.Append Idx
            End If
    
        End With
    
        If Err = 0 Then AddIndexToTable = True
    
    Done:
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,797
    You don't need any code.
    use AutoNum field.
    zero effort.

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,999
    it is probably not throwing errors because of your on error resume next line. After you have tested the error you need 'on error goto 0' to reset it

    other thing to do is to step through the code, checking values as you go

    have you looked at using sql to create the index rather than playing around with the tabledef?

  4. #4
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Brooklyn Hts, NYC
    Posts
    333
    about the "on error resume next"
    ...as I used to be told all of the time: "when all else fails: CHECK THE OBVIOUS"

    SQL - worked fine
    (still don't know what was wrong with the original code; but that's moot now. I go on...)

    thnx!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a linked table in Access
    By JuliusV in forum Access
    Replies: 1
    Last Post: 06-29-2015, 11:41 PM
  2. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 04:49 PM
  3. Replies: 5
    Last Post: 04-05-2014, 07:52 PM
  4. Create a linked table from excel using VBA
    By stigmatized in forum Import/Export Data
    Replies: 2
    Last Post: 08-04-2012, 12:21 AM
  5. Replies: 0
    Last Post: 05-10-2012, 12:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums