PDA

View Full Version : Solved: Excel Code Efficiency with DB Connection



taporctv
07-12-2007, 05:20 AM
I already have a MS Access DB with queries stored within it. As of now with my code within Excel, I'm creating an ado connection, creating a recordset, and using a SQL string to populate the recordset. Is there anyway possible to just run the query already saved in Access to populate a recordset in Excel. I find that my excel code is beginning to look sloppy with all this SQL code thrown in there.

Ebrow
07-12-2007, 05:36 AM
Hi.

Try this out.


Sub runQueryFromDB()
'Add the reference to Microsoft Access 11.0 Lib.
'Add the DAO 3.6 reference
Dim myAccess As Access.Application
Dim myDB As Database
Dim myRS As Recordset
Set myAccess = CreateObject("Access.Application")
myAccess.Visible = True 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase ("C:\Test\Test.mdb")
'open access query normally
myAccess.DoCmd.OpenQuery ("qryQuery1")
'create recordset
Set myDB = myAccess.CurrentDb()
Set myRecordset = myDB.OpenRecordset("qryQuery1")
End Sub

Bob Phillips
07-12-2007, 05:40 AM
Why? Looks sloppy? Works well? Where are your priorities?

Bob Phillips
07-12-2007, 05:47 AM
This might help

http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1

taporctv
07-12-2007, 05:57 AM
Why? Looks sloppy? Works well? Where are your priorities?

Im just not sure on the best way of doing it. I already have the queries stored in Access. I just want to know which way was more efficient. Opening an Access object and executing the query or just copying the SQL from access and using it in excel.

Bob Phillips
07-12-2007, 06:08 AM
If you have the stored queries in Access, I would suggest that is the way to go.

Even better would be to write a nice Query class to access the queries.

taporctv
07-12-2007, 06:13 AM
So try Ebrows method? That would make my VBA look a lot less cluttered.

taporctv
07-12-2007, 07:19 AM
Public Sub runQueryFromDB(query As String, ByRef myRS As Recordset)
'Add the reference to Microsoft Access 11.0 Lib.
'Add the DAO 3.6 reference
Dim myAccess As Access.Application
Dim myDB As Database


Set myAccess = CreateObject("Access.Application")
myAccess.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase ("C:\Documents and Settings\IntTesting\IA Testing.mdb")
'open access query normally
myAccess.DoCmd.OpenQuery (query)
'create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)

End Sub
im getting a type mismatch error in the last line. myRS is delcared as a Recordset. Does myDB.OpenRecordset(query) return something else besides a recordset?

Ebrow
07-12-2007, 10:50 AM
Function runQueryFromDB(query As String) as recordset 'Add the reference to Microsoft Access 11.0 Lib. 'Add the DAO 3.6 reference Dim myAccess As Access.Application Dim myDB As Database Set myAccess = CreateObject("Access.Application") myAccess.Visible = False 'if you don't run this the database will run in the background hidden myAccess.OpenCurrentDatabase ("C:\Documents and Settings\IntTesting\IA Testing.mdb") 'create recordset Set myDB = myAccess.CurrentDb() Set runQueryFromDB = myDB.OpenRecordset(query) End Function Sub Test Dim myRS as recordsetSet myRS = runQueryFromDB(?qryQuery1?) End sub



Are you trying to do this??

I don't understand why you where passing the recordset variable in and then set it on the sub. I am guessing you want to be able to set a variable else were in another sub by using the function.

Ebrow
07-12-2007, 10:52 AM
That went wierd. Sorry here is the code:


Function runQueryFromDB(query As String) as recordset

'Add the reference to Microsoft Access 11.0 Lib.
'Add the DAO 3.6 reference

Dim myAccess As Access.Application
Dim myDB As Database

Set myAccess = CreateObject("Access.Application")
myAccess.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase ("C:\Documents and Settings\IntTesting\IA Testing.mdb")

'create recordset
Set myDB = myAccess.CurrentDb()
Set runQueryFromDB = myDB.OpenRecordset(query)

End Function


Sub Test

Dim myRS as recordset
Set myRS = runQueryFromDB(“qryQuery1”)

End sub

taporctv
07-13-2007, 05:46 AM
Im still getting a type mismatch at this line within the function

Set runQueryFromDB = myDB.OpenRecordset(query)

I have no idea what I could be doing wrong.

Ebrow
07-13-2007, 09:16 AM
Have you set the DAO 3.5 reference?

query should be in quotation marks and the query name, so I am guessing that you are using query as a variable then?

Try this code instead:-


Sub Test

Dim myRS As DAO.recordset
Set myRS = runQueryFromDB(Query)

End Sub

taporctv
07-13-2007, 09:35 AM
Thanks. That helped. I also had to add that to the function.

Function runQueryFromDB(query As String) As dao.recordset

My only problem is Access still opens regardless of the fact I set visible to false.

OdiN
07-13-2007, 10:27 AM
Sometimes with Access I've seen issues where you have to explicitly reference the object - i.e.:

Access.Application.Visible = True

taporctv
07-16-2007, 05:49 AM
Access keeps opening up and not closing despite the code I have. Is there anything that i'm missing?

Public Sub runQueryFromDB(query As String, myRS As DAO.Recordset)

'Add the reference to Microsoft Access 11.0 Lib.
'Add the DAO 3.6 reference
Dim myAccess As access.Application
Dim myDB As Database
Set myAccess = CreateObject("Access.Application")


'myAccess.Application.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase ("C:\Documents and Settings\IntTesting\IA Testing.mdb")
'open access query normally
myAccess.DoCmd.OpenQuery (query)
'create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
myAccess.Application.Quit
Set myAccess = Nothing

End Sub

When I delete the sheet where the tables were loaded, then access will close.

Bob Phillips
07-16-2007, 06:20 AM
Just try

myAccess.Quit

taporctv
07-16-2007, 06:26 AM
I tried that but its not working. I dont get it. I have another module that opens access and closes access and all i used there was myaccess.quit. I dont understand why its not working in my current module.

alimcpill
07-16-2007, 07:34 AM
Can't you just use your existing method posted at the top of this topic, but pass the name of a query in the db rather than a sql string when you open the recordset? Surely there's no need to actually automate an instance of Access as that would seem to be an unnecessary overhead.

taporctv
07-16-2007, 08:14 AM
Can't you just use your existing method posted at the top of this topic, but pass the name of a query in the db rather than a sql string when you open the recordset? Surely there's no need to actually automate an instance of Access as that would seem to be an unnecessary overhead.

I dont know if that's possible. If it is, do you know how to implement that in code.

alimcpill
07-16-2007, 08:38 AM
Well as I say, it sounds like you are pretty close to it already from what you said in the first post, but say you had an open connection to an access DB called 'conn' and new recordset object called 'rs', then to run some sql and put the results in the recordset you'd presumably use something like

rs.Open "SELECT * FROM MyTable", conn

However, if you've got a query in your database called 'MyQuery' which returns the results you want, and you don't want to copy the sql across to your vba code (and why would you), you just go

rs.Open "MyQuery", conn

and it should work. There's various ways to do this, but this sounds closest to what you describe at the top. If you want you can specify that you are passing a query name rather than a sql string, but there's no need to as ADO will work it out at runtime. It might improve performance a tiny bit, but for a single shot like this it'd be negligible.

taporctv
07-16-2007, 10:27 AM
Your code didnt work for me. Maybe there might be something extra i need to add.