Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    47

    Unanswered: Processing random files with dynamic query

    Hi Group,
    I have imported some files using export specifications. I now need to strip out superfluous information once these files are imported. I have created several queries which do this.
    However I would like to automate this process as there are at least 30 files. The thing is the file names will always be different so I can't 'hard code' the tables name in the query.

    How can I create the queries dynamically to use each import file?

    Thanks!!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Does the layout of the table need to change also?

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    some files using export specifications
    and are the import specs the same for each file?

    i do something similar with *.XLS in a (user defined) directory. works fine.

    the (DAO!) code is trivial:

    Code:
        dirdFile = Dir(userFile)                            'check for a file
        If dirdFile = "" Then                               'if no files found
            PathOK = False                                  'set the sad return
            MsgBox "No XLS found in " & userFile, vbCritical + vbOKOnly, "Harvester.PathOK"
            Exit Function                                   'and quit
        End If                                              'otherwise at least one file exists so we are OK
        
        Set dabs = CurrentDb
        Set recs = dabs.OpenRecordset("FileList")           'open recordset on table FileList
        
        With recs
            Do While dirdFile <> ""                         'as long as we keep finding files
                .AddNew                                     'add a record
                !fileSpec = userPath & "\" & dirdFile       'save the full filespec
                .Update
                dirdFile = Dir                              'and try again
            Loop
        End With
    and once you've got all the source file names in a table, you step through them, importing one by one.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2004
    Posts
    47
    Rogue,
    No the layout of the tables are set in stone. However, the layout for each table is different.

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    If the layouts don't change, you can import them into an existing table that is used in the queries. Delete all records from the tables before you start the import routine.

  6. #6
    Join Date
    Apr 2004
    Posts
    47

    Thanks Rogue....

    I'll look into this....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •