Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Table (ADOX - VB6)

    I'm tying to create a table in an Access 97 database using ADOX 2.7 & I want to set any text field in the table to 'Allow Zero Length' (Using DAO, this was an actual property of the fields collection). In the below example I'm copying each column from a source table & appending it to a new table definition. I'm setting the 'adColNullable' but I think this is an Access 2000 property??

    Is this the best way to do this? I basically want a flexible function that will allow users to copy any table (mvarTableName) from a source server (probably SQL Server) and place into an Access database. Is there a quicker/easier way than the route I'm taking here....?

    '// Create the table if it doesn't exist
    If (tblDest Is Nothing) Then
    Set tblDest = New ADOX.Table
    tblDest.Name = mvarTableName
    With tblDest
    For Each colField In tblHost.Columns
    .Columns.Append tblHost.Columns(colField.Name).Name, _
    tblHost.Columns(colField.Name).Type, _
    tblHost.Columns(colField.Name).DefinedSize
    If tblHost.Columns(colField.Name).Type = (adVarChar Or adVarWChar) Then
    .Columns(colField.Name).Attributes = adColNullable
    '//.Columns(colField.Name).Properties("Allow Zero Length") = True
    End If
    Next
    End With
    catDest.Tables.Append tblDest
    End If

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create Table (ADOX - VB6)

    adColNullable is a constant that describes one of the ADOX Column attributes, not a property. It has a value of 2, so you can always substitute the value for the constant, but if you're using the ADOX library, then the constant should be available to you even in Access 97.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Table (ADOX - VB6)

    Sorry, this is a VB6 application not an Access database.

    The user will select any database to export to using common dialog in VB6, the application then checks to see if the table to export already exists in that database (using the Catalogue.Tables collection). If it doesn't, it has to make the table by copying the source table definition, which is what the code below does.

    I just basically want to be able to set any text field column created to 'Allow Zero Length'....is this possible using ADOX??

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create Table (ADOX - VB6)

    I realize that, but you specified an Access 97 format and you're trying to modify an Access 97 field, right?
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create Table (ADOX - VB6)

    The syntax is Column.Properties("Set OLEDB:Allow Zero Length").
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Table (ADOX - VB6)

    Charlotte, that's great - thanks very much...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •