how to create an access file from excel

[visualjoris]

I want to create an access-database-file from excel. The database has 3 tables. The names of the tables are equal
to the worksheetnames. The values of the cells A2 to A400 have to be added in the first column of the tables of the database. Why does the following code not work.
The code does create a database but when I open it, it is empty.


Sub test()
Dim db As Database
Dim Td As New TableDef
Dim kolom1 As New Field

'created database with the same name as excelfile
Set db = workspaces(0).CreateDatabase(ActiveWorkbook.Path & "\" & _
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) _
& ".mdb", dbLangGeneral, dbVersion20)

For i = 1 To Worksheets.Count
Set Td = db.CreateTableDef(Worksheets(i).Name)
Set kolom1 = Td.CreateField(Range("A1"))
kolom1.Name = Range("A1")
kolom1.AppendChunk Range("A2:A400")
Next i



End Sub

[Vasconcelos]

You should have to use the Append method on TableDefs and Fields collection, and specify the data type and size of the field. AppendChunk only works in Memo or Long Binary fields and in the current record, not in multiple records.

See a example in MSDN:

How to Append an Excel Worksheet to a Database Using DAO
http://support.microsoft.com/default...;en-us;q145826