asked on 5/16/2011

Create column during Access import

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.

Microsoft Access VB Script

Last Comment

fizzlefry

5/29/2011

fizzlefry

5/17/2011

ASKER

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

Serge Fournier

5/17/2011

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 Fournier

5/17/2011

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

Serge Fournier

5/17/2011

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

fizzlefry

5/17/2011

ASKER

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...

Nick67

5/17/2011

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

fizzlefry

5/17/2011

ASKER

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

ASKER CERTIFIED SOLUTION

fizzlefry

5/24/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

fizzlefry

5/29/2011

ASKER

I was able to find my own workaround for it.