Run .mdb in background 1

[NuniPR]

Hi Guys!

I have the following code:

   Dim strAccessFile As String
strAccessFile = App.Path & "\EQ\MYACCESSDATABASE.mdb"
Shell "RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strAccessFile, vbHide

When the mdb opens the splash screen for Access shows momentarily (1 second or less), but it leaves the "shadow" of where it showed embedded in the middle of the screen!

Do you have any suggestion as to what to do in order to run the mdb in the background so that when the Access splash screen shows it does not affect the main screen of my app? (Or that it simply doesn't show when the shell runs).

Thanks in advance.

Nunina
San Juan, PR

 

[NuniPR]

Sorry guys!

I should mention that this mdb runs an AutoExec module that opens a form in hiding. This form runs a code that converts an xls file into a dbf. Then it takes the dbf and links it to the mdb.

Then it runs a query from both the dbf and a table in my production db (Oracle). It sounds complicated, but it's not. And it works beautifully.

FYI.

Thanks again.

Nunina

 

[bjd4jc]

Have you thought about using the Access Object Libray to directly execute the Macro rather than using the Shell function? This would definately eliminate the splash screen.

 

[NuniPR]

Ok, can you help me out with that? How do I call it?

Thanks, bjd4jc.

Nunina

 

[NuniPR]

Hmmm... we have a little issue. Like I said before, I am not using this Access mdb as a database. I guess I should've explain a little better.

My production DB is Oracle. I have infomation that needs to be uploaded into Oracle, once it has been purged, and "cleaned" in the Excel file. Since the Excel file is being handled by a specific user, the upload to Oracle should be as smooth as possible.

When I tried uploading the data in the xls to oracle using VB, it took a loooooong time. So, I decided to link my Oracle table to Acces, convert the xls to dbf (it was giving problems with the columns' names if I tried to load it directly from excel to Oracle), and then load the dbf data to Oracle.

Do you really think I should use DAO just to run a Macro?

Thanks again.

Nunina

 

[bjd4jc]

Well, I am not talking about DAO but about Microsoft Access x.x Object Library. I really don't see how it would be much different than running Access from a shell command...

Add a reference to the Library mentioned above and then use code simlar to the following:

    Dim objAccess As Access.Application
    
    Set objAccess = New Access.Application
    
    objAccess.OpenCurrentDatabase ("C:\db1.mdb")
    objAccess.DoCmd.RunMacro "MacroName"
    objAccess.CloseCurrentDatabase
    Set objAccess = Nothing

If you're concerned with the overhead then it really isn't any more than opening access up to run the AutoExec macro (It could potentially be less) and it has the added benefit of not showing the splash screen...

 

[NuniPR]

bjd4jc:

Thanks so much for your help. Here is a star for you!!!

Gracias.

Nunina

 

[bjd4jc]

You're welcome, thanks for the star

 

[BobRodes]

One thing to be careful of is marshaling overhead. Try to make as few calls as possible using the Access.Application object; you're better off calling a macro or a top-level procedure in the Access environment. That's exactly what bjd4jc has done, but I wanted to point it out in case you weren't aware of it. For example, don't do something like iterate through a table record by record using this object, instead do that in a VBA proc and call that proc from this object.

HTH

Bob

 

[NuniPR]

Hi Guys,

Now, there is one little detail. When Access is running, my VB form does not show. I have a form with a progress bar, that simply "disappears" when Access is running, making the user believe that the application froze.

Any suggestions?

Thanks again,

Nunina

 

[BobRodes]

Going on little information, I'd look into DoEvents first.