Load objects into newly created database from text files

[dr91075]

Hello all,

I would like to load objects into a newly created database from text files. Is this possible?

I am creating the database using the following:

Function CreateNewDatabase(strPath As String, strName As String) As Database
Dim WS As Workspace, db As Database, strFullName As String
    strFullName = strPath & "/" & strName
    Set WS = DBEngine.Workspaces(0)
    If Dir(strFullName) <> "" Then Kill strFullName
    Set db = WS.CreateDatabase(strFullName, dbLangGeneral)
End Function

Sub Test()
Dim db As Database
Set db = CreateNewDatabase(CurrentProject.Path, "TestMe.accdb")
End Sub

I can import objects into the "current" database using TransferText, but I can't seem to accomplish this externally. Any ideas or help would be greatly appreciated.

[June7]

Code to read/write/create textfile http://www.databasedev.co.uk/text_files.html

Might be able to read each line of text file, parse the data, and save to table of external database. I am not sure if would be by opening an editable recordset of the external table and adding records or by sql INSERT action. I have used both methods for moving data between 2 dbs (the one with code and one external), but never between two external to the coded db.

[dr91075]

Thanks, June. I appreciate the reply. However, the newly created database would not have any tables. That's the part I'm stuck on. I guess a more accurate question would be how do I move focus from the current database to the created one so that it becomes the current database?

I'm sorry if I'm not explaining well. I've programmed with Excel for a long time, but I'm new to Access.

[June7]

I made assumption you would have code to create tables after creating the db.

AFAIK, can't 'move focus' to the created db.

Can open the new db file and manually go to it (mouse click).

[dr91075]

Thanks again, June. The tables are to be created from the text files. They don't reside in any database. I could import them into the current database and export them to the new one, but I'm concerned about the bloating as this will be done periodically by users. Perhaps it's the only option, though.

[June7]

Why would need new tables and not import data to existing tables?

I have code in one db that periodically creates a db, extracts data into new db, zips the db, emails the db, deletes the new db. The extract code is like:
CurrentDb.Execute "SELECT * INTO tablename IN 'C:\filename.accdb' FROM queryname;"

Then in another db I have code to import data from another db:
DoCmd.RunSQL "INSERT INTO tablename(FAAID) SELECT FAAID FROM [" & strSourcePath & "].tablename;"

Seems that code in db1 should be able to grab data from external source (db2 or text file) and insert into db3. In the case of text file, since TransferText won't work, think have to first create the table then use INSERT action of parsed data from text file.

[dr91075]

Thanks June. I'll give this some thought.

[dr91075]

Just to update... I was able to work this out by storing the code to import the textfiles in the current database, exporting the module to the newly created database, and running that procedure...

Sub CreateNew()
    Dim acApp As Access.Application
    
    CreateNewDatabase CurrentProject.Path, "NewTester.accdb"
    DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.Path & "/NewTester.accdb", acModule, "Module3", "Module3"
    
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase CurrentProject.Path & "/NewTester.accdb"
    acApp.Run "ImportForms"
    Set acApp = Nothing
End Sub