Create column during Access import

fizzlefry
fizzlefry used Ask the Experts™
on
I am creating a reporting program in Access 2007.  I have a ASCII flatfile that I need to break-up and import into Access.  The file is a mixture of 4 pieces of information:  wholesaler, retailer, sales, and items.  What I need to do is report on the retailer sales, which is easy.  But also I need to report on the wholesaler information.  Currently, there is nothing in my 4 files that links back to the wholesaler.  What I want to do is insert a column of text, in all 3 tables, that links to the wholesaler number (unique).  Here's what I have:

My program to break-up my flatfile (thanks lewisq).  This runs externally and is called to run from Access:
DIM strTemp AS STRING

OPEN "test" FOR INPUT AS #1
OPEN "HID.txt" FOR OUTPUT AS #2
OPEN "BID.txt" FOR OUTPUT AS #3
OPEN "SID.txt" FOR OUTPUT AS #4
OPEN "PUR.txt" FOR OUTPUT AS #5

DO WHILE NOT EOF(1)
  LINE INPUT #1, strTemp
  IF LEFT$(strTemp, 3) = "HID" THEN PRINT #2, strTemp
  IF LEFT$(strTemp, 3) = "BID" THEN PRINT #3, strTemp
  IF LEFT$(strTemp, 3) = "SID" THEN PRINT #4, strTemp
  IF LEFT$(strTemp, 3) = "PUR" THEN PRINT #5, strTemp
LOOP

CLOSE #1, #2, #3, #4, #5

My code in Access to handle the import (started by a button click):
Private Sub ffMulti_Click()
'runs my external program'
Shell "C:\MSA REPORT\TEST.EXE"

Pause (2)

'dirty check to make sure it split the flatfile properly'
If Dir("C:\MSA REPORT\PUR.txt") <> "" Then
MsgBox "File Split Complete", vbOKOnly, "Done!"
Else: MsgBox "File Split Failed.  Contact Support.", vbOKOnly, "Error!"
End If

'imports my 3 separated text files'
DoCmd.TransferText acImportFixed, "BID Import Specification", "tblITEMS", "C:\MSA REPORT\BID.txt"
DoCmd.TransferText acImportFixed, "SID Import Specification", "tblRetailers", "C:\MSA REPORT\SID.txt"
DoCmd.TransferText acImportFixed, "PUR Import Specification", "tblSales", "C:\MSA REPORT\PUR.txt"

MsgBox "Flat File imported successfully.", vbOKOnly, "Done!"

End Sub

The text value I need to insert in in the HID.txt file.  My HID file contains something like:
HID00123456TOB W2011043Company Name... etc...
I need to take that number after HID, and insert it into an open column in my 3 tables that house retailer, sales, and item information (but it needs to be done at the time or either separation or import, so it's linked properly).  I'm not sure what point would be the best to insert the text.  I don't know if it would be better (or possible) to insert upon splitting the program or upon importing.  

I could really use some expert help here.  I would appreciate any and all help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I apologize if the point value did not equate the effort.  I'm really at a loss and could use the help.
Serge FournierAnalyst Programmer

Commented:
i hope you are good in vbs

here is an example of adox database creation from a vbscript

it also chek for a primary key and create it if none exist




if bready=false and resbutlefstr="" and reskeymid<>27 and resbutmidstr<>"cancel" then
         '=== all value were validated, we continue
         dbnam = dbnam & ".mdb"
      
         '=== chek if the file already exist
         Set objFolder2 = objFSO.GetFolder(basedir)'=== dir
         Set objFiles2 = objFolder2.files '=== fichiers

         found=0
         For Each objFile3 in objFiles2
            nomfile=objfile3.name
            nomfile=lcase(nomfile)
            if nomfile=dbnam then
               found=1
            end if
         next  
         if found=1 then
            a = clefra(array("fmid"))
            fbot.WriteLn("The file: <br>" & dbnam & "<br>Already exist<br><br>Please use EDIT DATABASE to manage it<br><br>")
            fbot.WriteLn("FIN<br><br>")
         
            fbot.WriteLn("LIST of table in the existing database:<br><br>")
            Set objcat = CreateObject("ADOX.Catalog")
            Set objcon = CreateObject("ADODB.Connection") 
            constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbnam
            objcon.open constr
            objcat.activeconnection=(objcon)
            for each tab in objcat.Tables
               If tab.Type = "TABLE" Then
                  fbot.WriteLn("<br>TABLES:<br><br>")
                  fbot.WriteLn(tab.name & "<br>")
                  fbot.WriteLn("<br>COLUMNS:<br><br>")
                  for each col in tab.columns
                     c = lcase(col.name)
                     fbot.WriteLn(c & "<br>Type: " & col.type & "&nbsp&nbsp&nbspAutoincrement: " & col.Properties("AutoIncrement") & "<br>")
                  next
               end if
            next
            objcon.close
            set objcat=nothing
            set objcon=nothing
         else
            a = clefra(array("fmid"))
            '=== formats: jet10 = 1 Jet11 = 2 Jet20 = 3 Jet3x = 4 Jet4x = 5 (Access 2000)
            Dim Catalog
            fbot.WriteLn("Creation ADOX catalog (adox.dll, access required)<br><br>")
         
            Set objcat = CreateObject("ADOX.Catalog")
            format = 5
            fbot.WriteLn("database creation: " & dbnam & "<br><br>")
         
            constr="Provider=Microsoft.Jet.OLEDB.4.0;" & "Jet OLEDB:Engine Type=" & Format & ";Data Source=" & dbnam
            objcat.Create constr

            '=== add a table in the database
            fbot.WriteLn("table creation: " & tabnam &"<br>")
            Set objtab = CreateObject("ADOX.table")
            objtab.name = tabnam
            objcat.Tables.Append objtab
            objcat.Tables.refresh
         
            '=== add a columns in the only existing table
            for each tab in objcat.Tables
               If tab.Type = "TABLE" Then
            
                  fbot.WriteLn("table existing: " & tab.name & "<br>")
                  '=== add column in database
                  Set objcol = Nothing
                  colnam="codintsef"
                  a=crecol(tab,colnam)

                  fbot.WriteLn("primary key creation: " & colnam & "<br>")
                  Set objkey = CreateObject("ADOX.key")
                  objkey.name="Primary"
                  objkey.columns.append colnam
                  tab.keys.append objkey
               
                  'Set aIndex = New ADOX.Index
                  'aIndex.Name = "ByField2"
                  'aIndex.Clustered = False
                  'aIndex.Columns.Append "Field2"
                  'aIndex.Columns.Append "Field1"
                  'aTable.Indexes.Append aIndex ' save the index

                  for each col in tab.columns
                     c = lcase(col.name)
                     fbot.WriteLn("column existing: " & c & "&nbsp&nbsp&nbspautoincrement status: " & objcol.Properties("AutoIncrement") & "<br>")
                  next
               
               end if
            next
         
            set objcat=nothing
         
            fbot.WriteLn("<br>You database have been created with 1 table and a defaut column named " & colnam & "<br>")   
            fbot.WriteLn("<br>END<br>")

Open in new window

Serge FournierAnalyst Programmer

Commented:
do not mind the html code that is everywhere, it's because it come from an interface i made in html to create a database

you can see at line 78, i put in comment the test i made when i added more columns in the database
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Serge FournierAnalyst Programmer

Commented:
of course as a professional, i chek if the database file exist before i create it

then i chek if the column exist before creating it

Author

Commented:
wildboy...  thanks you very much for the reply.  Regretfully I am FAR from good in VB.  I'm still learning.  I am decent in Access, but my coding abilities a remedial at BEST...  although I greatly appreciate the response, I'm not too sure how to apply your concept to my database.  I already have the database created.  I just need to get this blanket number from the HID file to apply to all other tables.  This way, I can report by both client and wholesale.  Could you maybe elaborate more, not to sound like a total novice...  I would appreciate any further help you could offer...
Most Valuable Expert 2014

Commented:
From the look of it your tables already exist

DoCmd.TransferText acImportFixed, "BID Import Specification", "tblITEMS", "C:\MSA REPORT\BID.txt"
DoCmd.TransferText acImportFixed, "SID Import Specification", "tblRetailers", "C:\MSA REPORT\SID.txt"
DoCmd.TransferText acImportFixed, "PUR Import Specification", "tblSales", "C:\MSA REPORT\PUR.txt"

If tblItems, tblRetailers and tblSales already exist, just open them in Design view and add a field.
Now, your code doesn't really show you doing anything with HID.txt
And you haven't said how exactly these file/tables are related.
Do they all have the same number of entries and they relate numerically?
ie line 4 in HID --> line 4 in BID --> line 4 in SID ext

Author

Commented:
Basically, the flatfile has 4 parts:  HID, BID, CID, PUR.  I have my tables and relations set up.  Right now, there is linking information from CID to PUR (it's the retailer number).  BID is a reference of how they report on products sold (it's their way of stating the items).  For all intents and purposes, it doesn't really matter right now.  The HID is the manufacturer that submitted the report.  So I need to be able to report on the individual accounts (through the CID, which I can), and I need to report based on the supplier (HID).  Right now, there is nothing to link the fact that the import is coming for this specific HID.  My hopes were to utilize the HID number and drop a filed in every table, and as it imports, it writes the appropriate HID# to each table.  The files are all fixed width (and I knwo the column sizes already).  Basically, the HID is a 1 line file with manufacturer information.  The HID# is the 8 character number located after "HID" in my HID.txt.  Of course, any leading zeros would have to be dropped, so the issue becomes I need it to be in number format (otherwise it keeps the leading zeros).  This is also a problem, as I've seen, since Access takes all text as TEXT formar.  I can't change it to a number (or simply don't know how).  If I change it to a number, I get key errors.  

But that's it, in a nutshell.  I just need to assign the current HID number in the current HID.txt to a column in my other tables to link that import to that manufacturer.

I really hope you can help, and that my explanation may help you more.  I am including 4 scaled down source files, so you can see what I'm currently working with.  Maybe it might help to show more.  I really appreciate your consideration in this.  I really do! BID.TXT HID.TXT SID.TXT PUR.TXT
Thanks for the efforts, but I found a work-around through the use of importing into temp tables and manipulating it from there.  Thanks again for the efforts.

Author

Commented:
I was able to find my own workaround for it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial