[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

While adding a table and perspective fields - how does one go about setting the Allow Zero Length property to Yes?

Posted on 2007-07-31
4
Medium Priority
?
244 Views
Last Modified: 2013-11-27
I have the following function that enables me to create a new table based on what name you would like to give it.  The problem is when I try to append information - all of the fields have the property - Allow Zero Length as 'No'.  How can I change the below to incorporate the property Allow Zero Length as 'Yes'?  

Even if I didn't have to change any code below - is there an option under Tools - that allows this to be set to yes?

Private Function Add_Table(sName As String) As Boolean
On Error GoTo Err_This

  Dim sSQL As String
 
  If TableExists(sName) = True Then
    sSQL = "DROP TABLE [" & sName & "]"
    CurrentDb.Execute sSQL
    DoEvents
  End If

  Dim rs As DAO.Recordset
  Dim sNewFld As String
  Dim dbs As Database
  Set dbs = CurrentDb
  Dim tdfNew As TableDef
  Set tdfNew = dbs.CreateTableDef(sName)
 
    With tdfNew
      sSQL = "SELECT * FROM tblHorizontal"
      Set rs = CurrentDb.OpenRecordset(sSQL)
      Do Until rs.EOF
        sNewFld = Nz(rs.Fields("fFieldName"), "")
        .Fields.Append .CreateField(sNewFld, dbText)
        rs.MoveNext
      Loop
      Set rs = Nothing
    End With

  dbs.TableDefs.Append tdfNew
  Set tdfNew = Nothing
 
  Add_Table = True
 
Exit_This:
  Exit Function
 
Err_This:
  Resume Exit_This
End Function
0
Comment
Question by:stephenlecomptejr
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19605248
run another sub

sub AllowZeroLen(xTable as string, xField as string)
Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection
If cat.Tables(xTable).Columns(xfield).Properties("Jet OLEDB:Allow Zero Length") = False Then
    cat.Tables(xTable).Columns(xField).Properties("Jet OLEDB:Allow Zero Length") = True
End If

end sub
0
 
LVL 2

Expert Comment

by:tbeernot
ID: 19606967
Or maybe this:

    Dim f As Field
    set f = ....
    f.AllowZeroLength = False
0
 
LVL 2

Expert Comment

by:tbeernot
ID: 19606971
NB: so after adding the field, get it by it's name from the fields collection and set the property
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 19610978

using DAO

Private Function Add_Table(sName As String) As Boolean
On Error GoTo Err_This

  Dim sSQL As String
 
  If TableExists(sName) = True Then
    sSQL = "DROP TABLE [" & sName & "]"
    CurrentDb.Execute sSQL
    DoEvents
  End If

  Dim rs As DAO.Recordset
  Dim sNewFld As String, fld as Dao.field
  Dim dbs As Database
  Set dbs = CurrentDb
  Dim tdfNew As TableDef
  Set tdfNew = dbs.CreateTableDef(sName)
 
    With tdfNew
      sSQL = "SELECT * FROM tblHorizontal"
      Set rs = CurrentDb.OpenRecordset(sSQL)
      Do Until rs.EOF
        sNewFld = Nz(rs.Fields("fFieldName"), "")
         
        Set fld = .CreateField(sNewFld, dbText)
              fld.AllowZeroLength = true

              .Fields.Append fld

                             '.Fields.Append .CreateField(sNewFld, dbText)
        rs.MoveNext
      Loop
      Set rs = Nothing
    End With

  dbs.TableDefs.Append tdfNew
  Set tdfNew = Nothing
 
  Add_Table = True
 
Exit_This:
  Exit Function
 
Err_This:
  Resume Exit_This
End Function
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question