VB TransferText Command difficulties 
Author Message
 VB TransferText Command difficulties

Greetings-

I am currently attempting to automate a process for
importing text files into an Access DB.  I need to import
about ten text files per day into my database.  Instead of
going through the process of importing each file
individually, I would like to be able to hit one button
and import the whole group.  Additionally, I would like to
import these files using one of the import specifications
that I already have saved.  My first inclination was to
use a macro, but it doesn't seem possible to process
multiple files either via a wildcard or anything else.  
This lead me to attempt the following command in vb:

Private Sub CommandLOAD_R1_Click()
DoCmd.TransferText
acImportFixed,"ImportSpecification","TargetTable","C:\filep
ath\*",,,
End Sub

(even when I pointed this to a specific file it didn't
work)

Any idea on how to accomplish this either by fixing the
above code or anything else?  



Sun, 01 Aug 2004 00:13:18 GMT  
 VB TransferText Command difficulties
Hello Brian,

Thank you for using the Microsoft Access Newsgroups!  My name is Barry, and
I will be assisting you with your issue.

If the number of Text files to be imported is Constant, as well as the
NAMES of the files being the same, then you could create a Table to store
the Path and Filename of each text file, as well as the Table name to store
the data, then use code similar to the following to Import them all into
the Database (Since you did not mention which version of Access you were
using, this is ADO syntax:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select MyTable, MyPath from MyTextFiles", cn

rs.MoveFirst
Do Until rs.EOF
    DoCmd.TransferText acImportFixed,"ImportSpecification", rs!MyTable,
rs!MyPath
    rs.MoveNext
Loop

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

The table is set up as follows:

Table Name:  MyTextFiles

Fields:  MyTable Text,  MyPath Text

"MyTable" is the Access Table Name that the data in the file in "MyPath" is
stored in.

I hope this information is useful.  Please respond by placing a post in the
Newsgroup if you need additional assistance.

Sincerely,

Barry Stephens
Microsoft Online Support Engineer

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Content-Class: urn:content-classes:message


| Subject: VB TransferText Command difficulties
| Date: Tue, 12 Feb 2002 08:13:18 -0800
| Lines: 25

| MIME-Version: 1.0
| Content-Type: text/plain;
|       charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
| Thread-Index: AcGz4C70PwjugC3RQHefhL34VpL+Ww==
| Newsgroups: microsoft.public.access.modulesdaovba
| NNTP-Posting-Host: TKMSFTNGXA09 10.201.226.37
| Path: cpmsftngxa08!cpmsftngxa07
| Xref: cpmsftngxa08 microsoft.public.access.modulesdaovba:83172
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| Greetings-
|
| I am currently attempting to automate a process for
| importing text files into an Access DB.  I need to import
| about ten text files per day into my database.  Instead of
| going through the process of importing each file
| individually, I would like to be able to hit one button
| and import the whole group.  Additionally, I would like to
| import these files using one of the import specifications
| that I already have saved.  My first inclination was to
| use a macro, but it doesn't seem possible to process
| multiple files either via a wildcard or anything else.  
| This lead me to attempt the following command in vb:
|
| Private Sub CommandLOAD_R1_Click()
| DoCmd.TransferText
| acImportFixed,"ImportSpecification","TargetTable","C:\filep
| ath\*",,,
| End Sub
|
| (even when I pointed this to a specific file it didn't
| work)
|
| Any idea on how to accomplish this either by fixing the
| above code or anything else?  
|



Sun, 01 Aug 2004 03:42:21 GMT  
 VB TransferText Command difficulties
Barry-

Thanks for your help on this!

To clarify my situation a little better-

I am working on MS Access 2000-SR1
The number of files that I will be processing is not
constant
I will also need to process text files that do not have
a .txt extension

Any further assistance would be much appreciated.

-Brian

Quote:
>-----Original Message-----
>Hello Brian,

>Thank you for using the Microsoft Access Newsgroups!  My
name is Barry, and
>I will be assisting you with your issue.

>If the number of Text files to be imported is Constant,
as well as the
>NAMES of the files being the same, then you could create
a Table to store
>the Path and Filename of each text file, as well as the

Table name to store
Quote:
>the data, then use code similar to the following to

Import them all into
Quote:
>the Database (Since you did not mention which version of
Access you were
>using, this is ADO syntax:

>Dim cn as ADODB.Connection
>Dim rs as ADODB.Recordset

>Set cn = CurrentProject.Connection
>Set rs = New ADODB.Recordset

>rs.Open "Select MyTable, MyPath from MyTextFiles", cn

>rs.MoveFirst
>Do Until rs.EOF
>    DoCmd.TransferText

acImportFixed,"ImportSpecification", rs!MyTable,
Quote:
>rs!MyPath
>    rs.MoveNext
>Loop

>rs.Close
>cn.Close

>Set rs = Nothing
>Set cn = Nothing

>The table is set up as follows:

>Table Name:  MyTextFiles

>Fields:  MyTable Text,  MyPath Text

>"MyTable" is the Access Table Name that the data in the

file in "MyPath" is
Quote:
>stored in.

>I hope this information is useful.  Please respond by

placing a post in the

- Show quoted text -

Quote:
>Newsgroup if you need additional assistance.

>Sincerely,

>Barry Stephens
>Microsoft Online Support Engineer

>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Get Secure! - www.microsoft.com/security

>--------------------
>| Content-Class: urn:content-classes:message


>| Subject: VB TransferText Command difficulties
>| Date: Tue, 12 Feb 2002 08:13:18 -0800
>| Lines: 25
>| Message-ID: <30bf01c1b3e0$2ef4dcd0


Quote:
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>|   charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
>| Thread-Index: AcGz4C70PwjugC3RQHefhL34VpL+Ww==
>| Newsgroups: microsoft.public.access.modulesdaovba
>| NNTP-Posting-Host: TKMSFTNGXA09 10.201.226.37
>| Path: cpmsftngxa08!cpmsftngxa07
>| Xref: cpmsftngxa08

microsoft.public.access.modulesdaovba:83172

- Show quoted text -

Quote:
>| X-Tomcat-NG: microsoft.public.access.modulesdaovba
>|
>| Greetings-
>|
>| I am currently attempting to automate a process for
>| importing text files into an Access DB.  I need to
import
>| about ten text files per day into my database.  Instead
of
>| going through the process of importing each file
>| individually, I would like to be able to hit one button
>| and import the whole group.  Additionally, I would like
to
>| import these files using one of the import
specifications
>| that I already have saved.  My first inclination was to
>| use a macro, but it doesn't seem possible to process
>| multiple files either via a wildcard or anything else.  
>| This lead me to attempt the following command in vb:
>|
>| Private Sub CommandLOAD_R1_Click()
>| DoCmd.TransferText
>|

acImportFixed,"ImportSpecification","TargetTable","C:\filep

- Show quoted text -

Quote:
>| ath\*",,,
>| End Sub
>|
>| (even when I pointed this to a specific file it didn't
>| work)
>|
>| Any idea on how to accomplish this either by fixing the
>| above code or anything else?  
>|

>.



Sun, 01 Aug 2004 04:49:26 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using the DoCmd.TransferText command from VB 4.0..

2. Command Bar & Button Difficulties

3. Difficulty with PictureSave() command.

4. TransferText command has broken

5. Access 2000, Export Text File - Using TransferText Command

6. TransferText command in VB5 for Access97

7. TransferText command in VB5 for Access97

8. fundamental question on the difficulty creating a simple database in VB

9. having difficulty establishing security using VB 4.0(16-bit) and Jet 2.5

10. Difficulties pasting SQL statements in VB editor.

11. Difficulties with Access Database in VB 6.0

12. vb shell command to replace dos command

 

 
Powered by phpBB® Forum Software