OK, thanks. I posted a "what's causing the delay" question to the forum a
while back, but didn't get an answer. Simply put, if I have Access open, the
data extracts faster. If Access is closed, it still works, but instead of 8
seconds, it can be closer to 90 seconds.

If you have any ideas, I'd love to hear them. And if you know of simple
code to open Access anyway, I'd still like to learn it, even if that isn't my
problem here.



"NickHK" wrote:

Daniel,
I see no reason why you would need to open Access; you are using Jet/OLEDB
to query the .mdb, so Access is irrelevant.
The source of the apparent delay lies elsewhere.

NickHK

"Daniel Bonallack" wrote in
message ...
I guess this would be the basic code.
So I want to check that Access is open, and preferably have a query open
in
design view
Thanks!

Sub GetBaseData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset

DBFullName = "xxx.mdb"


' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct


With Recordset
Src = "daniel's sql query code"
.Open Source:=Src, ActiveConnection:=Connection

Sheets("data").Select

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

"NickHK" wrote:

Daniel,
You using VBA with ADO recordset or Excel's DataGet External data
functionality.
Just post the opening code.

NickHK

"Daniel Bonallack" wrote in
message ...
Hi Nick

Thanks for responding. I'm using VBA code in Excel to run an SQL
query on
the closed Access database, and import the data into Excel.

Do you need me to be more specific? I can copy in the code if you
want,
but
it's quite long.

Daniel


"NickHK" wrote:

Daniel,
How are you pulling the data into excel ?

NickHK

"Daniel Bonallack" wrote
in
message ...
I currently have code in Excel VBA that pulls data from Access in
my
Excel
worksheet. However, it runs about 10 times faster if Access is
already
open.
And (I'm not sure of this), I think it helps if an Access query
is
open
and
in design view.

Can I have the code to:
1. Open Access database XXXX.mdb, if it is not already open
2. Open the query YYYY in design view
3. Ensure that the Excel worksheet is still active

Hope this isn't a big ask - thanks very much in advance

regards
Daniel Bonallack