TransfertText... with the question!

[Patrice]

Hi,

I have that command in a sub

DoCmd.TransferText acImportDelim, "Meteo", "Meteo", strPath_Nom

If something bad append like trouble with primary key, I'll get a message
from Access like " 0 record was imported..." but is no error.number generated

How can I trap the Access message to be able to continu in my code like that

If NoBadLuckWithTransferText then
bolTransfer = True
else
bolTransfer = False
end if

Thank you

Patrice

 

[pietlinden]

Hi,

I have that command in a sub

DoCmd.TransferText acImportDelim, "Meteo", "Meteo", strPath_Nom

If something bad append like trouble with primary key, I'll get a message
from Access like " 0 record was imported..." but is no error.number generated

How can I trap the Access message to be able to continu in my code like that

If NoBadLuckWithTransferText then
  bolTransfer = True
else
  bolTransfer = False
end if

Thank you

Patrice

You can trap for any errors you want... you have to modify your code a
little, though..

Sub TransferFile(ByVal strPath_NomAs String)
On Error Goto ErrHandler

DoCmd.TransferText acImportDelim, "Meteo", "Meteo", strPath_Nom

Exit Sub

ErrHandler:
Select Case Err.Number
Case someNumber
'do one thing
Case otherNumber
'do something else
Case Else
' take the default action
End Select

End Sub

In your case, one thing you could do is write the name of the
offending file to a table or move it to a different folder...

HTH,
Pieter

 

[Patrice]

The problem is : No error is generated. It's only a warning message

 

[dymondjack]

Try

DoCmd.SetWarnings(False)

Just be VERY careful with this... make sure to set it back to true on every
exit point of the procedure that there is (there should only be one
anyway...), and don't reset a project during debug mode without setting it
back to true.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Patrice]

With that I got no messages but error number still = 0 so I can't trap
anything

 

[dymondjack]

I don't think it will be possible to actually trap an error, as TransferText
doesn't raise any if there was an error in the process. SetWarnings will
supress the message, but that's it. Unfortunately, there's no flags that you
can watch via code to say whether the transfer was correct or not (at least
not that I'm aware of).

You can always try to read the table that it gets imported to when the
transfer is done, and verify what information you can. Sometimes thats
almost impossible, as you may not always know what you should have when it's
done, but I think that's about the best that can be done for it.

I've got a number of tables that I import on a daily basis from a separate
application, and it took quite a bit of trial and error (working with
different import/export types) to find one way that I was confident enough
with, but I never did come up with a way to verify the data brought in.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Patrice]

Thank's

The other solution, but it's not very professionnal, is to ask the user if
he got a message from Access!!! JUst hope he 'll give the good answer

Docmd.transferText.......

if msgbox("Did you get troubleshouting during the
transfer?",vbYesNo,"Information")= vbYes then

But really I think I'll create a temporary importTable and after the
TransferText I'll check if is something in there before read it to compare
with the real table where I want it to go.

 

[John Spencer]

Import into a work table.
Then use VBA and an append query to trap any errors.

Dim Db as DAO.Database
Dim StrSQL as String
Dim LTransferred as Long, LOriginal as Long
LOriginal = DCount("*","WorkTable")

Set Db = CurrentDb()

StrSQL = "Insert into FinalTable(FieldA, FieldB)" & _
" SELECT FieldA, FieldB FROM WorkTable"

Db.Execute (StrSQL,dbFailOnError)
LTransferred = Db.RecordsAffected

If LTransferred <> LOriginal THEN
MsgBox LOriginal - LTransferred & " records were not transferred"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================