Is it possible to force a query to start table search at end?

[sumdumgai]

As the post title says, is it possible to force a query to start its table record search at the end of the table and go backwards? The table I'm searching has hundreds of thousands of records and I want to check if any new records have a field with a value that has already been imported into the table. The duplicates would most likely occur near the end of the table and not the beginning, so I see no reason to waste cycles searching records from the very first record in the table. Thanks.

 

[plog]

No, and what your asking doesn't really make sense. For one, a table has no order--there is no first record, no last record, no next and no prior. A table is essentially a bucket of data you throw records into. Now, when you use a query to pull records out of that bucket, that can have order--but only when you explicitly use the ORDER BY clause.

Additionally, a query is an entity onto itself, its not really an iterative process. All the records get populated at the same time--its an all or nothing deal.

With that said, you can use a Recordset in VBA (http://allenbrowne.com/ser-29.html) to iterate through your tables in the order you specify.

 

[jdraw]

Further to plog's comments, if you want to avoid duplicates in a table, then index the field an specify no duplicates in the table design. The database itself can "prevent" duplicates.

 

[nanscombe]

Or if you are using a bit of VBA code.

Dim lngCount as Long

lngCount = nz(DCount("*", "yourTableName","yourFieldName = " & yourValueValue), 0)

Only if no existing records are found will lngCount return 0.

 

[sumdumgai]

Thanks for the input. So, if I understand correctly, there is no way to search records for a particular field value in a way that you are looking first at records that were most recently created; i.e. there is no attribute of a record that distinguishes it chronologically from any other record. Is that right?

I can't use the ‘index no duplicates’ attribute because duplicates are permissible. The records I am importing come in ‘bunches’ within one to many files. The file name is a saved as a field in the record. That means there could be many records that are themselves unique, but they may have ‘file name’ fields that have the same value (i.e. because they came from the same input file). Once the records from a particular file are imported, I do not want to import record from that file again. That would result in duplicate records. These input files are created daily and their file names contain their create date. Therefore, there is no chance that an input file would have the same name as one created and imported a week ago. I was hoping I could search the most recent records imported (up to an arbitrary limit that I would set) to see if a newly to-be imported record is coming from an input file that was already imported (i.e., an input file is incorrectly being imported again).

Searching the table for records that were most recently imported (with file names that are recent) is what I wanted to do; i.e., start with the last imported record and search backward for some number of records and then stop; instead of searching hundreds of thousands of records.

Thanks.

 

[Galaxiom]

As long as there is an index on the filename field the search will be very fast.

An index eliminates the need for the engine to read every record when searching.

 

[MarkK]

What is the actual problem? You say you "see no reason to waste cycles," but what are cycles? Do you have an actual process that is too slow? Then we should focus on that process and try to optimize it specifically.

What is the code for your process, VBA and SQL? Posting an example of the table might help. Are the fields you use indexed? You can index multiple fields and require that that combination of values be unique. In that case your table can refuse duplicate combinations of data.

hth

 

[sumdumgai]

Thanks again for your help. Bear with me. I'm new to Access queries and I am beginning to appreciate their power. I am using Excel VBA to build SQL queries and open the database. Nanscombe's (Nigel) suggestion seems to work. For a couple of hundred thousand records in a test database, the record count came back within a second or two. Could someone please offer an example DCount query that used more than one search value for the specified field? As I said, the input files may be one or many. I'd like to search the database for one to many file names within the imported records. Thanks.

 

[sumdumgai]

Here's some code that I have so far. I'd like to place the input file names (one to many) in a variable array and then pass those array items to the query:

lngCount = Nz(DCount("*", "tablename", "[Source File] = '" _
& sFile1 _
& "' Or [Source File] = '" _
& sFile2 _
& "' Or [Source File] = '" _
& sFile3 _
& "'"), 0)

 

[MarkK]

Before we figure out how to do that, why would you?

Lets say you have four different file names and you run your DCount() and you get 12 records. Then what?

 

[sumdumgai]

If I have n files to import, I want to pass n parameters to the DCount. For right now, if my record count from the DCount is > 0 then I know that at least one of my input files has been imported before and the import process does not start. That would be my first step. Later, to be more sophisticated, I could determine which files to skip and which to import. Thanks.

 

[sumdumgai]

This seems to work. I would load the array with as many file names as are being imported. In example below, I just show three files. Any improvements would be appreciated:

sFileCount = 3

ReDim sFile(1 To sFileCount)

' Load the array
sFile(1) = "20140121080412677.txt"
sFile(2) = "20140120090418476.txt"
sFile(3) = "20140120090418992.txt"

orClause = "[Source File] = '" & sFile(1) & "'"

If sFileCount > 1 Then
For n = 2 To sFileCount
orClause = orClause & " Or [Source File] = '" & sFile & "'"
Next n
End If

lngCount = Nz(DCount("*", "Table Name", "" & orClause & ""), 0)

 

[sensetech]

Wouldn't it be easier to check / process the files one at a time, in a loop?

 

[MarkK]

I don't know why you'd batch them all up like that. You can only import one at a time, right? So check at import time if what you are going to import is valid.

For Each Filename In List
   If Filename.IsValid And Filename.Exists Then
      Import
   Else
      Skip
   End If
Next

With your scheme, if it fails the test after you've clumped all the names in one expression, then you still have to go through them all and figure out which one--or more--failed. Then you have to test if each one can be found on disk.

 

[nanscombe]

I'm afraid I'm going to naughty here.

There is a new way of doing it with the fileSystemObject but I've not had the opportunity to get to know it yet, so I'm going to stick with a method I know.

Public Function importFiles(byVal ifDirectory as String)
Dim strFile as String, rstImports as Recordset

[COLOR="Blue"]' Open a recordset based on the table tblImportedFiles[/COLOR]
  Set rstImports = CurrentDb.OpenRecordset("tblImportedFiles", dbOpenDynaSet)

  With rstImports

[COLOR="blue"]' Make sure ifDirectory ends with a "\"[/COLOR]
    If Right(ifDirectory, 1) <> "\" Then ifDirectory = ifDirectory & "\"

[COLOR="blue"]' Get first file[/COLOR]
    strFile = Dir(ifDirectory & "*.*", vbNormal)

    Do
[COLOR="blue"]' If file is not found exit loop[/COLOR]
      If Len(strFile & vbNullString) = 0 Then Exit Do

[COLOR="blue"]' If file ends in "." then skip it[/COLOR]
      If Right(strFile, 1) = "." Then Goto nextFile

[COLOR="blue"]' If file does not end in ".txt" then skip it[/COLOR]
      if Right(strFile, 4) <> ".txt" Then Goto nextFile

[COLOR="blue"]' Look for an existing entry of strFile in field importedFileName[/COLOR]
      .FindFirst ("importedFileName = '" & strFile & "'")
      If .NoMatch Then

[COLOR="blue"]'  If an entry is not found then do import routines
        ' Import file code here
        ' ...[/COLOR]

[COLOR="blue"]'  Once import is complete add a new record to table tblImportedFiles[/COLOR]
        .AddNew
          !importedFileName = strFile
        .Update

      Else

[COLOR="blue"]'  If an entry is found then do file skipping routines
        ' Skip file code here[/COLOR]

      Endif    

nextFile:  

[COLOR="blue"]'  Get next file[/COLOR]
      strFile = Dir
    Loop

  End With

[COLOR="blue"]' Close and clean up[/COLOR]
  rstImports.Close
  Set rstImports = Nothing

End Function

Table tblImportedFiles would have at least a text field called importedFileName.

If there was a legitimate possibility of having two different files with the same name then you could test for the path or file size as well.

 

[sumdumgai]

Thanks for the suggestions. All of the following is done with VBA. The process starts with an SFTP download of txt files (one to many) into a 'holding' folder. As the files are SFTP'd, they are deleted from the source server. If, for some reason, the process needs to be restarted before the database import is started, the files in the 'holding' folder are reused. The txt files in the ‘holding’ folder are then imported into Excel, concatenating them onto a single worksheet. The data then goes through a cleaning process, essentially checking against validation tables and formatting it. When the data is ready, Excel creates an output txt file and launches Access, automatically starting a 'Saved Import' routine. After import, Access returns control back to Excel. The individual downloaded txt files are then moved from the ‘holding’ folder to an archive folder.

The problem with checking the downloaded files against the archive before they are imported is that I'd have to treat each individual txt file separately, whereas the txt files coming in on any given day are to be treated as a group; e.g., a daily report is produced that summarizes all imported data. I also want to make sure all txt files have been imported into the database before I discard them (because they may already be in the archive); e.g., the file could have been archived but the import failed.

I like the idea of checking the database first for a file name and, if found, discarding the input file before it is imported into Excel for validation and grouped into a single output file for Access. Importing files more than once is a big problem because the database table is an append table.

Is there a limit as to the number of parameters that an SQL query can handle, both in terms of number of parameters and number of characters in the SQL statement?

Sorry for the long reply. Thanks again.

 

[jdraw]

I don't understand why you are using Excel and Access.
Why delete the the sftp files before the process is completed successfully? If you have some corruption in the data base, don't you stand to lose all the data?

 

[sumdumgai]

Thanks for suggestions. Because the database is backed up regularly and the input files are archived, the database can be reconstructed from last good back up.

What would I use if not Excel and Access?

My question now is about SQL query limits. Is there a max number of parameters that an SQL query can use, and is there a max number of characters that an SQL query can be?

Thanks.

 

[sensetech]

Surely you could drop the Excel processing and instead get the files straight into Access then do the cleansing & reformatting there.

Sounds worryingly similar to a process which I'm currently optimising for a client. At the moment the data arrives in Excel format, goes through an Excel app which cleanses it, the clean data goes into an Access app to enable heavy use of SQL queries then the exported output from each of those queries goes into yet another Excel app to produce the end-user reports. I'm ripping the Excel apps out and making it all Access-based. It'll be a lot simpler to maintain and there'll be quite a time-saving with large input files.

 

[nanscombe]

When I processed files I had two folder, In & Out.

I would put any new files into the In folder, process the files and automatically move them to the Out folder.

If there is a way of telling whether an import has been successful or not it should be possible to add a Failed folder as well if things go awry.