Re: dBaseIV import by code problem

Discussion in 'Microsoft Access Macros' started by Steve Schapel, Aug 9, 2003.

  1. Peter,

    A couple of things...
    - I don't think it is correct to put the trailing \ on the directory
    name in the Database Name argument
    - There is a spelling error with the Object Type argument... should
    be acTable, although you might as well leave this blank as it is not
    applicable in this case
    - You have not entered a Destination. I believe that this is
    required.

    I am also puzzled at your use of the Left$ function in the definition
    of your fName variable. I can't really see what this would achieve
    which simply Dir("C:\StoreDBFfiles\???A.dbf") alone will not achieve.

    So, try it like this...
    fName = Dir("C:\StoreDBFfiles\???A.dbf")
    DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles", ,
    fName, "MyTable"

    - Steve Schapel, Microsoft Access MVP


    On Sat, 09 Aug 2003 08:00:06 GMT, "Peter Jamieson"
    <> wrote:

    >This code snippet :
    >
    >fName = Left$(Dir("C:\StoreDBFfiles\???A.dbf"), 8)
    >DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles\", acTablefs,
    >fName
    >
    >gives me an error waring:
    >
    >Run_time error 2507:
    >' The dBaseIV type isn't an installed database type or doesn't support the
    >operation you chose"
    >
    >I have manually imported the .dbf file types OK.
    >Any ideas as to what is cauing the error appreciated!
    >Cheers, Peter J.
    >
     
    Steve Schapel, Aug 9, 2003
    #1
    1. Advertisements

  2. G'day Steve,
    Many thanks for your response which has proved to be just what was needed!
    I have used your suggestions and my project has now progressed to the stage
    below.
    BTW, the "peculiar" fName variable was a hangover from earlier code and has
    been rectified
    along the lines of your suggestion.

    The fName is useful to discover two required values, TCode and
    DateCode.....from the
    latter the date of the file can be determined.....and these values are to be
    inserted into
    the newly created fields, newField1 and newField2.

    My next question is: how to do this insertion?
    And then: how to collect each newly arrived table into a master table?...
    and then I guess to delete the unwanted table?
    Do I run the risk of serious bloat if I use this method for large numbers of
    files?
    As it stands the code stops, after only one dbf file is retrieved, at the
    Set newField1 =etc
    line since this has already been done at the previous cycle.

    The full sub is intended to daily collect a lot of dbf files into one table
    and so far looks like this:

    Sub ImportDBFfiles()
    '' Code to import all dbf files from a folder
    '' and collect the files into an Access table
    Dim fName As Variant
    Dim theTable As TableDef
    Dim dbs As Database
    Dim newField1 As Field, newField2 As Field
    Dim DateCode As String, TCode As String
    Dim counter As Long

    Set dbs = CurrentDb
    fName = Dir("C:\StoreDBFfiles\???*R.dbf")
    Do While (fName <> "")
    DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles", ,
    fName, "MyTable"
    dbs.TableDefs.Refresh
    Set theTable = dbs.TableDefs!MyTable
    Set newField1 = theTable.CreateField("DateCode", dbText, 4)
    Set newField2 = theTable.CreateField("TCode", dbText, 3)
    With theTable
    .Fields.Append newField1
    .Fields.Append newField2
    End With
    ' Determine TCode and DateCode from the fName
    TCode = Left(fName, 3)
    DateCode = Mid(fName, 4, 4)
    '' Not sure how to insert these values into their fields
    '' Not sure how to code append MyTable to a master table
    Loop
    dbs.Close
    Set dbs = Nothing
    End Sub

    Again, many thanks for your assistance....much appreciated!
    Cheers, Peter J.


    "Steve Schapel" <> wrote in message
    news:...
    > Peter,
    >
    > A couple of things...
    > - I don't think it is correct to put the trailing \ on the directory
    > name in the Database Name argument
    > - There is a spelling error with the Object Type argument... should
    > be acTable, although you might as well leave this blank as it is not
    > applicable in this case
    > - You have not entered a Destination. I believe that this is
    > required.
    >
    > I am also puzzled at your use of the Left$ function in the definition
    > of your fName variable. I can't really see what this would achieve
    > which simply Dir("C:\StoreDBFfiles\???A.dbf") alone will not achieve.
    >
    > So, try it like this...
    > fName = Dir("C:\StoreDBFfiles\???A.dbf")
    > DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles", ,
    > fName, "MyTable"
    >
    > - Steve Schapel, Microsoft Access MVP
    >
    >
    > On Sat, 09 Aug 2003 08:00:06 GMT, "Peter Jamieson"
    > <> wrote:
    >
    > >This code snippet :
    > >
    > >fName = Left$(Dir("C:\StoreDBFfiles\???A.dbf"), 8)
    > >DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles\",

    acTablefs,
    > >fName
    > >
    > >gives me an error waring:
    > >
    > >Run_time error 2507:
    > >' The dBaseIV type isn't an installed database type or doesn't support

    the
    > >operation you chose"
    > >
    > >I have manually imported the .dbf file types OK.
    > >Any ideas as to what is cauing the error appreciated!
    > >Cheers, Peter J.
    > >

    >
     
    Peter Jamieson, Aug 9, 2003
    #2
    1. Advertisements

  3. Peter,

    (My apologies for accidentally sending this as private email)

    Possibly the simplest way is to use Update and Append queries to get
    the data where you want it. Declare a variable...
    Dim strSQL As String
    .... and then in the place you indicated in your code, something like
    this...

    strSQL = "UPDATE MyTable SET DateCode='" & Mid(fName, 4, 4) & "',
    TCode='" & Left(fName, 3) & "'"
    dbs.Execute strSQL
    strSQL = "INSERT INTO MasterTable (DateCode, TCode, OtherFields)
    SELECT DateCode, TCode, OtherFields FROM MyTable"
    dbs.Execute strSQL

    I must say, though, that I would not approach it in this way. I can
    see no reason for the creation of the new fields in MyTable. If you
    need the DateCode and TCode included in each record in the
    MasterTable, you can bypass putting this into MyTable, just include it
    in the data append to MasterTable, e.g.

    Sub ImportDBFfiles()
    Dim fName As String
    Dim strSQL As String
    Dim dbs As Database
    Set dbs = CurrentDB
    Do While (fName <> "")
    fName = Dir("C:\StoreDBFfiles\???*R.dbf")
    DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles",
    , fName, "MyTable"
    strSQL = "INSERT INTO MasterTable (DateCode, TCode, OtherFields)
    SELECT '" & Mid(fName, 4, 4) & "', '" & Left(fName, 3) & "',
    OtherFields FROM MyTable"
    dbs.Execute strSQL
    strSQL = "DELETE * FROM MyTable"
    dbs.Execute strSQL
    Kill "C:\StoreDBfiles\" & fName
    Loop
    End Sub

    Note that I have moved the fName definition inside the loop... it
    wouldn't work the way you had it before. Also, I have assumed you
    would take care of the fiel already processed by deleting rather than
    renaming.

    Having said all that, I still probably wouldn't do it this way. I
    would have a separate import logging table, with an ID field and the
    DateCode and TCode in there, and then reference this from MasterTable,
    so...

    Sub ImportDBFfiles()
    Dim fName As String
    Dim strSQL As String
    Dim NextID As Long
    Dim dbs As Database
    Set dbs = CurrentDB
    Do While (fName <> "")
    NextID = DMax("[ID]","LogTable") + 1
    fName = Dir("C:\StoreDBFfiles\???*R.dbf")
    DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles",
    , fName, "MyTable"
    strSQL = "INSERT INTO LogTable (ID, DateCode, TCode) _
    SELECT " & NextID & ", '" & Mid(fName, 4, 4) & "', '" & _
    Left(fName, 3) & "'"
    dbs.Execute strSQL
    strSQL = "INSERT INTO MasterTable (ID, OtherFields) _
    SELECT " & NextID & ", OtherFields FROM MyTable"
    dbs.Execute strSQL
    strSQL = "DELETE * FROM MyTable"
    dbs.Execute strSQL
    Kill "C:\StoreDBfiles\" & fName
    Loop
    End Sub

    Warning: All the above is "air code", and I would be surprised if it
    doesn't need some tweaking. Especially as this is a macros newsgroup,
    and none of this has got anything to do with macros :)

    - Steve Schapel, Microsoft Access MVP


    On Sat, 09 Aug 2003 14:57:01 GMT, "Peter Jamieson"
    <> wrote:

    >G'day Steve,
    >Many thanks for your response which has proved to be just what was needed!
    >I have used your suggestions and my project has now progressed to the stage
    >below.
    >BTW, the "peculiar" fName variable was a hangover from earlier code and has
    >been rectified
    >along the lines of your suggestion.
    >
    >The fName is useful to discover two required values, TCode and
    >DateCode.....from the
    >latter the date of the file can be determined.....and these values are to be
    >inserted into
    >the newly created fields, newField1 and newField2.
    >
    >My next question is: how to do this insertion?
    >And then: how to collect each newly arrived table into a master table?...
    >and then I guess to delete the unwanted table?
    >Do I run the risk of serious bloat if I use this method for large numbers of
    >files?
    >As it stands the code stops, after only one dbf file is retrieved, at the
    >Set newField1 =etc
    >line since this has already been done at the previous cycle.
    >
    >The full sub is intended to daily collect a lot of dbf files into one table
    >and so far looks like this:
    >
    >Sub ImportDBFfiles()
    >'' Code to import all dbf files from a folder
    >'' and collect the files into an Access table
    >Dim fName As Variant
    >Dim theTable As TableDef
    >Dim dbs As Database
    >Dim newField1 As Field, newField2 As Field
    >Dim DateCode As String, TCode As String
    >Dim counter As Long
    >
    >Set dbs = CurrentDb
    > fName = Dir("C:\StoreDBFfiles\???*R.dbf")
    >Do While (fName <> "")
    > DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles", ,
    >fName, "MyTable"
    > dbs.TableDefs.Refresh
    >Set theTable = dbs.TableDefs!MyTable
    >Set newField1 = theTable.CreateField("DateCode", dbText, 4)
    >Set newField2 = theTable.CreateField("TCode", dbText, 3)
    >With theTable
    > .Fields.Append newField1
    > .Fields.Append newField2
    >End With
    > ' Determine TCode and DateCode from the fName
    > TCode = Left(fName, 3)
    > DateCode = Mid(fName, 4, 4)
    >'' Not sure how to insert these values into their fields
    >'' Not sure how to code append MyTable to a master table
    >Loop
    > dbs.Close
    >Set dbs = Nothing
    >End Sub
    >
    >Again, many thanks for your assistance....much appreciated!
    >Cheers, Peter J.
     
    Steve Schapel, Aug 9, 2003
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nick

    Import problem

    Nick, May 16, 2004, in forum: Microsoft Access Macros
    Replies:
    4
    Views:
    141
  2. Dave R.
    Replies:
    1
    Views:
    495
    Brendan Reynolds
    Jun 24, 2005
  3. Newbie
    Replies:
    5
    Views:
    374
    Douglas J. Steele
    Sep 4, 2006
  4. italia
    Replies:
    0
    Views:
    237
    italia
    Sep 12, 2006
  5. Don Wiss

    Import code getting error

    Don Wiss, Apr 22, 2009, in forum: Microsoft Access Macros
    Replies:
    11
    Views:
    745
    Steve Schapel
    Apr 26, 2009
Loading...

Share This Page