Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
Daniel,
This should get you started: http://www.access-programmers.co.uk/...29ea& t=27242 NickHK "Daniel Bonallack" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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/...29ea& t=27242 NickHK "Daniel Bonallack" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Access from Excel
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" wrote in message ... 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/...29ea& t=27242 NickHK "Daniel Bonallack" wrote in message ... 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 --------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open access from excel ? | Excel Discussion (Misc queries) | |||
Excel cannot open Access MDB | Excel Discussion (Misc queries) | |||
help to open pwd Excel from Access | Excel Programming | |||
Open Access db from Excel | Excel Programming | |||
Open Excel from access | Excel Programming |