New Field Padding Problem in Access

[mikemcginty]

When I create a new text field (say size 20) in my Access database using ADO, the whole field is padded with spaces whenever I write data. The same does not happen if I create the new field directly using Access.

eg if I write "TEST" the "TEST " is returned

I also used Access to delete the additional spaces, but they automatically regenerate.

Any ideas

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[petermeachem]

That's weird. How are you reading and writing the fields?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[mikemcginty]

The app retrieves data through an ADO connection

The field is being padding by Access but only for fields I have created using ADO. When I manually create the field thru Access it is ok. All parameters of the ADO field seem the same as the manually created field.

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[petermeachem]

Can we see a bit of the code you use to create a field then.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[mikemcginty]

Type TableField

Name As String ' name of the field
Type As Integer ' type of the field
DefinedSize As String
DefaultValue As Integer
End Type

Private sub AddFieldToTable()

Dim tFieldParameters As TableField

tFieldParameters.Name = "NewField"
tFieldParameters.Type = adWChar
tFieldParameters.DefinedSize = "100"

iDummy = iADOXAddTableField(tblMyTableName, tFieldParameters)

end sub

Private Function iADOXAddTableField(sTableName As String, tField As TableField) As Integer
' add a new field to an existing database table
' if the field already exists then the function exits

Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column

On Error GoTo ExitError

' anticipate failure
' failure will occur if the field already exists
iADOXAddTableField = -1

' open the catalog
ADODBCheckConnection
cat.ActiveConnection = dbQADMObject

With col

.Name = tField.Name
.Type = tField.Type

If tField.DefinedSize <> "" Then _
.DefinedSize = tField.DefinedSize

End With

cat.Tables(sTableName).Columns.Append col

' return success
iADOXAddTableField = 1

ExitError:

Set cat = Nothing
Set col = Nothing

End Function

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[petermeachem]

I don't do it that way. I presume the problem is with the way the column is defined, have you tried using adChar instead of adWChar for instance.

I just do
on error resume next
thing.execute "ALTER TABLE ADD Column etc

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[mikemcginty]

Thanks Peter

I will try that

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!