running one macro in one databases from another...

[ease20022002]

Hi,

I am currently running a database from another database. Everything works fine, but I am trying to put in a precautionary step.

I have a form from the 1st database that imports a .csv file into another database. The only "problem" I am having with my precautionary step is that when I purposely corrupt the .csv file before importing to check if some type of error message would be genereated importing the macro with specs, etc., I do not see an error box appear stating there were errors importing my text file and have me press OK to see the error table, but unfortuantely no errors are produced. How do you make sure with some return code that the error message will appear when I am doing all this from another database.

I have already tried DoCmd.SetWarnings(WarningsOn).

Here is the code I have that runs the macro that does a simple TransferText method in the other database:

I set appAccess as an Access.Application. If there there is something outside this code I must do, please let me know.

code:
--------------------------------------------------------------------------------

With appAccess
.OpenCurrentDatabase strPath & strFile
.DoCmd.RunMacro mcrName2 ' macro is simple transfertext with with warnings on
.CloseCurrentDatabase
.Quit

End With
--------------------------------------------------------------------------------

Again, I have tried entering .DoCmd.SetWarnings(WarningsOn), it doesn't work. I have also tried making the application visible...unfrotunately..nothing...

Any help would be greatly appreciated

 

[rubbernilly]

If your import errors produced another table, you could check the TableDefs() count of the other database before and after your code runs. This would tell you that at least 1 record caused an error, if not how many.

If you decide to go that way and need help with that code, post back. I've got to go now, but I'll be back on later.

HTH

 

[Remou]

Hi easy20022002
Do you have to have a macro? If not, something like this might suit:

On Error GoTo HandleError
...
With appaccess
   ...
   .DoCmd.TransferText acImportDelim, , "tblTable", "FileName.txt"
   ...    
End With
HandleError:
...
MsgBox Err.Number & " " & Err.Description

 

[ease20022002]

rubbernilly, or anyone, yes, I would like some help with the TableDef count...I will try it on my own, but anyhelp would be greatly appreciated...

Thanks...

 

[rubbernilly]

Try this. (To run this, you'll need a reference to the DAO library.)

Public Function CheckDefs()
Dim db As DAO.Database
Dim iPre As Integer, iPost As Integer, i As Integer

Set db = OpenDatabase(strPath & strName)

iPre = db.TableDefs.Count
[green]'put code here to run macro[/green]
iPost = db.TableDefs.Count

If iPost > iPre Then 'new table was added
    MsgBox "Import caused errors.", vbOKOnly
End If

[green]'if needed, this code will iterate the TableDef Names[/green]
For i = 0 To iPost - 1
    Debug.Print db.TableDefs(i).Name
Next i

Set db = Nothing
End Function

You might have to release the db object and then recapture it (release it before your macro, then recapture afterwards). I don't know how the DAO database will react with the database as your macro-running code opens it. Try it, and if you need to release the object, you should see how from the code example.

HTH

 

[ease20022002]

rubbernilly,

Can you believe that an error table will not show up on a TableDef count? Well, it doesn't for some bizarre reason. I tested it creating a perfectly normal table and the count went from 11 to 12.

After running the import with the error table being generated, the count stayed between 11 and 11.

I placed the code into the actual database that performs the import and i got the same answer, 11 and 11.

Any other suggestions???

I find it truely bizarre that the tabledef.count won't catch the import error table, very strange...

 

[rubbernilly]

Maybe you're describing the situation that I was talking about.

Test this...

In a database that does not have an import error table, type the following into an immediate window (Ctrl+G in the VBE window):

?currentdb.Tabledefs.Count

and see what the return is. Then run the import with a corrupted file and make the error table get generated. Go back to your immediate window and run that same line again.

Does it increment?

If so, then the error table will show up in the TableDefs count (I can't see why it wouldn't). The problem is that you have an instance of the database open before the table gets created. So, if all of this turns out to be the case, try this code:

Public Function CheckDefs()
Dim db As DAO.Database
Dim iPre As Integer, iPost As Integer, i As Integer

Set db = OpenDatabase(strPath & strName)

iPre = db.TableDefs.Count
[red]set db = nothing[/red]

'put code here to run macro

[red]Set db = OpenDatabase(strPath & strName)[/red]
iPost = db.TableDefs.Count

If iPost > iPre Then 'new table was added
    MsgBox "Import caused errors.", vbOKOnly
End If

'if needed, this code will iterate the TableDef Names
For i = 0 To iPost - 1
    Debug.Print db.TableDefs(i).Name
Next i

Set db = Nothing
End Function

This will release the database object and then recapture it, hopefully with the error table included in the TableDefs.

HTH

 

[ease20022002]

Actually, I was just coming back to tell you I tried exactly what you posted previous and viola, it worked...

Thanks a bunch, rubbernilly.

You have any idea why the error messages are turned off when data type import errors occur while importing?

 

[rubbernilly]

My best guess is probably because you are automating an instance of Access rather than actually opening access and doing the equivalent of "Send Keys" (which is *not* a suggestion, just a comparison).