current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have an excel sheet which i use with an access database. I want to open the database and use different sub routines with different queries. However i don't know hot to publically assign an variable to use the database so i have to create the connection within each subroutine and open and close this.

I have a fealing that this must be able to do smarter so i don't have the same code written all over it.

I open the database with the following code and have to do this over and over for every query:

Public Sub copy_to_sql()

    Dim Cn As New ADODB.Connection
    Dim Rs As New ADODB.Recordset
    Dim Cmd As New ADODB.Command

    Dim strMDB As String

    strMDB = ThisWorkbook.Path & "\klantserverinfo.accdb"
    ' Set Cn = CreateObject("ADODB.Connection")
    Cn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & strMDB
    Cn.Open

    '--- Do some queries like

    SqlQuery = "SELECT * FROM database"

    Set Cmd.ActiveConnection = Cn
    Cmd.CommandText = SqlQuery
    Cmd.CommandType = adCmdText

    Set Rs.Source = Cmd
    Rs.Open    

    Cn.close
End Sub

Does anyone know how to do this better?

Ps, I use Office 2010 and the database used is an access 2010 database

share|improve this question

1 Answer 1

up vote 3 down vote

You could just take an input ( query string ) into your function 

Sub ExecuteQuery(query$) 
    ' your code
End sub

Then modify the query string inside your sub

SqlQuery = query

Then call it all from the main sub

Sub MainSub()

    Dim yourQuery$
    yourQuery = "SELECT * FROM database"

    ExecuteQuery query:=yourQuery     'same as:     Call ExecuteQuery(yourQuery)

End Sub

You could take more parameters into your ExecuteQuery subroutine, for example: location to save recordset to, worksheet by reference, connection string, etc. i hope you get the picture


once you understand the above fundamentals of object oriented coding you can expand a bit and implement funcitons like OpenDb(), Status(), CloseDb() to even make it easier for yourself since it seems like youre running sql against the db quite frequently.

Remember its the good design that makes the coding easier and more efficient

share|improve this answer
    
Thanks for the quick and clear answer... Never thought of this simple approach :-) –  alfl May 8 '13 at 10:06
    
@alfl youre welcome –  me how May 8 '13 at 10:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.