Using External Text Files (ETF)

[Cipher via AccessMonster.com]

I have several External Text FIles (ETF) and several Forms to process the
data. There are MANY things about this application that you will not be aware
of, so to tell me to do it all different or to ask..."why are you doing it
that way" is of no value to me. What I need to know is how can I accomplish
the following:

1) I have ETF's, Tables, Forms and Queries. When the customer opens a Table
in the database, he is not looking at the latest data because Access does not
reload the data in the table when it is opened. I cannot use LINKED data
because Access will not allow me to edit the data in a Linked File.

2) I have Saved Imports Operations and Saved Export Operations but I do not
want the customer to use anything else than the designed Form because of
their innexperience with Access.

3) I am familiar with VBA for Access, but lack a complete source of
information on the programming features ov VBA even though I havev purchased
all eleven books on the subject from Amazon.com.

4) I need a specific answer to the following please:
a) If I place a Command Button on the Form to re-import ETF data into a Table,
what type of process should I use and where can I obtain the appropriate
syntax.
b) As I need to have these ETF indexed in the Form, but I must remove the
INDEX firld from the table BEFORE it is Exported back into the ETF, I have
formed a Query because Access will not allow me to choose what fields are
selected for the Export Process.
c) I need to place an EXPORT DATA Command Button on the Form to send data
from the Query back to the ETF when the Table has been edited using the Form.
This will also require that the Query is updated before the EXPORT proceedure
is executed.


Thanks for the help

 

[Clifford Bass]

Hi,

That all sounds pretty good. Although, technically you do not need any
indexes on a table. You can still edit it. You will want to use the
DoCmd.TransferText from VBA to do the import and the export. Search Access's
help for "transfertext" to get the details. Or, if you already have the
processes in macros, just specify the appropriate macro in the button's On
Click event property. If you shift to indexless tables you can avoid the
need to use and/or modify the queries.

Hope that helps,

Clifford Bass

 

[Cipher via AccessMonster.com]

Hi Clifford:

Thank you for the information. I have heard of TransferText before but I
don't have a good reference book for the syntax for all these functions. I
will look in the HELP. Is there a really good list of all the functions for
2007 available on the web, what I need is a Directory of Functions with
descriptions and sample code. Is there such a book that you know?

Again, thanks.

Hi,

That all sounds pretty good. Although, technically you do not need any
indexes on a table. You can still edit it. You will want to use the
DoCmd.TransferText from VBA to do the import and the export. Search Access's
help for "transfertext" to get the details. Or, if you already have the
processes in macros, just specify the appropriate macro in the button's On
Click event property. If you shift to indexless tables you can avoid the
need to use and/or modify the queries.

Hope that helps,

Clifford Bass

I have several External Text FIles (ETF) and several Forms to process the
data. There are MANY things about this application that you will not be aware

[quoted text clipped - 29 lines]

Thanks for the help

 

[Cipher via AccessMonster.com]

As I am using Access 2007, the Microsoft website thates "...Unfortunatly,
Access 2007 VBA code doesn't recognize "Saved Specifications", so
unfortunatly, the suggestion of TransferText is no use to me (thank you
Microsoft). Please answer these specific questions.

1) If I load all the records into a table and export back to the table in VBA,
please give me a hint as to how to start, what functions or VB Code should I
investigate.

2) In VBA, how can I Re-Load a table

3) In VBA, hoe can I export a Query

Thanks Clifford

Hi,

That all sounds pretty good. Although, technically you do not need any
indexes on a table. You can still edit it. You will want to use the
DoCmd.TransferText from VBA to do the import and the export. Search Access's
help for "transfertext" to get the details. Or, if you already have the
processes in macros, just specify the appropriate macro in the button's On
Click event property. If you shift to indexless tables you can avoid the
need to use and/or modify the queries.

Hope that helps,

Clifford Bass

I have several External Text FIles (ETF) and several Forms to process the
data. There are MANY things about this application that you will not be aware

[quoted text clipped - 29 lines]

Thanks for the help

 

[Clifford Bass]

Hi,

Sort of. This is what I am aware of. There could be books or web
sites that have a lising that is purely alphabetical. I do not remember if I
have come across any.

One way is to use the online help from the VBA Editor. Instead of
searching, go to the Help menu and choose the top item. Click on "Visual
Basic for Applications Language Reference" and then "Visual Basic Language
Reference". For intrinsic functions such as Abs() you can choose
"Functions". For functions and methods of the various types of Access
objects go back up a level and choose "Access Object Model Reference". You
will find a nice hierarchical map if you click on "Object Model Map".

Another way is, while in the VBA Editor to press F2. This will let you
browse through all the objects that are currently available. It does let you
search for particular text. Once you find the command you want (i.e. DoCmd
and TransferText), you can click on to to see its parameters. And for more
detail, if help can locate it, you can click on the ? icon.

Hoping this is of some help,

Clifford Bass

 

[Clifford Bass]

Hi,

That web site is wrong. You can use the TransferText with saved
specifications. I have been doing it with other things, although not lately.
SoI just tested it in Access 2007 and it works for me. Here is the code I
used, which I trust will also answer your questions:

Public Sub ImportExportText()

' If the table exists, you will either want to empty it (shown here) or
you could
' delete it.
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tblImportMe"
DoCmd.SetWarnings True

' This imports into the named table, using the specified saved import
specification.
' If the table does not exist, it creates it. Otherwise it appends to
the table.
DoCmd.TransferText acImportDelim, "ImportMe Import Specification",
"tblImportMe", _
Environ("USERPROFILE") & "\Desktop\ImportMe.txt", True

' This exports the named table, using the specified saved export
specification.
' If the file exists it overwrites it.
DoCmd.TransferText acExportDelim, "tblImportMe-Exported Export
Specification", _
"tblImportMe", Environ("USERPROFILE") &
"\Desktop\ImportMe-Exported.txt", True

End Sub

To export a query, just use the query name instead of the table name.

Hope that helps. If not, let me know.

Clifford Bass

 

[Cipher via AccessMonster.com]

Wonderful Clifford, this really helps...Thank you.

Hi,

Sort of. This is what I am aware of. There could be books or web
sites that have a lising that is purely alphabetical. I do not remember if I
have come across any.

One way is to use the online help from the VBA Editor. Instead of
searching, go to the Help menu and choose the top item. Click on "Visual
Basic for Applications Language Reference" and then "Visual Basic Language
Reference". For intrinsic functions such as Abs() you can choose
"Functions". For functions and methods of the various types of Access
objects go back up a level and choose "Access Object Model Reference". You
will find a nice hierarchical map if you click on "Object Model Map".

Another way is, while in the VBA Editor to press F2. This will let you
browse through all the objects that are currently available. It does let you
search for particular text. Once you find the command you want (i.e. DoCmd
and TransferText), you can click on to to see its parameters. And for more
detail, if help can locate it, you can click on the ? icon.

Hoping this is of some help,

Clifford Bass

Hi Clifford:

[quoted text clipped - 5 lines]

Again, thanks.

 

[Cipher via AccessMonster.com]

Hi CLifford:

Well, there had to be problems...Here is the code I used:

Private Sub Load_Data_Click()

DoCmd.SetWarnings False
DoCmd.RunSQL "delete from [Customer List]"
DoCmd.SetWarnings True

DoCmd.TransferText acImportDelim, "Customer_List", "Customer List", "C:\
WellTech\Disposal\TextFiles\Customer List.txt", True

End Sub

The name of the Saved Import Specification is: 'Customer_List'
The name of the text file is: 'Customer List.txt'
The Path to the file is: 'C:\WellTech\Disposal\TextFiles\Customer List.txt'

but I get the error: 3625...The text file specification 'Customer_List' does
not exist

Of course, it does and I use it to reload the external data back into the
Table. The 'Delete from Customer List' works perfect...is it necessary for
the [] if there is a space in the name?

It might be that my Access revision does not have the fix, what version
number are you running?

I really appresiate all your help Clifford...thank you.

Hi,

That web site is wrong. You can use the TransferText with saved
specifications. I have been doing it with other things, although not lately.
SoI just tested it in Access 2007 and it works for me. Here is the code I
used, which I trust will also answer your questions:

Public Sub ImportExportText()

' If the table exists, you will either want to empty it (shown here) or
you could
' delete it.
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tblImportMe"
DoCmd.SetWarnings True

' This imports into the named table, using the specified saved import
specification.
' If the table does not exist, it creates it. Otherwise it appends to
the table.
DoCmd.TransferText acImportDelim, "ImportMe Import Specification",
"tblImportMe", _
Environ("USERPROFILE") & "\Desktop\ImportMe.txt", True

' This exports the named table, using the specified saved export
specification.
' If the file exists it overwrites it.
DoCmd.TransferText acExportDelim, "tblImportMe-Exported Export
Specification", _
"tblImportMe", Environ("USERPROFILE") &
"\Desktop\ImportMe-Exported.txt", True

End Sub

To export a query, just use the query name instead of the table name.

Hope that helps. If not, let me know.

Clifford Bass

As I am using Access 2007, the Microsoft website thates "...Unfortunatly,
Access 2007 VBA code doesn't recognize "Saved Specifications", so

[quoted text clipped - 10 lines]

Thanks Clifford

 

[Clifford Bass]

Hi,

You are welcome!

Well, I think I see the issue. But first, to answer your bracket
question. Yes, in the RunSQL method, they are required due to the space in
the table name. They are not needed in the TransferText method because that
can only be either a table or a query.

I think what is going on is that there are "Saved Imports" and "Saved
Exports" and there are (saved) "Import Specifications" and (saved) "Export
Specifications".

If you are using saved imports and exports there is a new command that
runs those:

DoCmd.RunSavedImportExport "My Saved Import or Export Name"

Which means there is now even another way to do imports and exports.
Try that instead of the DoCmd.TransferText.

Or, to use the TransferText method, you can define your import and
export specifications by doing the import and export manually. When you get
into any of several of the Wizard's dialogs, you will see an Advanced...
button near the bottom left. Click it. You will get another dialog on which
you can set a number of options. Do so as needed and then click on the Save
As... button. The name you enter here will be the name to use in the
TransferText method. I am not completely sure, but you may need separate
ones for import and export.

Let me know if this gets you where you need to be.

Clifford Bass

 

[Cipher via AccessMonster.com]

Clifford:

WONDERFUL...I finally have a solution, thank you VERY much. I had a little
difficulty with the FORM going blank when the data was saved (caused by the
table being emptied) and "Repaint" did not work, so I closed the FORM and
then Re-Opened it. Brute force, but it works fine. The only thing left is to
REPORT the whole Database content for each Table, which should not be
difficult (LOL). Again, thanks Clifford.

Paul

Hi,

That web site is wrong. You can use the TransferText with saved
specifications. I have been doing it with other things, although not lately.
SoI just tested it in Access 2007 and it works for me. Here is the code I
used, which I trust will also answer your questions:

Public Sub ImportExportText()

' If the table exists, you will either want to empty it (shown here) or
you could
' delete it.
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tblImportMe"
DoCmd.SetWarnings True

' This imports into the named table, using the specified saved import
specification.
' If the table does not exist, it creates it. Otherwise it appends to
the table.
DoCmd.TransferText acImportDelim, "ImportMe Import Specification",
"tblImportMe", _
Environ("USERPROFILE") & "\Desktop\ImportMe.txt", True

' This exports the named table, using the specified saved export
specification.
' If the file exists it overwrites it.
DoCmd.TransferText acExportDelim, "tblImportMe-Exported Export
Specification", _
"tblImportMe", Environ("USERPROFILE") &
"\Desktop\ImportMe-Exported.txt", True

End Sub

To export a query, just use the query name instead of the table name.

Hope that helps. If not, let me know.

Clifford Bass

As I am using Access 2007, the Microsoft website thates "...Unfortunatly,
Access 2007 VBA code doesn't recognize "Saved Specifications", so

[quoted text clipped - 10 lines]

Thanks Clifford

 

[Clifford Bass]

Hi Paul,

Hurrah! You are welcome.

Clifford Bass

 

[Cipher via AccessMonster.com]

Access hasnt finished with me yet...another problem has appeared:
When I load the various tables the first time, Access adds an INDEX field
(which is what I want). When I invoke the code...[ 'Load Delivery_Data
Table
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from Delivery_Data"
DoCmd.SetWarnings True
DoCmd.TransferText acImportDelim, "Delivery_Data Import Specification",
"Delivery_Data", "C:\WellTech\Disposal\DeliveryData\Delivery_Data.txt", False]


it reloads the data from the external text files, but whan it recomputes the
INDEX field, it appends the record number previous. Example, the first load
to the Table creates INDEXEX 1,2,3,4,5. When the Code Above is run, the
INDEXEC are 6,7,8,9,10.

I obviously need some kind of ID=1 before the Table is loaded again, but I
have no idea of the syntax or what it would be called...HELP!

Hi Paul,

Hurrah! You are welcome.

Clifford Bass

Clifford:

[quoted text clipped - 6 lines]

Paul

 

[Clifford Bass]

Hi Paul,

Is this is an autonumber field? If so, it is not really necessary to
restart the numbering. However, if you do want to you can do this after the
deletion of all of the records:

DoCmd.RunSQL "insert into Delivery_Data " & _
"(AutoField, Field2, Field3, Field4) " & _
"values " & _
"(0, ""text"", 3, #3/3/2003#)"

Where AutoField is your autonumber field and the other fields are all
the fields that require something. In the values clause the zero, which goes
into the autonumber field, tells Access to restart the numbering, using the
number following the specified number. You will need to specify some valid
value for each of those other required fields. I used the line continuation
so as to keep it readable in the newsgroup. Follow that by another delete to
delete the row just added.

Clifford Bass

 

[Cipher via AccessMonster.com]

Clifford:

If I press the "Saved Imports" button on the Access Ribon, the tables load
correctly and the Autonumber field is calculated correctly. If I use the code:


DoCmd.RunSavedImportExport "My Saved Import or Export Name"...will it be the
same as pressing the button? If so, is this easier to use as I have 18
columns of data in one or the External Text Files.

I am sorry for not knowing the answers to these questions and asking you so
many questions, but I do appreciate you Clifford...thank you.

Hi Paul,

Is this is an autonumber field? If so, it is not really necessary to
restart the numbering. However, if you do want to you can do this after the
deletion of all of the records:

DoCmd.RunSQL "insert into Delivery_Data " & _
"(AutoField, Field2, Field3, Field4) " & _
"values " & _
"(0, ""text"", 3, #3/3/2003#)"

Where AutoField is your autonumber field and the other fields are all
the fields that require something. In the values clause the zero, which goes
into the autonumber field, tells Access to restart the numbering, using the
number following the specified number. You will need to specify some valid
value for each of those other required fields. I used the line continuation
so as to keep it readable in the newsgroup. Follow that by another delete to
delete the row just added.

Clifford Bass

Access hasnt finished with me yet...another problem has appeared:
When I load the various tables the first time, Access adds an INDEX field

[quoted text clipped - 13 lines]

I obviously need some kind of ID=1 before the Table is loaded again, but I
have no idea of the syntax or what it would be called...HELP!

 

[Clifford Bass]

Hi Paul,

I cannot say for sure as I have not really used the Saved Imports
feature. But doing it from code should be the same as doing it by clicking.
Whichever way works the easiest and best for you is the way to go. Access
often provides many avenues to accomplish the same thing.

Apology not needed, but accepted in the spirit given! And you are
welcome!

Clifford Bass

 

[Cipher via AccessMonster.com]

Hi Clifford:

I tried my suggestion with the following results (all not good):
1) When I press the "Saved Imports" buttom, it only work properly if the Form
and Table that uses this information is closed.

2) If I use the following code:
DoCmd.Close acTable, "Inject_Cycle"
DoCmd.RunSavedImportExport (Import - Inject_Cycle)
I get the following error message: {Run-time error 31602...The specification
with the specified index does not exist. Specify a different index/'0'.}.

The example in the HELP tells me use (....), but I see you use "..." in these
code examples, which should be used. If I use:
DoCmd.RunSavedImportExport 'Import - Inject_Cycle'
nothing happens and no error message.

So it would appear that the code: DoCmd.RunSavedImportExport (Import -
Inject_Cycle) and pressing the "Saved Imports" button are NOT the same at all.
Even though the HELP says that it does the same thing.

A seperate question:
If I want to edit a Form that is configured as a Split Form and I want to
change the Column Names on what appears to be the Table. If I have a new
Table with different names, how can I IMPORT the new table into the Split
Form. I have columns that have different names now and they do not match. It
would be a much better idea for Microsoft to use the Table design for the
Split Form, this way they would always be the same...how can I change column
names on the SPlit Form?

Hi Paul,

I cannot say for sure as I have not really used the Saved Imports
feature. But doing it from code should be the same as doing it by clicking.
Whichever way works the easiest and best for you is the way to go. Access
often provides many avenues to accomplish the same thing.

Apology not needed, but accepted in the spirit given! And you are
welcome!

Clifford Bass

Clifford:

[quoted text clipped - 7 lines]

I am sorry for not knowing the answers to these questions and asking you so
many questions, but I do appreciate you Clifford...thank you.

 

[Clifford Bass]

Hi Paul,

For #2, the documentation always(?) uses the parantheses to indicate
the parameters because the fuction/method is defined using parantheses. If
you write your own subroutine it looks something like this:

Public Sub MySub(MyParameter1 As String, MyParameter2 As Integer)

But when you call it you do not use the parantheses:

MySub "abc", 123

However, to keep you on your toes, if you have a function:

Public Function MyFn(MyParameter1 As String, MyParameter2 As Integer) As Long

You do use the parantheses:

ReturnValue = MyFn("def", 456)

Unless you do not care about the return value:

MyFn "ghi", 789

So, since the DoCmd.RunSavedImportExport does not return a value (i.e.
is not a function) you do not use the parantheses:

DoCmd.RunSavedImportExport "My Saved Export Name"

And, if you make the shift to VB .NET, you then always have to use the
paranetheses. How is all that for confusing?

I think the use of the apostrophes should be fine. Curious that it did
not work. Did you check to see if there was an import error table created?

For the separate question on the split form, I am not able to answer
that as most of what I do still has to work with Access 2003 and therefore
cannot use the split forms. So I have not done anything with them other than
a brief look. Maybe post that as a separate question in the forms newsgroup.

Clifford Bass

 

[Cipher via AccessMonster.com]

Example1: DoCmd.Close acTable, "Inject_Cycle"
Example2:
DoCmd.RunSavedImportExport "Import-Inject_Cycle"

In #2, there is no need for a comma. In #1 there is...is this not an
inconsistancy?

If I delete the comma in #1, I get a compile error. If I ADD a comma in #2, I
get a missing argument error.

I have figured out the other error...It is necessary to have the INDEX field
on the Form because when the table is reloaded, there must be a place for the
INDEX to reside...makes sense.

Simple Task:
I know that you are as busy as I am CLifford, but I would like to run a test
with you.
1) I made a Form with SINGLE Command Button

2) The code it ran was:
DoCmd.RunSavedImportExport "Import-Inject_Cycle"
Beep

When the button is pressed, I get a beep but the Table is NOT reloaded with
the new data. If I press the Saved Imports button on the External Data Tab,
the new data IS loaded. Even though I do not get an error, the command just
does not work, so there must be something about the call DoCmd.
RunSavedImportExport "Import-Inject_Cycle" that is missing, even though I Cut-
&-Past the procedure name so there were no mistakes.

If you cannot see any cause I will have to abbandon this approach...thanks
Clifford.

Hi Paul,

For #2, the documentation always(?) uses the parantheses to indicate
the parameters because the fuction/method is defined using parantheses. If
you write your own subroutine it looks something like this:

Public Sub MySub(MyParameter1 As String, MyParameter2 As Integer)

But when you call it you do not use the parantheses:

MySub "abc", 123

However, to keep you on your toes, if you have a function:

Public Function MyFn(MyParameter1 As String, MyParameter2 As Integer) As Long

You do use the parantheses:

ReturnValue = MyFn("def", 456)

Unless you do not care about the return value:

MyFn "ghi", 789

So, since the DoCmd.RunSavedImportExport does not return a value (i.e.
is not a function) you do not use the parantheses:

DoCmd.RunSavedImportExport "My Saved Export Name"

And, if you make the shift to VB .NET, you then always have to use the
paranetheses. How is all that for confusing?

I think the use of the apostrophes should be fine. Curious that it did
not work. Did you check to see if there was an import error table created?

For the separate question on the split form, I am not able to answer
that as most of what I do still has to work with Access 2003 and therefore
cannot use the split forms. So I have not done anything with them other than
a brief look. Maybe post that as a separate question in the forms newsgroup.

Clifford Bass

Hi Clifford:

[quoted text clipped - 25 lines]

Split Form, this way they would always be the same...how can I change column
names on the SPlit Form?

 

[Cipher via AccessMonster.com]

I have found the error...so simple, but soooo anoying. Just by chance, I
closed all of my applications and then ran the Access database again...AND IT
WORKED. I narrowed it down to Notebook being open to the file I was loading.
When I closed Notebook (and no process was running) it all worked. Now, even
though I had DoCmd.SetWarnings True, there was NO error message.

It looks like I am off-to-the-racs now, I will give you a heads-up when it is
all done...just for completeness...thanks again.

Hi Paul,

For #2, the documentation always(?) uses the parantheses to indicate
the parameters because the fuction/method is defined using parantheses. If
you write your own subroutine it looks something like this:

Public Sub MySub(MyParameter1 As String, MyParameter2 As Integer)

But when you call it you do not use the parantheses:

MySub "abc", 123

However, to keep you on your toes, if you have a function:

Public Function MyFn(MyParameter1 As String, MyParameter2 As Integer) As Long

You do use the parantheses:

ReturnValue = MyFn("def", 456)

Unless you do not care about the return value:

MyFn "ghi", 789

So, since the DoCmd.RunSavedImportExport does not return a value (i.e.
is not a function) you do not use the parantheses:

DoCmd.RunSavedImportExport "My Saved Export Name"

And, if you make the shift to VB .NET, you then always have to use the
paranetheses. How is all that for confusing?

I think the use of the apostrophes should be fine. Curious that it did
not work. Did you check to see if there was an import error table created?

For the separate question on the split form, I am not able to answer
that as most of what I do still has to work with Access 2003 and therefore
cannot use the split forms. So I have not done anything with them other than
a brief look. Maybe post that as a separate question in the forms newsgroup.

Clifford Bass

Hi Clifford:

[quoted text clipped - 25 lines]

Split Form, this way they would always be the same...how can I change column
names on the SPlit Form?

 

[Clifford Bass]

Hi Paul,

Numbers 1 and 2 are consistent. There has to be a way to identify
separate arguments. In VBA (and VB and a lot of other languages) that way is
with a comma. Good old DOS Batch language uses just a space. Anyway, if you
add a comma without an actual argument, it will complain, with the exception
of optional arguments.

Clifford Bass