Help with Access VBA to create a new file and import textfile data

  • Thread starter EagleOne@microsoftdiscussiongroups
  • Start date
E

EagleOne@microsoftdiscussiongroups

2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
 
E

EagleOne@microsoftdiscussiongroups

Some progress made

Replaced: Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDB)

With: Set dbs = appAccess.CurrentDb

Works till the TextFile Import.

Can any one see the error in that code line?

EagleOne@microsoftdiscussiongroups said:
2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
 
D

Douglas J. Steele

You seem to be missing an opening square bracket in your SQL statement. As
well, I don't believe you can use My Documents as a location: you need an
actual location.

Grab the code from http://www.mvps.org/access/api/api0054.htm at "The Access
Web", and use it to determine the actual location of your My Documents
folder.

Dim strFolder As String

strFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL)

dbs.Execute "SELECT * INTO STARSData " & _
"FROM [Text;FMT=Fixed;HDR=Yes;DATABASE=" & _
strFolder & "\;].[STARSData#txt];", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
 
E

EagleOne@microsoftdiscussiongroups

Thank you Doug for your time and knowledge,

Here is what seems to work: (NOTE: A few easy? questions below)

Sub NewAccessDatabase()
'
Dim appAccess As Access.Application
Dim tdf As Object, fld As Variant
Dim strDB As String
Dim dbs As DAO.Database
Dim rs As Recordset
Dim myFileName As String
Dim myPath As String
myPath = CurrentProject.Path & "\"

' Initialize string to database path.
strDB = myPath & "1492 Recon.mdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.11")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
Stop
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError
Set rs = dbs.OpenRecordset("FIELDData")
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete

Set appAccess = Nothing
End Sub

1) I do not understand why I need to "rs.MoveFirst" after EACH delete?
I thought the code next should work (to delete the imported report
3-lind header):
rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Also, 2) the only text import code I could get to work is:
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError

I believe that the above code relies on SCHEMA.INI which has to be in the
"myPath" and has my TableDef information.

How could I import the same textfile correctly after defining my TableDef
with VBA?
(In short, I would prefer to do all in VBA and not have to concern re:
SCHEMA.INI)

' Create new table.
Set tdf = dbs.CreateTableDef("FIELDData")
' 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









Douglas J. Steele said:
You seem to be missing an opening square bracket in your SQL statement. As
well, I don't believe you can use My Documents as a location: you need an
actual location.

Grab the code from http://www.mvps.org/access/api/api0054.htm at "The Access
Web", and use it to determine the actual location of your My Documents
folder.

Dim strFolder As String

strFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL)

dbs.Execute "SELECT * INTO STARSData " & _
"FROM [Text;FMT=Fixed;HDR=Yes;DATABASE=" & _
strFolder & "\;].[STARSData#txt];", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

How about creating a new text file, then importing it?

Untested air-code:

Dim intFileIn as Integer
Dim intFileOut As Integer
Dim strBuffer As String
Dim strFileIn As String
Dim strFileOut As String

' Obviously you need to set your own file paths
' for strFileIn and strFileOut...

strFileIn = "C:\Folder1\Folder2\File.txt"
strFileOut = "C:\Folder1\Folder2\ModFile.txt"
intFileIn = FreeFile()
intFileOut = FreeFile()
Open strFileIn For Input As #intFileIn
Open strFileOut For Output As #FileOut

' Read (and ignore) the first three lines

Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer

' Read the rest of the data, writing it out again

Do While Not EOF(intFileIn)
Line Input #intFileIn, strBuffer
Print #intFileOut, strBuffer
Loop

Close #intFileIn
Close #intFileOut


To be honest, I'm surprised that you were able to delete from the recordset
at all. Connecting to text is usually read-only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
Thank you Doug for your time and knowledge,

Here is what seems to work: (NOTE: A few easy? questions below)

Sub NewAccessDatabase()
'
Dim appAccess As Access.Application
Dim tdf As Object, fld As Variant
Dim strDB As String
Dim dbs As DAO.Database
Dim rs As Recordset
Dim myFileName As String
Dim myPath As String
myPath = CurrentProject.Path & "\"

' Initialize string to database path.
strDB = myPath & "1492 Recon.mdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.11")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
Stop
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError
Set rs = dbs.OpenRecordset("FIELDData")
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete

Set appAccess = Nothing
End Sub

1) I do not understand why I need to "rs.MoveFirst" after EACH delete?
I thought the code next should work (to delete the imported report
3-lind header):
rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Also, 2) the only text import code I could get to work is:
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError

I believe that the above code relies on SCHEMA.INI which has to be in the
"myPath" and has my TableDef information.

How could I import the same textfile correctly after defining my TableDef
with VBA?
(In short, I would prefer to do all in VBA and not have to concern re:
SCHEMA.INI)

' Create new table.
Set tdf = dbs.CreateTableDef("FIELDData")
' 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









Douglas J. Steele said:
You seem to be missing an opening square bracket in your SQL statement.
As
well, I don't believe you can use My Documents as a location: you need an
actual location.

Grab the code from http://www.mvps.org/access/api/api0054.htm at "The
Access
Web", and use it to determine the actual location of your My Documents
folder.

Dim strFolder As String

strFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL)

dbs.Execute "SELECT * INTO STARSData " & _
"FROM [Text;FMT=Fixed;HDR=Yes;DATABASE=" & _
strFolder & "\;].[STARSData#txt];", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
Click to expand...
Click to expand...
 
E

EagleOne@microsoftdiscussiongroups

You ask a VG question.

It is by belief that I am deleting from the newly created .mdb file not the
text file.

The reason I say that, is that the .mdb file is short three "records"
"lines" whatever as compared to the .txt file from which it was created.

A "still open" queston is: What is an alternate import (to the Access .mdb
file) command either VBA or SQL which will permit me to create the "blank"
database in VBA and then import the data from the text file - without need of
the SCHEMA.INI file? (Not possible?)


***********************************


Douglas J. Steele said:
How about creating a new text file, then importing it?

Untested air-code:

Dim intFileIn as Integer
Dim intFileOut As Integer
Dim strBuffer As String
Dim strFileIn As String
Dim strFileOut As String

' Obviously you need to set your own file paths
' for strFileIn and strFileOut...

strFileIn = "C:\Folder1\Folder2\File.txt"
strFileOut = "C:\Folder1\Folder2\ModFile.txt"
intFileIn = FreeFile()
intFileOut = FreeFile()
Open strFileIn For Input As #intFileIn
Open strFileOut For Output As #FileOut

' Read (and ignore) the first three lines

Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer

' Read the rest of the data, writing it out again

Do While Not EOF(intFileIn)
Line Input #intFileIn, strBuffer
Print #intFileOut, strBuffer
Loop

Close #intFileIn
Close #intFileOut


To be honest, I'm surprised that you were able to delete from the recordset
at all. Connecting to text is usually read-only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
Thank you Doug for your time and knowledge,

Here is what seems to work: (NOTE: A few easy? questions below)

Sub NewAccessDatabase()
'
Dim appAccess As Access.Application
Dim tdf As Object, fld As Variant
Dim strDB As String
Dim dbs As DAO.Database
Dim rs As Recordset
Dim myFileName As String
Dim myPath As String
myPath = CurrentProject.Path & "\"

' Initialize string to database path.
strDB = myPath & "1492 Recon.mdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.11")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
Stop
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError
Set rs = dbs.OpenRecordset("FIELDData")
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete

Set appAccess = Nothing
End Sub

1) I do not understand why I need to "rs.MoveFirst" after EACH delete?
I thought the code next should work (to delete the imported report
3-lind header):
rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Also, 2) the only text import code I could get to work is:
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError

I believe that the above code relies on SCHEMA.INI which has to be in the
"myPath" and has my TableDef information.

How could I import the same textfile correctly after defining my TableDef
with VBA?
(In short, I would prefer to do all in VBA and not have to concern re:
SCHEMA.INI)

' Create new table.
Set tdf = dbs.CreateTableDef("FIELDData")
' 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









Douglas J. Steele said:
You seem to be missing an opening square bracket in your SQL statement.
As
well, I don't believe you can use My Documents as a location: you need an
actual location.

Grab the code from http://www.mvps.org/access/api/api0054.htm at "The
Access
Web", and use it to determine the actual location of your My Documents
folder.

Dim strFolder As String

strFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL)

dbs.Execute "SELECT * INTO STARSData " & _
"FROM [Text;FMT=Fixed;HDR=Yes;DATABASE=" & _
strFolder & "\;].[STARSData#txt];", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
message 2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Depending on the nature of your text file, the TransferText method may be
sufficient to import your data.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
You ask a VG question.

It is by belief that I am deleting from the newly created .mdb file not
the
text file.

The reason I say that, is that the .mdb file is short three "records"
"lines" whatever as compared to the .txt file from which it was created.

A "still open" queston is: What is an alternate import (to the Access
.mdb
file) command either VBA or SQL which will permit me to create the "blank"
database in VBA and then import the data from the text file - without need
of
the SCHEMA.INI file? (Not possible?)


***********************************


Douglas J. Steele said:
How about creating a new text file, then importing it?

Untested air-code:

Dim intFileIn as Integer
Dim intFileOut As Integer
Dim strBuffer As String
Dim strFileIn As String
Dim strFileOut As String

' Obviously you need to set your own file paths
' for strFileIn and strFileOut...

strFileIn = "C:\Folder1\Folder2\File.txt"
strFileOut = "C:\Folder1\Folder2\ModFile.txt"
intFileIn = FreeFile()
intFileOut = FreeFile()
Open strFileIn For Input As #intFileIn
Open strFileOut For Output As #FileOut

' Read (and ignore) the first three lines

Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer
Line Input #intFileIn, strBuffer

' Read the rest of the data, writing it out again

Do While Not EOF(intFileIn)
Line Input #intFileIn, strBuffer
Print #intFileOut, strBuffer
Loop

Close #intFileIn
Close #intFileOut


To be honest, I'm surprised that you were able to delete from the
recordset
at all. Connecting to text is usually read-only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
Thank you Doug for your time and knowledge,

Here is what seems to work: (NOTE: A few easy? questions below)

Sub NewAccessDatabase()
'
Dim appAccess As Access.Application
Dim tdf As Object, fld As Variant
Dim strDB As String
Dim dbs As DAO.Database
Dim rs As Recordset
Dim myFileName As String
Dim myPath As String
myPath = CurrentProject.Path & "\"

' Initialize string to database path.
strDB = myPath & "1492 Recon.mdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.11")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
Stop
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError
Set rs = dbs.OpenRecordset("FIELDData")
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete
rs.MoveFirst
rs.Delete

Set appAccess = Nothing
End Sub

1) I do not understand why I need to "rs.MoveFirst" after EACH delete?
I thought the code next should work (to delete the imported report
3-lind header):
rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Also, 2) the only text import code I could get to work is:
dbs.Execute "SELECT * INTO FIELDData FROM
[Text;FMT=Fixed;HDR=No;DATABASE=" & myPath & ";].[FIELDData#txt];",
dbFailOnError

I believe that the above code relies on SCHEMA.INI which has to be in
the
"myPath" and has my TableDef information.

How could I import the same textfile correctly after defining my
TableDef
with VBA?
(In short, I would prefer to do all in VBA and not have to concern re:
SCHEMA.INI)

' Create new table.
Set tdf = dbs.CreateTableDef("FIELDData")
' 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









:

You seem to be missing an opening square bracket in your SQL
statement.
As
well, I don't believe you can use My Documents as a location: you need
an
actual location.

Grab the code from http://www.mvps.org/access/api/api0054.htm at "The
Access
Web", and use it to determine the actual location of your My Documents
folder.

Dim strFolder As String

strFolder = fGetSpecialFolderLocation(CSIDL_PERSONAL)

dbs.Execute "SELECT * INTO STARSData " & _
"FROM [Text;FMT=Fixed;HDR=Yes;DATABASE=" & _
strFolder & "\;].[STARSData#txt];", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
message 2003

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

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

The issue revolved around which database is the CurrentDb

Below will create Recordset("STARSData") but in Tools.mdb
whereas I want the Recordset("STARSData") in 1492 Recon.mdb

This code creates a new db appAccess.NewCurrentDatabase strDB

But I cannot "SET" 1492 Recon.mdb 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"



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help!! Error 3111 7
Make Table in Back End using VBA code is not creating the table 3
Quickest method to take data from a table, modify it and put it in another table 5
Delete file but still in memory 5
Assigning Permissions to Temporary Database Tables 8
Type mismatch error after converting to Access 2007 2
Phantom Table 4
Creating New Field Dynamically 1

Top