Thread: Importing Text (csv) table to correct database

Reposted with Rule-abiding Title

Hello, first timer, got a simple one

I have very comprehensive Excel vba skills, but am learning Access the wrong way (or the right way but haven't gotten my head around the object structures yet)

I have lots of csv files I have created from an export. They are stats and figures from individual weeks, numbered 1-52

What I'm trying to do is the 'ImportTextWizard' in Access 2010, to create Database copies of the csv's so I can delete the csv's. There are a lot more than 52 because it goes over several years.

So far, in a module sitting in 'Database4.accdb' I have a module with the following code in it:

Public Function ImportAll()
Dim oFs As New FileSystemObject, oFolder As Object, oFile As Object
Dim cleanname As String
Dim wsp As DAO.Database
If oFs.FolderExists("Filepathfolder") Then
        Set oFolder = oFs.GetFolder("Filepathfolder")
            For Each oFile In oFolder.Files
                    cleanname = Left(oFile.Name, Len(oFile.Name) - (Len(oFile.Name) - InStr(1, oFile.Name, ".", vbTextCompare)) - 1)
                        Set wsp = DBEngine.CreateDatabase(oFolder.Path & "\" & cleanname & ".accdb", dbLangGeneral)
                            With wsp
                            Debug.Print oFile
                                DoCmd.TransferText acImportDelim, CSVimport, cleanname, oFile, True
                                .Close
                            End With
            Next
End If
        
End Function

This does everything, but in the wrong way.

Using this code, what I get is 52 .accdb's in the Filepath directory, which are empty of tables, and I get 52 tables added to the Database4.accdb

In Excel I can specify which workbook/worksheet I'm dealing with, or, if all else fails, fall back on '.Activate' (yuck)

How can I get each Database to have a table of the same name, and one each? End result from "Week1.csv" should be to have one "Week1.accdb" with a table inside called "Week1"

NB: My specs 'CSVImport' are simply to be Delimited, with the 1st row as headers. Though I wonder if because the CSVImport was 'recorded'/'saved' when Database4.accdb was open, means that that is the database the specs say to write the table to?

Just a bit confused. I get the feeling I'm a parameter or 2 or a single line of code off this working...


All help appreciated, and I acknowledge the code may look amateur...because it is!

Thanks
C