Open Access from Excel

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

Daniel,
How are you pulling the data into excel ?

NickHK

"Daniel Bonallack" <[email protected]> wrote in
message news:[email protected]...
> 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

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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
>
>
>

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

NickHK

"Daniel Bonallack" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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
> >
> >
> >

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 Data>Get External data
> functionality.
> Just post the opening code.
>
> NickHK
>
> "Daniel Bonallack" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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 Data>Get External data
> > functionality.
> > Just post the opening code.
> >
> > NickHK
> >
> > "Daniel Bonallack" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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" <[email protected]> wrote
in
> > > > message news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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 Data>Get External data
> > > functionality.
> > > Just post the opening code.
> > >
> > > NickHK
> > >
> > > "Daniel Bonallack" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > 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" <[email protected]> wrote
> in
> > > > > message news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Daniel,
This should get you started:
http://www.access-programmers.co.uk/...b229ea&t=27242

NickHK

"Daniel Bonallack" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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 Data>Get External data
> > > > functionality.
> > > > Just post the opening code.
> > > >
> > > > NickHK
> > > >
> > > > "Daniel Bonallack" <[email protected]> wrote
in
> > > > message news:[email protected]...
> > > > > 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" <[email protected]>
wrote
> > in
> > > > > > message
news:[email protected]...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Nick

Thanks very much. Looking at some of the other posts today, I notice that
you try to find out what someone is trying to achieve, rather than just
giving them an answer. That's really helpful - I often think I know what I'm
doing, but then it turns out I'm approaching the problem the wrong way.

Thanks again for your help today.

regards
Daniel







"NickHK" wrote:

> Daniel,
> This should get you started:
> http://www.access-programmers.co.uk/...b229ea&t=27242
>
> NickHK
>
> "Daniel Bonallack" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > 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 Data>Get External data
> > > > > functionality.
> > > > > Just post the opening code.
> > > > >
> > > > > NickHK
> > > > >
> > > > > "Daniel Bonallack" <[email protected]> wrote
> in
> > > > > message news:[email protected]...
> > > > > > 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" <[email protected]>
> wrote
> > > in
> > > > > > > message
> news:[email protected]...
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Daniel,
My work is kind of slow at the moment, so I have more time to contribute.
There's normally many ways to perform similar actions, so if you know the
ultimate goal, it make the choice of methods more rational.
For example the recent poster who insists on running code in edit mode. As
we have no idea what he is trying to achieve, it is not possible to offer
alternatives apart from "No can do".

NickHK

"Daniel Bonallack" <[email protected]> wrote in
message news:[email protected]...
> Hi Nick
>
> Thanks very much. Looking at some of the other posts today, I notice that
> you try to find out what someone is trying to achieve, rather than just
> giving them an answer. That's really helpful - I often think I know what
I'm
> doing, but then it turns out I'm approaching the problem the wrong way.
>
> Thanks again for your help today.
>
> regards
> Daniel
>
> "NickHK" wrote:
>
> > Daniel,
> > This should get you started:
> >
http://www.access-programmers.co.uk/...b229ea&t=27242
> >
> > NickHK
> >
> > "Daniel Bonallack" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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.
---------------------------- CUT ---------