VBA/Newbie DB Setup w/ Automated Importing

[AC5FF]

Hello all; I'm posting this here because I think VBA or Module help will be the easiest way to do what I need to do; but I have ZERO knowledge on using VBA or Modules ...

The easiest part here; is that I am creating something completely new and from scratch so I don't have to work around pre-set tables/formats/etc and can literally build this DB how I want. The issue that I am going to have is getting my data into the database automated. I am trying to eliminate work here and stop people from having to sit at an excel spreadsheet and enter all of this data by hand. Once the data is into Access I am pretty good at manipulating it with queries and creating reports to print out exactly the data that I will need to retrieve.

So; on to the grit of my request. I am needing help to import data from a file into a table. My current system reads in data from external modules and creates saved files as: TXUSE0707.11 where the TXUSE portion is always the same and the 0707.11 is MMDD.YY that the module data is retrieved.

Here is actual data from one of these files:
(actual file would not upload due to the file extention)

AC5FFTMP HIGHEST COUNT INFO
07-07-2011

029-001-01  00-02  .z.z.h.z.z.z.z.z.z.z|z.z.z.z.z.z.-.-.-.-|-.-.-.-.-.-.-.-.-.-|-.-.-.-.-.-.    .15 .    .    .    .    . 
029-001-02  00-00  .z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.h.z.z|z.z.z.z.z.z.    .36 .    .    .    .    . 
029-001-03  00-00  .z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.z.h.z|z.z.z.z.-.-.-.-.-.-|-.-.-.-.-.-.    .24 .    .    .    .    . 
029-001-04  00-01  .z.z.z.z.z.z.h.z.z.z|z.z.z.z.z.z.z.h.z.z|z.z.z.z.-.-.-.-.-.-|-.-.-.-.-.-.    .22 .    .    .    .    .

Left to right the data tells me:
029-001 = Account Number (will always be XXX-XXX)
-01 = System Number (will always be -XX)
00-02 = Unneeded bits - will ignore this information
.z.z.h.E.-. ... etc = data to save. The charicters between the "." are data where the periods are spacers. There is always 36 columns of data.
.XX . = is the total number of data points for this particular account/system


Initial thoughts of difficulties... The outfiles that my system creates with the modules saves the files as mentioned above. Access itself can't import from an unknown file type. These files may have to be changed to .TXT files. However, the data in the files are exactly as I have pasted above.
Also; the file name changes every time. If I read in my modules today I get a file name of TXUSE0715.11. If I read in a second group of modules today I will get TXUSE0715a.11 - and so forth. I don't want to duplicate imports and I don't want to miss imports into the DB. Both of which could mess up the data accuracy in the end... Could the file name be stored in the DB as well and checked for duplicates before import?

The date information at the top of the data is the date that the module was read. That will have to be entered in to each line. Although this should be easy enough to do - just have to run an update query after import.

So, am i looking at something extremely difficult to do here? I had envisioned a DB with an "Import Now" button that when pressed would look for the a file name with today's date, or the one with the latest date/time stamp?

Now the end all - beats all question. Have I bitten off more than I can chew or can something like this easily be done??

THANKS!

 

[slw]

I am also a newbie, but I dealt with a somewhat similar situation a couple of weeks ago. The following code doesn't do exactly what you are looking to do, but may give you some ideas. The code is invoked by clicking a button on the form. Here is what it does:

• prompts the user to select the directory (directory 1) where the import files are located (the originating application always posts to this directory)
• For each file in directory 1 one by one,
  • copies the file to a "processed" directory (directory 2) to archive it
  • renames the file as "nextimport.txt" in directory 1 (The files output by the originating application are text files, like yours, but have an extension Access does not recognize. Renaming the file takes care of that.)
  • runs a pre-defined Access import named "Allied" which is designed to import files named "nextimport.txt" from directory 1
  • deletes the current "nextimport.txt" file (the file now only resides in directory 2 where it was copied in a previous step, so it won't be imported again in the future from directory 1)
  • loops to the next file in directory 1 if there is one.

Again, I'm pretty new to Access VBA, so there may be better ways to accomplish this kind of import.

Private Sub Command75_Click()

Dim strPath As String
Dim strFile As String

On Error Resume Next

' ********Let user select a folder ********
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "You didn't select a folder", vbExclamation
Exit Sub
End If
End With

If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

' ********Loop through the text files ********
strFile = Dir(strPath & "*.*")
Do While strFile <> ""
FileCopy strFile, "Z:\postings\processed\" & strFile
Name strFile As "nextimport.txt"
' Import text file
DoCmd.RunSavedImportExport "Allied"
Kill "nextimport.txt"
strFile = Dir
Loop

End Sub

 

[AC5FF]

Thanks SLW... I will have to give this a try. From the way I am reading this - it'll help me with importing a different file name every time. That will help to solve half of the issue...

Next part is to get the data into a DB correctly. I've played with stuff like this in the past and never had any luck.... I know it should be able to be done - just a matter of how.

 

[AC5FF]

Okay ... With my limited to zero knowledge of VB I thought I would cut/paste your information into the event proceedure for a button I created. No Go. Didn't work at all. Not sure where/why yet, still troubleshooting.

However, I did play around a little this morning and remembered a trick I used a long time ago with import specifications... So, I was able to import my data into a table just as I need it to be. Works like a champ. (after renaming my data files to .TXT)

But I will need help now automating it. ... I.E. From a button click. I can go to file/get external data, follow through the wizard to choose my saved 'import specifications' and it works fine - but I can't expect the users to do this. I need to make it simplified for them...