In one macro I would like to get external data from Access, and use that
data. I am able to perform an Access query on its own, but it doesn't seem to
update until the macro is finnished. This means that I cannot reference the
data the I am calling. While the macro is running (and the external data have
not come yet) there is a message:

ExternalData_23: Getting Data...

Is there a way to force the external data to come in before the program
moves on.

Here is the basic structure of the macro (varConn and varSql are strings
that have connection and SQL information).

Sub query()

Dim varQuery As QueryTable
Set varQuery = ActiveSheet.QueryTables.Add(Connection:=varConn,
Destination:=Sheet1.Range("A1"), Sql:=varSql)

varQuery.Refresh

Dim i As Integer
i = 2
Do Until Sheet1.Cells(i, 1) = ""
'Do Something
Loop

End Sub

Thanks in advance!

Would it be as convenient to set up a sheet with links to the Access query?
(Data Import External Data New Database Query)

This way the link can be updated quickly and your macro could draw from that
sheet.
--
Best wishes,

Jim


"Andy" wrote:

In one macro I would like to get external data from Access, and use that
data. I am able to perform an Access query on its own, but it doesn't seem to
update until the macro is finnished. This means that I cannot reference the
data the I am calling. While the macro is running (and the external data have
not come yet) there is a message:

ExternalData_23: Getting Data...

Is there a way to force the external data to come in before the program
moves on.

Here is the basic structure of the macro (varConn and varSql are strings
that have connection and SQL information).

Sub query()

Dim varQuery As QueryTable
Set varQuery = ActiveSheet.QueryTables.Add(Connection:=varConn,
Destination:=Sheet1.Range("A1"), Sql:=varSql)

varQuery.Refresh

Dim i As Integer
i = 2
Do Until Sheet1.Cells(i, 1) = ""
'Do Something
Loop

End Sub

Thanks in advance!

Basically, I'm asking the user a series of questions and the query will
depend on the answers. As far as I know, setting up links will not suit my
needs.
Thanks

"Jim Jackson" wrote:

Would it be as convenient to set up a sheet with links to the Access query?
(Data Import External Data New Database Query)

This way the link can be updated quickly and your macro could draw from that
sheet.
--
Best wishes,

Jim


"Andy" wrote:

In one macro I would like to get external data from Access, and use that
data. I am able to perform an Access query on its own, but it doesn't seem to
update until the macro is finnished. This means that I cannot reference the
data the I am calling. While the macro is running (and the external data have
not come yet) there is a message:

ExternalData_23: Getting Data...

Is there a way to force the external data to come in before the program
moves on.

Here is the basic structure of the macro (varConn and varSql are strings
that have connection and SQL information).

Sub query()

Dim varQuery As QueryTable
Set varQuery = ActiveSheet.QueryTables.Add(Connection:=varConn,
Destination:=Sheet1.Range("A1"), Sql:=varSql)

varQuery.Refresh

Dim i As Integer
i = 2
Do Until Sheet1.Cells(i, 1) = ""
'Do Something
Loop

End Sub

Thanks in advance!

Try setting BackgroundQuery to False for the querytable.

Tim


"Andy" wrote in message
...
In one macro I would like to get external data from Access, and use that
data. I am able to perform an Access query on its own, but it doesn't seem
to
update until the macro is finnished. This means that I cannot reference
the
data the I am calling. While the macro is running (and the external data
have
not come yet) there is a message:

ExternalData_23: Getting Data...

Is there a way to force the external data to come in before the program
moves on.

Here is the basic structure of the macro (varConn and varSql are strings
that have connection and SQL information).

Sub query()

Dim varQuery As QueryTable
Set varQuery = ActiveSheet.QueryTables.Add(Connection:=varConn,
Destination:=Sheet1.Range("A1"), Sql:=varSql)

varQuery.Refresh

Dim i As Integer
i = 2
Do Until Sheet1.Cells(i, 1) = ""
'Do Something
Loop

End Sub

Thanks in advance!

That was exactly what I was looking for. Thanks

"Tim" wrote:

Try setting BackgroundQuery to False for the querytable.

Tim


"Andy" wrote in message
...
In one macro I would like to get external data from Access, and use that
data. I am able to perform an Access query on its own, but it doesn't seem
to
update until the macro is finnished. This means that I cannot reference
the
data the I am calling. While the macro is running (and the external data
have
not come yet) there is a message:

ExternalData_23: Getting Data...

Is there a way to force the external data to come in before the program
moves on.

Here is the basic structure of the macro (varConn and varSql are strings
that have connection and SQL information).

Sub query()

Dim varQuery As QueryTable
Set varQuery = ActiveSheet.QueryTables.Add(Connection:=varConn,
Destination:=Sheet1.Range("A1"), Sql:=varSql)

varQuery.Refresh

Dim i As Integer
i = 2
Do Until Sheet1.Cells(i, 1) = ""
'Do Something
Loop

End Sub

Thanks in advance!