DoCmd.TransferText

[zackat]

I'm using VBA and DoCmd.TransferText with a specification file to import data from a csv file into a table.
The table has an indexed field, No duplicates.

Initial run is ok.

Importing the same csv file the second time, generates an MS Access build-in message which is correct as it conflicts with the indexed field.

How can I trap this build-in message and replace it with my own?

Note:
The build-in message isn't an error with error number and or description.

Thanks

 

[reneemettrie]

I'm using VBA and DoCmd.TransferText with a specification file to import data from a csv file into a table.
The table has an indexed field, No duplicates.

Initial run is ok.

Importing the same csv file the second time, generates an MS Access build-in message which is correct as it conflicts with the indexed field.

How can I trap this build-in message and replace it with my own?

Note:
The build-in message isn't an error with error number and or description.

Thanks

If you don't care about the message you could use DoCmd.SetWarnings false, the built-in message will not be shown. At the end of the code you have to type docmd.set warnings true to turn them on again.

 

[zackat]

Thanks reneemettrie,
The message is correct and probably ok for a techie, not for a user that doesn't know anything about key's, indexes, ect. That's why I'm looking for somehow trap the auto generated message and display something simple of my own.

Docmd.SetWarning=false is ok for not showing any warning/messages, but that would leave the user kinda clueless and probably thinking they are doing a great job.

 

[dapfeifer]

You could disable warnings, then use MsgBox to create your own warning. Despite having warnings disabled the message box will still display.

 

[zackat]

Thanks dapfeifer but that will show the message regardless if something is wrong. I only want to display the message if something goes "wrong" during the transfertext command.

This is what I got:
DoCmd.TransferText acImportDelim, "import_specification", "tbl_Import", "C:\Data\test.csv", vbYes

adding a msgbox after this will always be executed, what I don't want to happen.

I somehow have to raise an error or trap the event that handles the build-in message and then display a msgbox.

 

[maxmangion]

did you try putting the warnings and msgbox in an error handler i.e.

On Error Goto ErrorHandler
your code is here
ErrorHandler:
DoCmd.SetWarnings False
Msgbox ...
DoCmd.SetWarnings True
etc

This way your msgbox should appear only if your code encounters an error.

 

[zackat]

maxmangion, yes i do have it in a error handler and just like you mention. The thing is it isn't an error, but just a message and somehow displays the message. Still showing the build-in message that comes with, Yes No Cancel and help buttons.

The message is:
Microsoft Office Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 2 record(s) were lost due to key violations.
Do you want to proceed anyway?
YES - NO - CANCEL - HELP

To be complete this is the whole (simple) procedure
Sub Import()
On Error GoTo Import_Error

DoCmd.TransferText acImportDelim, "import_specification", "tbl_Import", "P:\Data\test.csv", vbYes


On Error GoTo 0
Exit Sub

Import_Error:
DoCmd.SetWarnings True
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import of Module Module1"
DoCmd.SetWarnings True

End Sub

 

[maxmangion]

Hi,

In the Import_Error: the first DoCmd.SetWarnings should be set to False.

 

[maxmangion]

i did a google search and the error you're encountering seems to have the number 10014, so you may try to trap that error:

if err.number = 10014 Then
your msgbox goes here.