create table in database programaticlly

[sal21]

I loop from vba code for excel a vlaue in sheet ( are year 2001,2010,1958,....) with a for next, i loop only distinct value from column.
I just have a databse in c:\mydatabse.mdb.
How to create new table named YEARS with the fileds of loop in sheet column?
Possible with ado or dao code in vba for excel?

Note:
i just have an active ADO connection named myconn

[HansV]

Do you want to create a separate field for each year, or do you want a single field, with the years as values (i.e. a record for each year)?

You haven't reacted to the reply in Post=22498 yet...

[sal21]

Do you want to create a separate field for each year, or do you want a single field, with the years as values (i.e. a record for each year)?

You haven't reacted to the reply in Post=22498 yet...

each filed for yeach year of the loop...

[HansV]

OK. What type of data do you want to store in the fields? Text (if so, how long should the field be), or numbers (if so, whole numbers or numbers with decimals), or dates, or ...?

[sal21]

OK. What type of data do you want to store in the fields? Text (if so, how long should the field be), or numbers (if so, whole numbers or numbers with decimals), or dates, or ...?

numbers....
i want to store a numbers in column filed. with the format 000.000.000

Note:
during the creation is possible to indexed the fileds?

[HansV]

Do you want an index on each field? If so, why? In general, it is not a good idea to index all fields in a table.

[sal21]

Do you want an index on each field? If so, why? In general, it is not a good idea to index all fields in a table.

OK No index in fileds...

The teacher are you

Sorry...
before to create the list of year fileds i need to create a first field (indexed) with name AGENCY
sorry

ops i have found a code and my idea is:
........
Set adoxTable = New ADOX.Table
With adoxTable
.Name = "ANNO_AGENZIE"
for i to ....
my_var_year=sheets range A & i
.Columns.Append "my_var_year", adVarWChar, 4
next i
'.Columns.Append "ItemDescription", adVarWChar, 100
' .Columns.Append "ItemValue", adVarWChar, 4
' .Keys.Append "PrimaryKeyItemID", adKeyPrimary, "ItemID"

End With
........

wath you think about?

[HansV]

Let's say that the variable in the loop that contains the year is lngYear.

Code: Select all

Dim strSQL As String
' Before the loop:
strSQL = "CREATE TABLE YEARS (AGENCY Long"
...
' Within the loop:
strSQL = strSQL & ", [" & lngYear & "] Long"
...
' After the loop:
strSQL = strSQL & ")"
myconn.Execute strSQL
' Add the index on AGENCY:
strSQL = "CREATE INDEX AGENCYINEX ON YEARS (AGENCY ASC)"
myconn.Execute strSQL

[sal21]

Let's say that the variable in the loop that contains the year is lngYear.

Code: Select all

Dim strSQL As String
' Before the loop:
strSQL = "CREATE TABLE YEARS (AGENCY Long"
...
' Within the loop:
strSQL = strSQL & ", [" & lngYear & "] Long"
...
' After the loop:
strSQL = strSQL & ")"
myconn.Execute strSQL
' Add the index on AGENCY:
strSQL = "CREATE INDEX AGENCYINEX ON YEARS (AGENCY ASC)"

myconn.Execute strSQL

No comments!