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.OL EDB.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.
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.OL
"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.
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...
1*/ DefaultValue property
2*/ DecimalPlaces property
On my Access table using ADO...
Please experts, look any further into this. I increased points to 120...
ASKER
No answer...
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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