Link to home
Start Free TrialLog in
Avatar of thimerion
thimerion

asked on

Create Table in ADO

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.
Avatar of joekendall
joekendall
Flag of United States of America image

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

Avatar of thimerion
thimerion

ASKER

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...
No answer...
Avatar of DanRollins
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 https://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
ASKER CERTIFIED SOLUTION
Avatar of Moondancer
Moondancer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial