Solved Generate error mesage for import errors (1 Viewer)

[Jimal]

Hello saviors..

I'm importing tables into my access database by a user form
however I understand there are some errors -Type Conversion Failure
these errors are stored in a new table without my knowledge - I mean without any error prompt

Can you please suggest a code to pop an error message If one such error table is generated upon imporing my table...

thanks in advance

 

[Isaac]

You could add a line of code to check if the name of the import table exists

 

[theDBguy]

Maybe @Isaac meant to check for tables with "ImportErrors" in the name.

Just a guess...

 

[Jimal]

well @theDBguy seems like an idea... can u mind helping with a code as im not an expert in the VBA code

 

[theDBguy]

well @theDBguy seems like an idea... can u mind helping with a code as im not an expert in the VBA code

Hi. Sorry, not in front of a computer now to write good code for you; but, try searching for the keyword "TableExists." I think I've seen a sample function with that name.

I can post something tomorrow, if you still need one.

Sent from phone...

 

[Jimal]

thank you @theDBguy
I tried some but in vain
I have a error table generate by name C_TERMINATIOSN$_ImportErrors
but when I used a public function as below
-----------------------------------------------------------------------------------
Public Function ifTableExists(tblName As String) As Boolean

If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

ifTableExists = True

End If

End Function
-----------------------------------------------------------------------------------

and called the function as
call ((C_TERMINATIOSN$_ImportErrors))

it is showing syntax errors for invalid charectors etc.,

pls help

 

[arnelgp]

If there is a wizard on the import.
Try to follow it and Correctly Identify the Datatype of each columns.

 

[Gasman]

thank you @theDBguy
I tried some but in vain
I have a error table generate by name C_TERMINATIOSN$_ImportErrors
but when I used a public function as below
-----------------------------------------------------------------------------------
Public Function ifTableExists(tblName As String) As Boolean

If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

ifTableExists = True

End If

End Function
-----------------------------------------------------------------------------------

and called the function as
call ((C_TERMINATIOSN$_ImportErrors))

it is showing syntax errors for invalid charectors etc.,

pls help

Well for a start I would expect you would have to call the function as

Call ("C_TERMINATIOSN$_ImportErrors")

but I would hazard a guess the table is called C_TERMINATIOSN, in the system table, even if it looks like it has been spelt incorrectly.?
You would need to see exactly what is stored in MSysobject for that file?

I am going to go to my grave saying this , but 'Walk though the code in the debugger to see what it is ACTUALLY doing, not what you THINK it is doing'.

 

[Jimal]

Hi @Gasman

You are right the actual imported file / stored table name is C_TERMINATIOSN ,whereas the generated error table is C_TERMINATIOSN$_ImportErrors

I should need to check for the error table right...that's what I did

 

[Gasman]

Hi @Gasman

You are right the actual imported file / stored table name is C_TERMINATIOSN ,whereas the generated error table is C_TERMINATIOSN$_ImportErrors

I should need to check for the error table right...that's what I did

What is it stored as in the system table though? as that is where you are looking?

 

[arnelgp]

use the wizard.
this will give a chance to set the Column's Datatype.
if you have mixed data in a column, like numeric and text, access might
interpret the column as numeric. therefore some of the records as not converted if
they have text.

 

[CJ_London]

and called the function as
call ((C_TERMINATIOSN$_ImportErrors))

it is showing syntax errors for invalid charectors etc.,

would think a) you don't want to use call since you need to return a value and b) you need to specify the name of your function. i.e. you need something like this

if  ifTableExists("C_TERMINATIOSN$_ImportErrors") then
   'do something
else
   'do something else
end if

 

[theDBguy]

thank you @theDBguy
I tried some but in vain
I have a error table generate by name C_TERMINATIOSN$_ImportErrors
but when I used a public function as below
-----------------------------------------------------------------------------------
Public Function ifTableExists(tblName As String) As Boolean

If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

ifTableExists = True

End If

End Function
-----------------------------------------------------------------------------------

and called the function as
call ((C_TERMINATIOSN$_ImportErrors))

it is showing syntax errors for invalid charectors etc.,

pls help

Hi. I go to bed and wake up to a lot of activities here.

I would agree with @CJ_London on this one. Give it a try and let us know how it goes. Cheers!

 

[Isaac]

Try this.

Function TableExists(strName As String) As Boolean
Dim td As DAO.TableDef
On Error Resume Next
Set td = CurrentDb.TableDefs(strName)
If Err.Number <> 0 Then
    TableExists = False
Else
    TableExists = True
End If
End Function

If TableExists("C_TERMINATIOSN$_ImportErrors")=True then
  msgbox "File imported with errors, please review",vbinformation,"  "
end if

 

[Jimal]

thank you @Isaac it worked thank you all ….