Results 1 to 8 of 8
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Error: Cannot find table or constraint


    In the segment of code shown below, the new table is created and linked successfully. However, there's some sort of subtlety, timing I guess, where the new table is not yet available to have the fields added. Is there some command I need to run first?

    Code:
    '---------------------------------------------------------------------------------
    '  Okay, now for the new table
    '---------------------------------------------------------------------------------
        Dim BkndDB As DAO.Database
        Dim strBkndDB As String
        
        'DB that gets the new table -----------------------
        strBkndDB = Mid(CurrentDb.TableDefs("InstProperties").Connect, 11)  'Skip over the ;DATABASE=
        Set BkndDB = OpenDatabase(strBkndDB)
    
        'Okay, now create it-------------------------------
        BkndDB.Execute "CREATE TABLE tblTMSCmds (TWCmdID AUTOINCREMENT CONSTRAINT TWCmdID PRIMARY KEY)"
        
        'Okay, now Link to the new table-------------------
        DoCmd.TransferDatabase acLink, "Microsoft Access", strBkndDB, acTable, "tblTMSCmds", "tblTMSCmds"
        
        'Finally, release the object-----------------------
        Set BkndDB = Nothing
        
    '---------------------------------------------------------------------------------
    '  And now its fields.
    '---------------------------------------------------------------------------------
    
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWDTCreated Text(20)"            'Date/Time Twilio logged in incoming TMS command
        conBackend.Execute strDDL, dbFailOnError
    
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWDTRelayed Text(20)"            'Date/Time TMS executed (relayed) the request
        conBackend.Execute strDDL, dbFailOnError
        
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWGroupID Long"                  'Record ID of group receiving the broadcast message
        conBackend.Execute strDDL, dbFailOnError
            
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWNumSMS long"                   'The number of SMS sent with this request
        conBackend.Execute strDDL, dbFailOnError
                
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWNumCalls long"                 'The number of Calls made with this request
        conBackend.Execute strDDL, dbFailOnError
        
        strDDL = "ALTER TABLE tblTMSCmds ADD Column TWMsgBody Text(255)"             'The text of the message broadcasted
        conBackend.Execute strDDL, dbFailOnError

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    I have a procedure that creates table then alters. But I use different linking code:

    'set table link
    Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
    tdf.SourceTableName = Me.tbxTestNum
    tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
    CurrentDb.TableDefs.Append tdf
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    "Data\LabData.accdb"
    "Data\LabData.accdb" being the back end? And table created & linked but with zero fields?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Yes, it is backend. Here is the CREATE line:

    cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"

    Then more fields are added.

    Should be able to add those 6 fields with one ALTER action.

    strDDL = "ALTER TABLE tblTMSCmds ADD (TWDTCreated Text(20), " & _
    "TWDTRelayed Text(20), TWGroupID Long, TWNumSMS long, " & _
    "TWNumCalls long, TWMsgBody Text(255))"

    But why would you need a procedure that repeatedly creates a table with exact same data structure? My process creates new table and fields will be different with exception of the 2 in the CREATE action. Yes, it is not a fully normalized db. These new tables have a 1-to-1 relationship with a parent table. Need to create new table is rare. So rare that I really prefer to create table manually. I did not originate this code but cleaned it up some then essentially ignored it. The idea was to give end user administrator ability to create new tables. Has never been used.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks,
    I'll give your code a try.
    Bill

    But why would you need a procedure
    The procedure is part of a compatibility module that runs at startup and inspects the compatibility between the version of the app versus the version of the backend db. With that, when I release a new version of the app any changes to the tables/structures are automatically updated to DB's "in the field".

    Since you asked
    Code:
    Private Sub Upgrade(DBCurVer As Variant)
    
    '=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
    ' New code is required here to perform necessary upgrades from known versions.
    ' That means when TMS code requires an update to any TableDefs that code be
    ' inserted here to accomplish the modifications. For example, say we update
    ' TMS to version 8.3 and that version requires TableDef changes. We set
    ' TMSDBVersion to 8.3 and write the necessary code to modify the table definitions
    ' accordingly and update the IPDBVersion of the database to match.
    '
    ' Everytime such a change is necessary we iterate the code blocks until we've
    ' taken the current back-end database incrementally to the highest level, where
    ' each block takes us from one version to the next and so on.
    '
    ' The "Select", then, is where we insert code to take the database from the
    ' selected "case" to the next level.
    '=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
    
    Select Case DBCurVer
        Case 7.1        'Insert the code here that's necessary to upgrade from version 7.1
            MsgBox "Database structure being updated from 7.1 to 7.3"
            Call InitVer7pt3
        Case 7.3
            MsgBox "Database structure being updated from 7.3 to 7.4"
            Call InitVer7pt4
        Case 7.4
            MsgBox "Database structure being updated from 7.4 to 7.6"
            Call InitVer7pt6
         Case 7.6
            MsgBox "Database structure being updated from 7.6 to 8.0"
            Call InitVer8
         Case 8#
            MsgBox "Database structure being updated from 8.0 to 8.4"
            Call InitVer8pt4
         Case 8.4
            MsgBox "Database structure being updated from 8.4 to 9.0"
            Call InitVer9
         Case 9#
            MsgBox "Database structure being updated from 9.0 to 9.4"
            Call InitVer9pt4
         Case 9.4
            MsgBox "Database structure being updated from 9.4 to 9.7"
            Call InitVer9pt7
         Case 9.7
            MsgBox "Database structure being updated from 9.7 to 9.94"
            Call InitVer9pt94
         Case 9.94
            MsgBox "Database structure being updated from 9.94 to 9.97"
            Call InitVer9pt97
         Case 9.97
            MsgBox "Database structure being updated from 9.97 to 10.5"
            Call InitVer10pt5
         Case 10.5
            MsgBox "Database structure being updated from 10.5 to 10.53"
            Call InitVer10pt53
         Case 10.53
            MsgBox "Database structure being updated from 10.53 to 10.56"
            Call InitVer10pt56
         Case 10.56
            MsgBox "Database structure being updated from 10.56 to 10.60"
            Call initver10pt60
         Case 10.6
            MsgBox "Database structure being updated from 10.60 to 10.80"
            Call initver10pt80
          Case 10.8
           MsgBox "Database structure being updated from 10.80 to 12.00"
           Call initver12pt00
    End Select
    
    End Sub

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I don't have this right yet, as I get an error message that table already exists. I understood the lines highlighted in red taken from your post to be those that established the link to the new table created with the blue line above. Apparently that's not correct? CurrentDb.CreateTableDef out of place?

    Code:
    '---------------------------------------------------------------------------------
    '  Okay, now for the new table
    '---------------------------------------------------------------------------------
        Dim BkndDB As DAO.Database
        Dim strBkndDB As String
        Dim tdf As TableDef
            
        'DB that gets the new table -----------------------
        strBkndDB = Mid(CurrentDb.TableDefs("InstProperties").Connect, 11)  'Skip over the ;DATABASE=
        Set BkndDB = OpenDatabase(strBkndDB)
    
        'Okay, now create it-------------------------------
        BkndDB.Execute "CREATE TABLE tblTMSCmds (TWCmdID AUTOINCREMENT CONSTRAINT TWCmdID PRIMARY KEY)"
        
        'Okay, now Link to the new table-------------------
        'DoCmd.TransferDatabase acLink, "Microsoft Access", strBkndDB, acTable, "tblTMSCmds", "tblTMSCmds"
        Set tdf = CurrentDb.CreateTableDef("tblTMSCmds")
        tdf.SourceTableName = "tblTMSCmds"
        tdf.Connect = "; DATABASE=" & strBkndDB
        CurrentDb.TableDefs.Append tdf
        tdf.RefreshLink
        
        'Finally, release the object-----------------------
        Set BkndDB = Nothing

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    That is correct.

    Except I am using ADO for creating table and DAO to modify.
    Code:
    Private Sub tbxTestNum_AfterUpdate()
    Dim td As TableDef
    'check if test table already exists
    For Each td In DBEngine.OpenDatabase(gstrBasePath & "\Data\LabData.accdb").TableDefs
        If td.Name = Me.tbxTestNum Then
            MsgBox "Data table for this test already exists in the backend."
            Exit Sub
        End If
    Next td
    Me.DataField.SetFocus
    DoCmd.GoToRecord , , acNewRec
    End Sub
    
    
    Private Sub btnBuild_Click()
    On Error GoTo err_Proc
    Dim tdf As TableDef
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim db As DAO.Database
    If IsNull(Me.tbxTestNum) Then
        MsgBox "Must enter test number.", vbCritical, "Error"
    Else
        Set cn = New ADODB.Connection
        'connect to the backend database
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
        'create the test table
        cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
        'set table link
        Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
        tdf.SourceTableName = Me.tbxTestNum
        tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
        CurrentDb.TableDefs.Append tdf
        Set rs = New ADODB.Recordset
        rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
        'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
        Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
        While Not rs.EOF
            If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
                'create field in new table
                cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
                            rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
                            IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
            End If
            If rs!DataType = "Text" Then
                'change the AllowZeroLength default Yes to No
                db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
            ElseIf rs!DataType = "Number" Then
                'make sure number field does not have 0 as DefaultValue
                db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).DefaultValue = ""
            End If
            rs.MoveNext
        Wend
        
        rs.Close
        cn.Close
        db.Close
        
        If IsNull(DLookup("DataTable", "TestsFieldAlias", "DataTable='" & Me.tbxTestNum & "'")) Then
            'add records to TestsFieldAlias table, this table associates field names of the test tables to field names of Specs and legacy dBase tables
            CurrentDb.Execute "INSERT INTO TestsFieldAlias (DataTable, DataField, Alias, Specification, Transfer) " & _
            "SELECT " & Me.tbxTestNum & " AS DataTable, DataField, Alias, Specification, Transfer " & _
            "FROM BuildTable WHERE Specification = -1 Or Transfer = -1;"
            'purge BuildTable
            DoCmd.RunSQL "DELETE FROM BuildTable WHERE DataField <> 'LabNum' AND DataField <> 'method'"
        Else
            MsgBox "Records for this test number already exist in TestsFieldAlias table."
        End If
        
    End If
    Me.tbxTestNum.SetFocus
    Exit_proc:
        Exit Sub
        
    err_Proc:
        MsgBox "Error encountered in AddDataTable procedure btnBuild_Click - " & Err & " : " & Err.Description
        Resume Exit_proc
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks June,

    I don't use ADO so I'll have to study your code and the language elements before I can assimilate what you've given me.

    Bill

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

Similar Threads

  1. cannot find table or constraint
    By HAREM in forum Access
    Replies: 6
    Last Post: 04-06-2019, 07:11 AM
  2. CHECK CONSTRAINT causing Syntax Error?
    By AishlinnAnne in forum SQL Server
    Replies: 18
    Last Post: 09-13-2016, 12:42 PM
  3. Cannot find Table or Constraint
    By Lowet50 in forum Access
    Replies: 3
    Last Post: 03-27-2015, 02:24 PM
  4. Replies: 5
    Last Post: 09-29-2014, 07:43 PM
  5. Replies: 2
    Last Post: 01-11-2014, 03:56 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
  •  
Other Forums: Microsoft Office Forums