Adding columns with adBoolean data type to Adox table using vba

[agarwaldvk]

Hi Everybody


I am using vba to add columns to an adox table. The code is something like this :-


With adoxTable
.Name = tableName
.Columns.Append "IsBacklog" , adBoolean
.Columns("IsBacklog").Attributes = adColNullable
.Columns.Append "IsMultipleInvoices" , adBoolean
.Columns("IsMultipleInvoices").Attributes = adColNullable
End With
adoxCatalog.Tables.Append adoxTable 'Generates error here - doesn't like adBoolean or adBinary


The database (as adox catalog) and the table (as adox table) are being created on the fly. The database is in Access 2007 format.

When it comes to the part when these columns are being appended to the table, it comes up with an error :- "Multiple-step..." error

It works ok with the data type being 'adInteger' or the default text field but doesn't with either adBoolean or adBinary.

I am extracting data from an SQL Server database table when these fields are of data type "bit". I understand that the equivalent Access data type is 'adBinary'. But with 'adBinary' field data type, it comes up with an error when I try to populate the table with the entry from the recordset containing data from the SQL Server table.

Any suggestions please?



Best regards



Deepak

[orange]

You might try this link for info
http://allenbrowne.com/func-adox.html#CreateTableAdox


As for SQL Server data types, I don't use SQL server. If you are trying to move data to a boolean, my guess is you'd have to determine if the incoming field was >0 or not and assign the boolean accordingly.

[agarwaldvk]

Orange

Thanks mate!

I will have a look at it tomorrow morning! Hopefully, it might have what I need.


Best regards


Deepak