Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    "Excel design" means data is arranged horizontally (not normalized) instead of vertically.

    Excel design:


    OwnerName OwnerPhone Pet1 Pet1DOB Pet2 Pet2DOB Pet3 Pet3DOB Pet4 Pet4DOB
    Relational Db Design:
    tblOwners
    OwnerID_PK OwnerLName OwnerFName OwnerPhone
    tblPets
    PetID OwnerID_FK PetName PetDOB
    Last edited by June7; 12-18-2019 at 05:28 PM.
    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.

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by VAer View Post
    So I just need to use whatever resource I already have.
    Somebody might help or show you how to add fields to a table using VBA because after all, it is possible. However, it doesn't fit the way we do things thus it will likely lead to more problems and you'll just keep coming back asking for more fixes that wouldn't be necessary if you did it the right way. That would be OK if we were getting paid for the wasted time, but we're not. It also goes against our nature to teach you how to do bad things. So someone might ignore all that and give you what you want, but most won't and should not. Sorry if that makes you upset but it's reality.

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    We aren't suggesting you use different resource, just use the one you have more effectively. Access 2GB limit is not based on number of records, it is amount of data (normalized or not) and GUI objects (forms, reports) and code.

    If you really must implement routine creation of fields, yes, it can be done. How many fields do you expect to add over life of db? There is a limit of 255 per table.

    An SQL action statement can create field. Research ALTER TABLE https://www.w3schools.com/SQl/sql_alter.asp

    But some table/field properties can only be programmatically edited with TableDefs so research that topic as well.

    But I'll throw some code at you and you choose your path. Here is an example utilizing both:
    Code:
        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
    

    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2018, 06:10 AM
  2. Replies: 8
    Last Post: 07-07-2017, 09:52 AM
  3. How to write SQL code in VBA
    By stildawn in forum Modules
    Replies: 3
    Last Post: 05-12-2014, 07:56 AM
  4. How To Write Vb Code For This?
    By townguy in forum Queries
    Replies: 1
    Last Post: 09-15-2011, 12:00 AM
  5. Trying to write a simplet vba code
    By bopsgtir in forum Programming
    Replies: 6
    Last Post: 03-12-2011, 08:52 AM

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