Read a file and import its data into a table

[namliam]

You can make the query in the query designer, change the view of the designer to SQL and copy/past the sql in VBA

Or... keep the query as a query object and simply refer to it by name
Currentdb.Openrecordset("YourQueryName")

Only tricky part (can be) to get your parameters into the sql

 

[anishkgt]

Okie dokie. Will try that.

 

[anishkgt]

Ok so here goes

Set RSqry = CurrentDb.OpenRecordset("qryMIR")
If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq) Then  'validate  MIR Sequence number and Ticketnumber to avoid Duplication
                    txtMIRActivity.Value = srcfile & " " &  MIRSeq & " " & PNR & " " & "DUPLICATE MIR FILE" &  vbNewLine & txtMIRActivity.Value
                    Print #2, srcfile & " " & MIRSeq & " " & PNR & " " & "DUPLICATE MIR FILE"
                    Close #1
                    Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
                    GoTo NextFile
                    ElseIf DCount("FileID", "qryMIR", "[TicketNumber]=""" & tkt & """") Then
                    txtMIRActivity.Value = srcfile & " " & tkt  & " " & PNR & " " & "DUPLICATE TICKET" & vbNewLine  & txtMIRActivity.Value
                    Print #2, srcfile & " " & tkt & " " & PNR & " " & "DUPLICATE TICKET"
                    Close #1
                    Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
                    GoTo NextFile
                    End If

is that how it should look, sorry if i got it wrong. Wondering what difference it would make.

 

[namliam]

/smash head to desk

I see where you are going and why *boink*

However your approach is *boink* flawed

You wanted to *boink* check for both and not *boink* either or, right???

Nevermind the possible abuse of Danything at the moment. Keep the DCount as I gave it to you Originaly *boink*

Dcount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") then

oh and please oh please, *boink boink boink* do indent your code.

If ...
    more code
elseif ...
    that code
else
    some code
end if

Just having some fun, and enjoying a major headache

 

[anishkgt]

So sorry about the things.

Well yes that is what i need, i wanted to check for both but the above code, wouldn't that check the whole query ? i need to check on a row because the MIRseq resets when it reaches 65535 from 1 and there can repeating tkt numbers which is ok as it can be for refunds and voids. what was error on my dlook ? just curious to learn.

 

[namliam]

Yes it will check the whole query but you dont want to check the whole query?

Your DLookup? Thought you was using DCount?? *my poor head*
Yes I do have a headache IRL, my being a pain here isnt really your fault....

Yours:
If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq) And 
   DCount("FileID", "qryMIR", "[TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication

vs
Mine:
If Dcount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") then

The difference is you do 2 seperate DCounts, thus two seperate queries, with seperate records. My way only has 1 DCount

 

[anishkgt]

Ok reason to check it by a row was that in an actual MIR file a MIRseq is unique to a MIR file and thus unique to the data within. once these are in query, a file is represented by a row so to check for duplicates the ideal way is to check for a MIRSeq from the processing file and check the TicketNumber in the corresponding row rather than checking MIRSeq and TicketNumber randomly. Hope that explains.

is that how your code checks ?

 

[namliam]

You lost my hurting head sorry

Can you give a data sample of either a table or your query on what you are trying to achieve?

 

[anishkgt]

how do i read the number for A04 lines?

 

[anishkgt]

ok let me get you an excel sheet of that !

 

[anishkgt]

OK so the FileId represents each file thus each row are the contents of each file. From the spreadsheet you can see that there are files with same MIRSeq but not same file with both MIRSeq and TicketNumber the same.

 

[namliam]

Well doesnt my dcount do just that?

If Dcount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") then

Simply change qryMir to this table

 

[anishkgt]

yes ! after a while of troubleshooting, seemed like it is working. i've edited code as follows to check another field too

If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
    txtMIRActivity.Value = srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE" & vbNewLine & txtMIRActivity.Value
    Print #2, srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE"
    Close #1
    Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
    GoTo NextFile
ElseIf DCount("FileID", "qryMIR", "[MIRType]=""" & MIRType & """ and [TicketNumber]=""" & tkt & """") Then ' validate data for duplicates with MIRType and TicketNumber
    txtMIRActivity.Value = srcfile & " " & "'" & MIRType & "'" & " " & tkt & " " & PNR & " " & "DUPLICATE DATA" & vbNewLine & txtMIRActivity.Value
    Print #2, srcfile & " " & "'" & MIRType & "'" & " " & tkt & " " & PNR & " " & "DUPLICATE DATA"
    Close #1
    Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
    GoTo NextFile
End If

is that ElseIf part correct ? and how do i count the number of A04 Lines in the file.

 

[namliam]

If     DCount("FileID", "qryMIR", "[MIRSeq]   =" & MIRSeq & "    and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
ElseIf DCount("FileID", "qryMIR", "[MIRType]=""" & MIRType & """ and [TicketNumber]=""" & tkt & """") Then ' validate data for duplicates with MIRType and TicketNumber

It could be a personal thing, but I prefer to keep things a little more readable by doing something like:

If     DCount("FileID", "qryMIR", "[MIRSeq] =" & MIRSeq & _ 
                             " and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication

This keeps things visible and on screen particularly if you get more and more "AND/OR" lines

These two Dcounts are very simular, can you not capture them in one select?

dim rsCheck as dao.recordset
'Can replace the qryMIR by the source table if there is a table that holds the 3 columns
rsCheck = currentdb.openrecordset (" Select MirSeq, MIRType, TicketNumber " & _
                                   " From   qryMIR " & _ 
                                   " Where     (    [MIRSeq]      =" & MIRSeq & _ 
                                           "    and [TicketNumber]=""" & tkt & """ ) " & _
                                   "    or     (    [MIRType]=""" & MIRType & """" & _ 
                                           "    and [TicketNumber]=""" & tkt & """ ) "
If not rscheck.eof then
 ' duplicate found
    if MIRSeq = rsCheck!MirSeq then 
       ' MIRSeq message
    elseif Mirtype = rscheck!MirSeq then
       ' mirtype message
    endif
    Goto NextFile
Endif
rsCheck.close
set rscheck = nothing

This would only do one select instead of 2 Dcounts removing a suplus search on your DB.

What A04 lines do you want to count?
The (duplicate) file now being read?
The file already existing in the db?
The number of A04 lines as they are being read from (any) file into the db?

A simple check in the database could be something like

DCount("*", "A04Table", "FileID = " & Fileid)

Or
"Select count(*) from A04Table where fileid = " & FileID

Could also incorporate the A04table and count into the above select statement to (further) reduce the processing time required.

 

[anishkgt]

The mirseq and mirtype are in tblfiles and ticket number is in tblpax. So i guess the select statement will not work here. Need to check on the elseif state and see if i can get it check the mirseq, mirtype and tikt number at one go.

About the A04 line, need to check the number of lines as they are being read from file into the db as a files named sectors.

 

[namliam]

The select statement will work with any number of tables... Reducing the number of tables to the bare minimum on seperate queries reduces the amount of memory needed as well as the I/O required to fetch the data. So instead of using your qryMIR which uses 10 tables, using a seperate select with only 2 tables will cause less delay in your code execution. Maybe not hugely noticable but small bits add up over time and as you get more and more data, it will become more and more noticable.

About A04
Well you can easily count them A04 lines while reading them, would seem kind off redundant since once the individual lines are in the db, a simple count(*) can count them for you. Unless you are experiencing performance issues with using a Count(*) solution it is by far prefered to not store calculatable values in your database.

What kind off situation would you need the number of A04 lines for?

 

[anishkgt]

Ok ! If i use a query with just the tables with mirseq, mirtype and tktnumber fields, will that be helpful or should i use the select query itself ?

About the A04
The A04 has the sectors, so each line represents a sector. I just need to count the number of sectors in a file. Isn't counting them as the file is read better than using a function in a query as the db grows.

 

[namliam]

As simple a select as you can make it, remove any surplus if you can

A04
Counting them as they come in the file will save you processing time when you are reporting/looking for the data. However with proper indexing and querying that time should be near 0. Unless you need this data A LOT....

Obviously while reading your A04 lines you can simply do
CounterA04 = CounterA04 +1
and simply write the final number into some table column.

 

[anishkgt]

trying to bring all into one Dcount

If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [MIRType]=""" & MIRType & """ and [TicketNumber]=""" & tkt & """")

i get an error like Type Mismatch. Where is that in the code ?

 

[namliam]

Type mismatch means you are using a number as a text or reverse