How to reset focus back to vb?

[hosackies]

Hi All,

I am a novice at VB and am trying to write a program that will run a MS Access macro. Here's what i have so far...

Dim MSAC As Access.Application
Dim db_name As String

Private Sub Command1_Click()

db_name = "C:\Database.mdb"

MSAC.OpenCurrentDatabase ("C:\Database.mdb")
MSAC.Visible = True

MSAC.DoCmd.RunMacro "Macro1"

SendKeys "password"

End Sub

Macro1 run a series of queries that pulls from an SQL server via ODBC linked tables, which requires login and password.

the problem occurs when the runmacro line executes, the focus is set to MS Access, and VB will not continue to run unless the login and password is entered.

but the purpose of this program is to automate that login process by using sendkeys commands.

How do i reset the focus back to VB so that it will continue to the sendkey commands to MS Access?

Thanks so much
hosackies

[Hack]

Welcome to the forums.

Instead of running the series of queries from an Access Macro, run them straight from your VB6 program.

[hosackies]

thanks for the welcome and the quick reply Hack...

Can you please clarify on what you mean by running the macro straight from VB?

do you mean setting up a ADO connection?
or is there some way to invoke the Access macro without opening an Access application?

-Hosackies

[Hack]

I don't mean run the macro. I mean forget the macro.

Run your SQL queries straight from your VB6 project (and yes, using an ADO Connection and ADO Recordset objects)

[hosackies]

Hack,

is there another way around this? because i will not be the moderator to the access macro. this VB program will act as a remote trigger to run the access macro maintained by someone else...

thanks
Hosackies

[Hack]

i will not be the moderator to the access macro. this VB program will act as a remote trigger to run the access macro maintained by someone else...

Well, then that does pose a problem for my solution.

the problem occurs when the runmacro line executes, the focus is set to MS Access, and VB will not continue to run unless the login and password is entered.

What does the VB program do after it tells the macro to run? In other words, what is VB supposed to be doing that gets stopped because of a loss of focus?

[hosackies]

The purpose of the VB program is to trigger the MS access macro.

here are the steps that it should go through:

1. open Access database
2. invoke Access Macros
3. when prompted, sendkey login and password information for SQL server for ODBC linked tables <--- this is the step when the process halts, vb wont advance until the prompts are entered, focus is set in MS Access
4. sleep until Access macros completes
5. create and send email
6. close ms Access and program

[hosackies]

I am also looking into an alternate method into solving this problem.

my search had lead to several cases on the internet that had the same problem as i have, but none seem to have a proper solution involving resetting focus to vb and using the sendkeys function.

but one of the cases suggested to reconnecting all ODBC connections when the MS Access application is open, BEFORE invoking any macros. that way, running the macros will not require any logins, eliminating step 3 from my previous post.

here's a page that has the code that does the trick.
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

it seems to be working right for the tests i am running right now.