Importing comma delimited text file - need help automating

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

I have an application that everything is working great on with one
exception. I need to be able to have a text file (comma delimited) that has
only one record import into the database in an existing table upon a file
name prompt. Example:

Application user clicks on command button. It should prompt for a "customer
number" which is the file name with a "txt" extension. So, if the customer
# is 00224, then the file (00224.txt) is imported into the existing table
(T-Import).

All files that are imported will have no headers, have only one record, and
will have identical file layouts.

Currently, the command button has an "on click" event procedure that runs a
macro that has queries that manipulates the data to the way I need it. I
need it to import the data first. I can manually import the data (which is
on an RS6000 server) with no problem. Users will run this a combined
total of about 50 times per day, and only one week worth of data will be
saved.

Application: Access 2003 (data - Access 2002). Split database between
user's PC (front end with forms, reports, code, macros) and server (back end
for data)
OS: Windows 2000 Professional for front end databases and Windows 2000
Server for back end databases.

Any help would be appreciated.

-Joanne

email hcscjob *at^ pad &dot# net removing symbols
 
Hi Joanne,

This is fairly simple.

1) Use File|Get External Data|Import to import one of these files
manually. If you have to make any custom settings as you go, click the
Advanced... button and save an Import Specification.

2) In the command button's Click event procedure, put code like this:

Dim strFileName As String
Dim strPath As String

strPath = "D:\Folder\" 'the location of the files
strFileName = InputBox("Please enter the customer number")
If strFileName = "" Then
'User didn't enter the number
Exit Sub
End If

DoCmd.TransferText acImportDelim, "MySpecification", _
"T-Import", strPath & StrFileName, False

Possible improvements to the user interface:

*Make the code put up the File Open dialog so the user can select the
file rather than have to remember and type the customer number.

*Make the code pop up a form with a list of customers to choose from.

*Have the code import all files in the folder whose name is a valid
customer number
 
Thank you, John. That is exactly what I needed to get this working. It has
been a few years since I did any major database programming and learning new
stuff / relearning old stuff has been great - as has the
microsoft.public.access.* NGs!
-Joanne
 
Hi John

I'm new to VB coding. How would you implement making the File Open dialog appear as you suggested to Joanne

Thanks
Erlyn
 
Back
Top