We help IT Professionals succeed at work.

Create Table in ADO

thimerion
thimerion asked
on
Hi,
I am using this code to create a table in Access with ADO.

Sub CreateTable()

   Dim tbl As New Table
   Dim cat As New ADOX.Catalog
   Dim colloop As ADOX.Column
 
   'Open the catalog.
   ' Open the Catalog.
    cat.ActiveConnection = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & "\EDI Traffic Billing Temp.mdb;"
     
    tbl.Name = "Customer Billing TEST"
   

    ' Update a new Field object and append it to the Fields
    ' collection of the Customer Billing table.
       
    cat.Tables.Append tbl
   
    Dim Col0 As New ADOX.Column
    Col0.Name = "Customer"
    Col0.Type = adVarWChar
    Col0.DefinedSize = 35
    Col0.Attributes = adColNullable
    tbl.Columns.Append Col0
   
    Dim Col1 As New ADOX.Column
    Col1.Name = "Trading Partner"
    Col1.Type = adVarWChar
    Col1.DefinedSize = 35
    Col1.Attributes = adColNullable
    tbl.Columns.Append Col1
       
    Dim Col19 As New ADOX.Column
    Col19.Name = "Sender"
    Col19.Type = adVarWChar
    Col19.DefinedSize = 35
    Col19.Attributes = adColNullable
    tbl.Columns.Append Col19

    Dim Col2 As New ADOX.Column
    Col2.Name = "Receiver"
    Col2.Type = adVarWChar
    Col2.DefinedSize = 35
    Col2.Attributes = adColNullable
    tbl.Columns.Append Col2

    Dim Col3 As New ADOX.Column
    Col3.Name = "Message Type"
    Col3.Type = adVarWChar
    Col3.DefinedSize = 35
    Col3.Attributes = adColNullable
    tbl.Columns.Append Col3

    Dim Col4 As New ADOX.Column
    Col4.Name = "Interchange"
    Col4.Type = adInteger
    Col4.Attributes = adColNullable
    tbl.Columns.Append Col4
   
    Dim Col5 As New ADOX.Column
    Col5.Name = "Inbound Characters"
    Col5.Type = adInteger
    Col5.Attributes = adColNullable
    tbl.Columns.Append Col5

    Dim Col6 As New ADOX.Column
    Col6.Name = "Outbound Characters"
    Col6.Type = adInteger
    Col6.Attributes = adColNullable
    tbl.Columns.Append Col6
   
    Dim Col7 As New ADOX.Column
    Col7.Name = "Docs"
    Col7.Type = adInteger
    Col7.Attributes = adColNullable
    tbl.Columns.Append Col7

    Dim Col8 As New ADOX.Column
    Col8.Name = "Size"
    Col8.Type = adInteger
    Col8.Attributes = adColNullable
    tbl.Columns.Append Col8

    Dim Col9 As New ADOX.Column
    Col9.Name = "Cost Rate"
    Col9.Type = adDouble
    Col9.Attributes = adColNullable
    tbl.Columns.Append Col9

    Dim Col10 As New ADOX.Column
    Col10.Name = "Cost"
    Col10.Type = adDouble
    Col10.Attributes = adColNullable
    tbl.Columns.Append Col10
   
    Dim Col11 As New ADOX.Column
    Col11.Name = "Price Rate"
    Col11.Type = adDouble
    Col11.Attributes = adColNullable
    tbl.Columns.Append Col11
   
    Dim Col12 As New ADOX.Column
    Col12.Name = "Price"
    Col12.Type = adDouble
    Col12.Attributes = adColNullable
    tbl.Columns.Append Col12
   
    Dim Col13 As New ADOX.Column
    Col13.Name = "MS Customer"
    Col13.Type = adVarWChar
    Col13.DefinedSize = 35
    Col13.Attributes = adColNullable
    tbl.Columns.Append Col13
   
    Dim Col14 As New ADOX.Column
    Col14.Name = "Project"
    Col14.Type = adVarWChar
    Col14.DefinedSize = 35
    Col14.Attributes = adColNullable
    tbl.Columns.Append Col14
   
    Dim Col15 As New ADOX.Column
    Col15.Name = "Comms"
    Col15.Type = adVarWChar
    Col15.DefinedSize = 35
    Col15.Attributes = adColNullable
    tbl.Columns.Append Col15
   
    Dim Col16 As New ADOX.Column
    Col16.Name = "Priority"
    Col16.Type = adVarWChar
    Col16.DefinedSize = 2
    Col16.Attributes = adColNullable
    tbl.Columns.Append Col16
   
    Dim Col17 As New ADOX.Column
    Col17.Name = "EDI Comms"
    Col17.Type = adVarWChar
    Col17.DefinedSize = 35
    Col17.Attributes = adColNullable
    tbl.Columns.Append Col17
   
    Dim Col18 As New ADOX.Column
    Col18.Name = "Total Docs"
    Col18.Type = adInteger
    Col18.Attributes = adColNullable
    tbl.Columns.Append Col18
   
End Sub
' EndCreateTableVB

1*/ For fields Cost and Cost Rate, Price and Price Rate, I need a Double with Decimalplaces = 4.  The current code generates a field Double with DecimalPlaces = Auto.

2*/ Is there a way to create this table without each time define a new Column (Dim Col18 As New ADOX.Column)

It needs to be ADO code or VBA access code (no DAO)!!

Thanks in advance.
Comment
Watch Question

On the first part, I have looked myself and can not find a way to set the Decimal Places when creating a table through code.

For the second part, you could dimension one variable and reuse it each time. Something like this:

   cat.Tables.Append tbl
 
   Dim Col0 As New ADOX.Column
   Col0.Name = "Customer"
   Col0.Type = adVarWChar
   Col0.DefinedSize = 35
   Col0.Attributes = adColNullable
   tbl.Columns.Append Col0
   
   Col0.Name = "Trading Partner"
   Col0.Type = adVarWChar
   Col0.DefinedSize = 35
   Col0.Attributes = adColNullable
   tbl.Columns.Append Col0

I believe you get the idea.

Thanks!

Joe

Author

Commented:
I really need a way to set

1*/ DefaultValue property
2*/ DecimalPlaces property

On my Access table using ADO...

Please experts, look any further into this.  I increased points to 120...

Author

Commented:
No answer...
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi thimerion,
You've requested to delete this question, but its status has remained as 'Pending Delete' because one or more comments have been added.  Normally, the only way to fully delete such a Question is to post a message to Community Support and ask for assistance.

EE is making a one-time database sweep to purge the Pending Delete Questions automatically.  During this sweep:

    thimerion -- To allow the deletion to proceed:  Do nothing.
    EXPERTS -- Please DON'T POST a comment except to contest this deletion.

In the future, please refer to http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp#8 for instruction on deleting questions.

DanRollins -- EE database cleanup volunteer
I don't disagree that the question might need to be deleted. He did ask a two part question and 1/2 of it was answered.

It might be good information for someone to know that as of this time no one knows how to change the decimal places.

Thanks!

Joe
Thanks, I agree, so have refunded 120 points and moved this to our PAQ to benefit others.  
Moondancer - EE Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.