VBA help From db1.dbf have module to create then import Text

[EagleOne@microsoftdiscussiongroups]

2003

I am not sure which Access group is the best for help below:

The VBA module exists in Tools.mdb
So I run NewAccessDatabase() from Tools.mdb

I get pieces parts to work but not the whole thing.

The issue revolves around which database is the CurrentDb

The code below creates: Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

The code -> appAccess.NewCurrentDatabase strDB creates a new db (Fine!)

But I cannot "SET" 1492 Recon.mdb to then create
TableDef("STARSData")because "it is already open."

If I cannot open it then dbs.CreateTableDef("STARSData") is created either,
not at all, or in the wrong database (typically "Tools/mdb"

I know my sequencing and/or how I refer to the objects is incorrect. That
is where I need help.

Sub NewAccessDatabase()
Dim appAccess As Access.Application
Dim dbs As Object, tdf As Object, fld As Variant
Dim tdf As Object, fld As Variant
Dim strDB As String
Dim dbs As DAO.Database
strDB = "C:\Documents and Settings\1492 Recon.mdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.11")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
'Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDB)
' Get Database object variable.
' Create new table.
'Set dbs = appAccess.CurrentDb.OpenRecordset("STARSData")
Set tdf = dbs.CreateTableDef("STARSData")
' Create fields in new table.
With tdf
..Fields.Append .CreateField("AMT", dbDouble, 19)
..Fields.Append .CreateField("DR_CR", dbText, 4)
..Fields.Append .CreateField("DOC_NUMBER", dbText, 17)
..Fields.Append .CreateField("ACRN", dbText, 6)
..Fields.Append .CreateField("FIPC", dbText, 6)
..Fields.Append .CreateField("REG_NUMB", dbText, 5)
..Fields.Append .CreateField("BFY", dbText, 5)
..Fields.Append .CreateField("APPN_SYMB", dbText, 6)
..Fields.Append .CreateField("SBHD", dbText, 6)
..Fields.Append .CreateField("BCN", dbText, 7)
..Fields.Append .CreateField("SA_FX", dbText, 4)
..Fields.Append .CreateField("AAA_UIC", dbText, 7)
..Fields.Append .CreateField("TRAN_TYPE", dbText, 10)
..Fields.Append .CreateField("DOV_NUMB", dbText, 9)
..Fields.Append .CreateField("DOV_DATE", dbText, 12)
..Fields.Append .CreateField("PIIN", dbText, 15)
..Fields.Append .CreateField("COST_CODE", dbText, 14)
..Fields.Append .CreateField("OBJ_CODE", dbText, 6)
..Fields.Append .CreateField("FUND_CODE", dbText, 6)
..Fields.Append .CreateField("JON_UIC", dbText, 7)
..Fields.Append .CreateField("JON_FY", dbText, 5)
..Fields.Append .CreateField("JON_SERIAL", dbText, 8)
..Fields.Append .CreateField("EFFEC_DATE", dbText, 12)
..Fields.Append .CreateField("EXEC_CODE", dbText, 6)
..Fields.Append .CreateField("USER_ID", dbText, 8)
End With
dbs.TableDefs.Append tdf
Set rs = appAccess.CurrentDb.CreateProperty("STARSData")
dbs.Execute "SELECT * INTO STARSData FROM
Text;FMT=Fixed;HDR=Yes;DATABASE=" _
& "My Documents\;].[STARSData#txt];", dbFailOnError

rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete
Set appAccess = Nothing
End Sub


Need help to make this code work?

TIA EagleOne