Solved: Excel Code Efficiency with DB Connection

[taporctv]

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]

Hi.

Try this out.

[VBA]
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
[/VBA]

[Bob Phillips]

Why? Looks sloppy? Works well? Where are your priorities?

[Bob Phillips]

This might help

http://www.stardeveloper.com/article...1050101&page=1

[taporctv]

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]

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]

So try Ebrows method? That would make my VBA look a lot less cluttered.

[taporctv]

[vba]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[/vba]
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]

[VBA]
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 FunctionSub TestDim myRS as recordsetSet myRS = runQueryFromDB(?qryQuery1?) End sub

[/VBA]

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]

That went wierd. Sorry here is the code:

[VBA]
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
[/VBA]

[taporctv]

Im still getting a type mismatch at this line within the function

[VBA]Set runQueryFromDB = myDB.OpenRecordset(query) [/VBA]

I have no idea what I could be doing wrong.

[Ebrow]

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:-

[VBA]
Sub Test

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

End Sub
[/VBA]

[taporctv]

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]

Sometimes with Access I've seen issues where you have to explicitly reference the object - i.e.:

Access.Application.Visible = True

[taporctv]

Access keeps opening up and not closing despite the code I have. Is there anything that i'm missing?

[vba]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[/vba]

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

[Bob Phillips]

Just try

myAccess.Quit

[taporctv]

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]

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]

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]

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

[vba]rs.Open "SELECT * FROM MyTable", conn[/vba]

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

[vba]rs.Open "MyQuery", conn[/vba]

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.