Results 1 to 7 of 7
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    file type causing create table code to fail

    I think (actually I know) that I have had similar code run successfully before.
    Yet today, as soon as I insert per below, the code fails to create the table. Otherwise it runs fine (?)

    In a perplexed state.. (I am)
    Any suggestions will be greatly appreciated in advance

    Code:
    '_______________tbeTypeDetails_AtticStock
    Function CreateAtticStockOnBackEnd(ByVal filepath As String) As Boolean
        Dim db As Database
        Dim DbPath As Variant
        Dim Td As TableDef
        Dim TdName As Variant
        Dim FD As Field
        On Error Resume Next
        DbPath = filepath
        TdName = "tbeTypeDetails_AtticStock"
        'Initialize the table.
        Set db = OpenDatabase(DbPath)
        Set Td = db.CreateTableDef(TdName)
        'Specify the fields.
        With Td
            Set FD = .CreateField("Type", dbText, 15) 'yeah, I know "type" is not a good name to use, but its currently ALL OVER THE PROJECT, so.....
            .Fields.Append FD      
            Set FD = .CreateField("AtticStockYN", dbBoolean)
            FD.DefaultValue = 0
            .Fields.Append FD
    
    '          ________________________
    '***** it's the following lines that are giving be angst
    '***** ________________________
    
    
    '        Set FD = .CreateField("QtyBasis", dbLong)
    '        .Fields.Append FD
    '        Set FD = .CreateField("FixtComplete_Pct", dbLong)
    '        FD.DecimalPlaces = 2
    '        .Fields.Append FD
    '        Set FD = .CreateField("FixtComplete_Min", dbLong)
    '        FD.DecimalPlaces = 0
    '        .Fields.Append FD
    '        Set FD = .CreateField("Driver_Pct", dbLong)
    '        FD.DecimalPlaces = 2
    '        .Fields.Append FD
    '        Set FD = .CreateField("Driver_Min", dbLong)
    '        FD.DecimalPlaces = 0
    '        .Fields.Append FD
    '        Set FD = .CreateField("Module_Pct", dbLong)
    '        FD.DecimalPlaces = 2
    '        .Fields.Append FD
    '        Set FD = .CreateField("Module_Min", dbLong)
    '        FD.DecimalPlaces = 0
    '        .Fields.Append FD
    '        Set FD = .CreateField("Trim_Pct", dbLong)
    '        FD.DecimalPlaces = 2
    '        .Fields.Append FD
    '        Set FD = .CreateField("Trim_Min", dbLong)
    '        FD.DecimalPlaces = 0
    '        .Fields.Append FD
    
    '***** ________________________
    
            
        End With
        'Save the table.
        db.TableDefs.Append Td
        Set FD = Nothing
        Set Td = Nothing
    
        'Clean up
        Set FD = Nothing
        Set Td = Nothing
        Set db = Nothing
    
    End Function
    best,
    stay well,


    m.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    There's no reason to create tables in code.
    They should already exist or create with a query or copy the structure.

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    brand new additional data, not previously part of the project, not able to be extracted from anything existing

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Create Table in SQL

    or just use the Access Create Table interface

    or SQL with vba

    Code:
    'Tracks and Artists
    ' ----------------------------------------------------------------
    ' Procedure Name: CrArtist
    ' Purpose: Create table tbl_tracks with trackLink and artistLink and isAvailable
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 29-Mar-20
    ' ----------------------------------------------------------------
    Sub CrArtist()
    Dim Createsql As String
    Createsql = "Create Table tbl_Tracks" _
              & "(TTId autoincrement primary key,Tracklink  Number, ArtistLink Number, IsAvailable Bit);"
              CurrentDb.Execute Createsql, dbFailOnError
    End Sub

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Which line fails and /or what is the grief?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    i give up... it ought not to have been failing; now it is not
    NTL, thnx for attention

    m.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't give up yet.

    I modified your code from tyour first post and was able to create a new table in the current dB.
    One problem was that you had
    Code:
    On Error Resume Next
    so that you were not notified of any errors.

    Then you had lines like
    Code:
            Set FD = .CreateField("FixtComplete_Pct", dbLong)
             FD.DecimalPlaces = 2
            .Fields.Append FD
    "dbLong" is a Long Integer!! An integer DOES NOT have decimals.
    I commented out the On Error line and the decimal places lines.

    When I executed the code..... TaDa!!
    I had a new table. (had to refresh the database window to see it)


    I used the current database instead of opening a different dB.
    Here is the code
    Code:
    '_______________tbeTypeDetails_AtticStock
    Function CreateAtticStockOnBackEnd() As Boolean
        Dim db As DAO.Database
        Dim Td As DAO.TableDef
        Dim FD As DAO.Field
    
        Dim DbPath As Variant
        Dim TdName As Variant
    
        '    On Error Resume Next
    
        'DbPath = filepath
        TdName = "tbeTypeDetails_AtticStock"
        'Initialize the table.
        Set db = CurrentDb
        'Set db = OpenDatabase(DbPath)
        Set Td = db.CreateTableDef(TdName)
    
        'Specify the fields.
        With Td
            Set FD = .CreateField("Type", dbText, 15)    'yeah, I know "type" is not a good name to use, but its currently ALL OVER THE PROJECT, so.....
            .Fields.Append FD
            Set FD = .CreateField("AtticStockYN", dbBoolean)
            FD.DefaultValue = 0
            .Fields.Append FD
    
            '          ________________________
            '***** it's the following lines that are giving be angst         '***** ________________________
    
            Set FD = .CreateField("QtyBasis", dbLong)
            .Fields.Append FD
    
            Set FD = .CreateField("FixtComplete_Pct", dbLong)
            '        FD.DecimalPlaces = 2
            .Fields.Append FD
    
            Set FD = .CreateField("FixtComplete_Min", dbLong)
            '        FD.DecimalPlaces = 0
            .Fields.Append FD
    
            Set FD = .CreateField("Driver_Pct", dbLong)
            '        FD.DecimalPlaces = 2
            .Fields.Append FD
    
            Set FD = .CreateField("Driver_Min", dbLong)
            '        FD.DecimalPlaces = 0
            .Fields.Append FD
    
            Set FD = .CreateField("Module_Pct", dbLong)
            '        FD.DecimalPlaces = 2
            .Fields.Append FD
    
            Set FD = .CreateField("Module_Min", dbLong)
            '        FD.DecimalPlaces = 0
            .Fields.Append FD
    
            Set FD = .CreateField("Trim_Pct", dbLong)
            '        FD.DecimalPlaces = 2
            .Fields.Append FD
    
            Set FD = .CreateField("Trim_Min", dbLong)
            '        FD.DecimalPlaces = 0
            .Fields.Append FD
    
            '***** ________________________
    
        End With
        'Save the table.
        db.TableDefs.Append Td
        '    Set FD = Nothing
        '    Set Td = Nothing
    
        'Clean up
        Set FD = Nothing
        Set Td = Nothing
        Set db = Nothing
    
    End Function



    'yeah, I know "type" is not a good name to use, but its currently ALL OVER THE PROJECT, so.....
    But that is Not a good reason to keep using a bad name..... just sayin....



    Good luck with your project....

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

Similar Threads

  1. Replies: 2
    Last Post: 02-13-2020, 12:13 PM
  2. Why does one file work and the other one fail
    By Lou_Reed in forum Access
    Replies: 2
    Last Post: 03-15-2017, 04:52 PM
  3. Stopping code on fail
    By DubCap01 in forum Programming
    Replies: 5
    Last Post: 03-06-2015, 08:11 PM
  4. Replies: 3
    Last Post: 07-21-2014, 09:33 AM
  5. Replies: 2
    Last Post: 04-16-2012, 12: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