Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

text (.txt) file to access(.mdb) file converter

I like to create a new Access database [lager.mdb] with a table name [Internet] from a user selected txt file using VB this code will be running on a PC without Access installed, I'm new into VB & MDB can someone give me some help?

If possible I also like to be able start the ascii to mdb convertion with a commandline parameter.
Maybe a VBS "Visual Basic Script" program is a better solution, you tell me :)


This is how my txt looks like.

Artnr     Benam     Annanben     Kortnamn     Artgrp     Enhet     Dokument     Anm1     Pris1     ilager
beg-AC-10     Insida/Utsida Lår     SABA     SABA     Styrketräningsutrustning     st          Ben     8 500,00     1,0000
beg-AC-11     Motionscykel MPK     MPK     MPK     Motionscykel     st          Kondition     8 800,00     1,0000
beg-AC-16     Bicepsmaskin-75 kg     World Class     World Class     Styrketräningsutrustning     st          Armar     8 000,00     1,0000
beg-AC-26     Trappmaskin Alpine-450          Alpine     Trappmaskin     st          Kondition     17 500,00     1,0000
beg-AC-30     Hacklift     Akema     Akema     Rehab-utrustning     st          Ben     4 500,00     1,0000
beg-AC-31     Löpband Challenger          Challenger     Löpband     st          Kondition     22 000,00     1,0000
beg-AC-34     Roddyna     Casall     Casall     Styrketräningsutrustning     st               800,00     1,0000
beg-AC-36     Hiss-50 kg     SABA     SABA     Rehab-utrustning     st          Rygg     2 500,00     1,0000




This is the code I have today but it's missing the txt import stuff...

      Dim cat As ADOX.Catalog
      'Dim tbl As ADOX.Table
      Dim con As ADODB.Connection
      Dim db_name As String
      Dim strSQL As String

      With OpenFileDialog1 ' Ask for new file location.
          .Title = "Select the file you like to Import"
          .Filter = "All(*.*)|*.*"
          .FilterIndex = 1
          .ShowDialog()
          If Len(.FileName) > 0 Then

              db_name = "lager.mdb"

              ' ---------------------------------------------------------------
              ' Delete the database if it exists.
              ' ---------------------------------------------------------------
              On Error Resume Next
              If Dir(db_name) <> "" Then Kill(db_name)
              On Error GoTo 0

              ' ---------------------------------------------------------------
              ' Create the new database.
              ' ---------------------------------------------------------------
              cat = New ADOX.Catalog()
              cat.Create( _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & db_name & ";")

              ' ---------------------------------------------------------------
              ' Connect to the database.
              ' ---------------------------------------------------------------
              con = cat.ActiveConnection

              ' ---------------------------------------------------------------
              ' Connect to the txt file.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create Table [Internet] and import the data from the txt file.
              ' Field names are in the first row in the txt file.
              ' There are two empty rows in the end of the txt file.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create the primary key [Nr] and make it autonr.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create the Index's.
              '
              ' Artnr = No dup.
              ' Benam = dup. OK
              ' Kortnamn = dup. OK
              ' Artgrp = dup. OK
              ' Anm1 = dup. OK
              ' ---------------------------------------------------------------
              ' ???
             
              ' ---------------------------------------------------------------
              ' Close the database connection.
              ' ---------------------------------------------------------------
              con.Close()
              con = Nothing
              'tbl = Nothing
              cat = Nothing

              MsgBox("Import Done.", vbInformation)
              Else
                  'MsgBox("Cancel!", vbCritical)
                  Exit Sub
              End If

      End With
0
PeOd
Asked:
PeOd
1 Solution
 
PsychoDazeyCommented:
Have you tried playing around with the TransferText method?  
0
 
TextReportCommented:
As PsychoDazey says you can try the TransferText option with a saved Import/Export Filter alternatively you can use the Open command to open the text file and loop through the file yourself.

But the answeer to your actul question is YES

RunCommand acCmdImport

and the user then has to find the file etc.

Cheers, Andrew
0
 
PsychoDazeyCommented:
Here is the syntax for the TransferText method I referred to:
DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PeOdAuthor Commented:
This convertion util is executed on a PC without MS Access installed, So I don't think that I can use the TransferText method.
0
 
AceDefabulCommented:
The following code should provide you with the missing parts. As for command line parameters, look up the Command function provided by Visual Basic.

 ' ---------------------------------------------------------------
      ' Connect to the txt file.
      ' ---------------------------------------------------------------
       
       Dim F1 As Long
       F1 = FreeFile
       Dim sThisLine As String
       
       'open the txt file
       'substitute the correct name for Import.txt"
       Open "Import.txt" For Input As #F1
       'get the column names from first line of Import file
       If Not EOF(F1) Then
         Line Input #F1, sThisLine
       End If
       
           
      ' ---------------------------------------------------------------
      ' Create Table [Internet] and import the data from the txt file.
      ' Field names are in the first row in the txt file.
      ' There are two empty rows in the end of the txt file.
      ' ---------------------------------------------------------------
      Dim vColumnNames As Variant
      Dim i As Integer
     
      'split the data fields using 5 space characters as the delimiter
      'I tried vbTab, it didn't work with cutting and pasting the example
      'data from your message.
     
      vColumnNames = Split(sThisLine, "     ")
      Set tbl = New ADOX.Table
      'add table to database
      tbl.Name = "Internet"
      cat.Tables.Append tbl
     
      'add the columns to the table
      For i = 0 To UBound(vColumnNames)
        'add column (name, data type, length)
        tbl.Columns.Append vColumnNames(i), adVarWChar, 65
      Next
             
      ' ---------------------------------------------------------------
      ' Create the primary key [Nr] and make it autonr.
      ' ---------------------------------------------------------------
     
      Dim col As ADOX.Column
      Set col = New ADOX.Column
      With col
        .Name = "Nr"
        .Type = adInteger
      End With
      Set col.ParentCatalog = cat
      col.Properties("Autoincrement") = True
      tbl.Columns.Append col
     
      Set idx = New ADOX.Index
      With idx
        .PrimaryKey = True
        .Name = "PrimaryKey"
        .IndexNulls = adIndexNullsDisallow
        .Unique = True
        .Columns.Append "Nr"
      End With
      tbl.Indexes.Append idx
      Set idx = Nothing
      ' ---------------------------------------------------------------
      ' Create the Index's.
      '
      ' Artnr = No dup.
      ' Benam = dup. OK
      ' Kortnamn = dup. OK
      ' Artgrp = dup. OK
      ' Anm1 = dup. OK
      ' ---------------------------------------------------------------
      Set idx = New ADOX.Index
      With idx
        .Name = "idxArtner"
        .Unique = True
        .Columns.Append "Artnr"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxBenam"
       ' .Unique = False
        .Columns.Append "Benam"
      End With
      tbl.Indexes.Append idx
                   
      Set idx = New ADOX.Index
      With idx
        .Name = "idxKortamn"
        '.Unique = False
        .Columns.Append "Kortnamn"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxArtgrp"
        '.Unique = False
        .Columns.Append "artgrp"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxAnm1"
        '.Unique = False
        .Columns.Append "Anm1"
      End With
      tbl.Indexes.Append idx
     
      Set idx = Nothing
     
      ' ---------------------------------------------------------------
      ' import the data from the txt file.
      ' There are two empty rows in the end of the txt file.
      ' ----------------------------------------------------------
      Dim vDataItems As Variant
      Dim rsTable As ADODB.Recordset
      Set rsTable = New ADODB.Recordset
      rsTable.Open "Internet", con, adOpenDynamic, adLockOptimistic
     
      While Not EOF(F1)
        Line Input #F1, sThisLine
        If Len(sThisLine) Then
          vDataItems = Split(sThisLine, "     ")
          rsTable.AddNew
          For i = 0 To UBound(vDataItems)
            rsTable(i) = vDataItems(i)
          Next
          rsTable.Update
        End If
      Wend
     
      rsTable.Close
      Set rsTable = Nothing
      Close #F1
     
0
 
PsychoDazeyCommented:
Could you use the runtime version of access?
0
 
TextReportCommented:
So why did you post the question in the access area if you are not actually using Access.

What are you running, if it is Access Runtime then TransferText will work. If VB rather that Access then you should post the question in the VB forum.

Cheers, Andrew
0
 
HobsonTCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area:
Accept AceDefabul 's answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Trevor
EE Cleanup Volunteer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now