Create Access table

Topic

New Reply

Excel/ Access 2007.
Hello.
I’ve created a procedure that imports a csv file. I’m then using DAO to create a new table (TableDef)
and copy the (csv) data into this new table. Is there an easier way to do this, without me having to
loop through all the rows? Similarly, would there be an easier way if I were appending the records to
an existing table? Thanks, Andy.

Reply | Quote

Replies

You could run an SQL statement:

Dim strSQL As String
Dim strNewTable As String
Dim strCSVTable As String
strNewTable = “tblNew”
strCSVTable = “tblImport”
strSQL = “SELECT * INTO [” & strNewTable & “] FROM [” & strCSVTable * “]”
CurrentDb.Execute strSQL, dbFailOnError

To append to an existing table, use

strSQL = “INSERT INTO [” & strNewTable & “] SELECT * FROM [” & strCSVTable * “]”

You can specify columns instead of *.

Reply | Quote

You could run an SQL statement:

Dim strSQL As String
Dim strNewTable As String
Dim strCSVTable As String
strNewTable = “tblNew”
strCSVTable = “tblImport”
strSQL = “SELECT * INTO [” & strNewTable & “] FROM [” & strCSVTable * “]”
CurrentDb.Execute strSQL, dbFailOnError

To append to an existing table, use

strSQL = “INSERT INTO [” & strNewTable & “] SELECT * FROM [” & strCSVTable * “]”

You can specify columns instead of *.

Thank you. Is tblImport an Excel range name? Andy.

Reply | Quote

I had assumed from your description that you had already imported the CSV file into an Access table named tblImport, and wanted to transfer its records to a new or existing table.

Reply | Quote

I had assumed from your description that you had already imported the CSV file into an Access table named tblImport, and wanted to transfer its records to a new or existing table.

No, sorry. The csv data is currently in an Excel worksheet. I now need, from Excel, to either create a new
Access table or append to an existing one. Andy.

Reply | Quote

You could use DoCmd.TransferSpreadsheet in Access to import from an Excel workbook, or DoCmd.TransferText to import directly from a .csv file.
If you want, you can use Automation to do this from Excel.

Alternatively, you could use ADO – it can run SQL on an Excel worksheet or .csv file.

Reply | Quote

You could use DoCmd.TransferSpreadsheet in Access to import from an Excel workbook, or DoCmd.TransferText to import directly from a .csv file.
If you want, you can use Automation to do this from Excel.

Alternatively, you could use ADO – it can run SQL on an Excel worksheet or .csv file.

Thank you very much. Andy.

Reply | Quote