Connecting to Different Databases from MS Access

[pod]

Here is some vba code I use to connect to three types of databases.

The "somefunctioncall" function makes use of the "opendb" function to connect to a database...
You will have to put in your parameters in the latter function and uncomment the "preferred connection string"


You might have some fiddling to do with this code, but it should work... I got those strings off the web by googling "connection strings" ...

Those databases do not have to be on the same PC, they can be on any server as long as you have access to them.

[2014-09-27: z:per OP request, revised code block follows ]

 
    Public Function opendb() As ADODB.Connection
            ' This function returns an ADODB.Connection object 
            ' which is required for accessing different types of databases
            '
            ' Her we declare three strings to access database other than MS Access files on your network
            ' as it was in my case
            Dim ORACLE_ConnString As String
            Dim SQL_ConnString As String
            Dim MSACCESS_ConnString As String
            '*****************************************************************
            ' This variable is used only when accessing a MS Access database
            ' NOTE: I always keep the form separate from the database when using Access for Database and front end forms
            Dim MSAdbPathAndName As String 
            MSAdbPathAndName = "C:\Reports\Database\msaccessdatabase.mdb"
            'Probably could use a MapPath function here ...
            '*****************************************************************
 
            Dim DBN As String ' "DATABASENAME"
            Dim UID As String '"yourusername"
            Dim PWD As String '"yourPASSWORD"
            Dim DBS As String '"SERVERNAME"
 
            'Here is where you want to set your parameters for connecting to the desired database
            DBS = "thisismywebservername"
            UID = "thisisme"
            PWD = "thisismypassword"
            DBN = "thisismydbname"
 
            'The connection strings are different for each type of database and network setup
            ORACLE_ConnString = "Provider=msdaora;Data Source=" & DBN & _
                                ";User Id=" & UID & ";Password=" & PWD & ";"
            SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBS & _
                                ";UID=" & UID & _
                                ";PWD=" & PWD & _
                                ";Initial Catalog=" & DBN & ";"
            MSACCESS_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
                                    MSAdbPathAndName & ";"
 
            'Here we declare and set the ADODB.Connection
            opendb = New ADODB.Connection
 
            'And finally you must decide which type of connection you are going to use... only one :)
 
            '    *************** preferred connection string ***********
            '    UNCOMMENT the ONE you need: if you want to connect to MS SQL then SQL_ConnString is the one you want
            '    ******************************************************* 
 
            '    opendb.Open ORACLE_ConnString
            opendb.Open(SQL_ConnString)
            '    opendb.Open MSACCESS_ConnString
 
        End Function
        Public Function stringReturningFunctionCall() As String
            ' The connection object is required for accessing the database and must be declared
            Dim objConn As ADODB.Connection
            ' The  recordset object is required for storing the queried dataset and must be declared
            Dim recSet As ADODB.Recordset
            'This functioncal return a string, we declare and set it to be empty
            Dim stringToReturn As String
            stringToReturn = ""
            'setting the connection object from a function that returns an ADODB.Connection 
            objConn = opendb
            'setting the Recordset object using the Connection object to excute the SQL statement
            recSet = objConn.Execute("SELECT [field1],[field2],[field3] FROM [sometable] order by [somefield] ")
            Do While Not rs.EOF ' run through the recordset until at the end
                'add your own code here
                stringToReturn = someStringAppendingFunction(stringToReturn, recSet(0) & _
                                                                ";" & recSet("field2") & _
                                                                ";" & recSet("field3"), ";")
                recSet.MoveNext()
            Loop
            'clean up time
            objConn.Close()
            objConn = Nothing
            recSet = Nothing
            'return the result
            stringReturningFunctionCall = stringToReturn
        End Function

[pod]

Here is a simplified version of using the "dbopen" function

Public Function opendb(ConnString As String) As ADODB.Connection 
    Set opendb = New ADODB.Connection 
    opendb.Open ConnString   
End Function

[Lysander]

Hi Pod,
If this was an answer to a specific question, it would be great. As an article, it would be nice to have more comments in the code and some more explanatiaroy text as to what this does and why it can be useful to people.

[pod]

Lysander, I agree with you.
I will work on a better presentation with a bit more explanatory text to go with it. Thank you.


P:oD

p.s.
Is it just me? I noticed that if I look at this page with Internet Explorer 8, the original portion of the code does not display unless I click "Expand"... with Firefox it displays correctly.

[NeoPa]

It's not just you Pod ;-)

[pod]

It took a while but there it is with slightly better comments...
If one of the moderators want to put this at the top, you have my blessing :)


P:oD

Public Function opendb() As ADODB.Connection
        ' This function returns an ADODB.Connection object 
        ' which is required for accessing different types of databases
        '
        ' Her we declare three strings to access database other than MS Access files on your network
        ' as it was in my case
        Dim ORACLE_ConnString As String
        Dim SQL_ConnString As String
        Dim MSACCESS_ConnString As String
        '*****************************************************************
        ' This variable is used only when accessing a MS Access database
        ' NOTE: I always keep the form separate from the database when using Access for Database and front end forms
        Dim MSAdbPathAndName As String 
        MSAdbPathAndName = "C:\Reports\Database\msaccessdatabase.mdb"
        'Probably could use a MapPath function here ...
        '*****************************************************************
 
        Dim DBN As String ' "DATABASENAME"
        Dim UID As String '"yourusername"
        Dim PWD As String '"yourPASSWORD"
        Dim DBS As String '"SERVERNAME"
 
        'Here is where you want to set your parameters for connecting to the desired database
        DBS = "thisismywebservername"
        UID = "thisisme"
        PWD = "thisismypassword"
        DBN = "thisismydbname"
 
        'The connection strings are different for each type of database and network setup
        ORACLE_ConnString = "Provider=msdaora;Data Source=" & DBN & _
                            ";User Id=" & UID & ";Password=" & PWD & ";"
        SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBS & _
                            ";UID=" & UID & _
                            ";PWD=" & PWD & _
                            ";Initial Catalog=" & DBN & ";"
        MSACCESS_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
                                MSAdbPathAndName & ";"
 
        'Here we declare and set the ADODB.Connection
        opendb = New ADODB.Connection
 
        'And finally you must decide which type of connection you are going to use... only one :)
 
        '    *************** preferred connection string ***********
        '    UNCOMMENT the ONE you need: if you want to connect to MS SQL then SQL_ConnString is the one you want
        '    ******************************************************* 
 
        '    opendb.Open ORACLE_ConnString
        opendb.Open(SQL_ConnString)
        '    opendb.Open MSACCESS_ConnString
 
    End Function
    Public Function stringReturningFunctionCall() As String
        ' The connection object is required for accessing the database and must be declared
        Dim objConn As ADODB.Connection
        ' The  recordset object is required for storing the queried dataset and must be declared
        Dim recSet As ADODB.Recordset
        'This functioncal return a string, we declare and set it to be empty
        Dim stringToReturn As String
        stringToReturn = ""
        'setting the connection object from a function that returns an ADODB.Connection 
        objConn = opendb
        'setting the Recordset object using the Connection object to excute the SQL statement
        recSet = objConn.Execute("SELECT [field1],[field2],[field3] FROM [sometable] order by [somefield] ")
        Do While Not rs.EOF ' run through the recordset until at the end
            'add your own code here
            stringToReturn = someStringAppendingFunction(stringToReturn, recSet(0) & _
                                                            ";" & recSet("field2") & _
                                                            ";" & recSet("field3"), ";")
            recSet.MoveNext()
        Loop
        'clean up time
        objConn.Close()
        objConn = Nothing
        recSet = Nothing
        'return the result
        stringReturningFunctionCall = stringToReturn
    End Function
 

[NeoPa]

What exactly do you want me to do here? Replace the original code in the first post with this new version?

[pod]

Bonjour NeoPa

Yes, I think it would be nicer to have better comments right at the top.

Thanks


P:oD

[zmbd]

I had started to ask that same question. (^_^)

Pod, with your kind permission, if NeoPa hasn't done this by this afternoon, I'll copy the new code to the OP replacing the original code and clean up the thread.

[pod]

Yes you may "do that voodoo that you do so well"

Can someone tell me where I stole this quote ?

P:oD

[zmbd]

"do that voodoo that you do so well"
Cole Porter, Ella Fitzgerald, Blazing Saddles...

Please double check the revised code, Simple copy and paste but goofyness is known to happen.
Once you're happy, I'll clean the the thread up.

[pod]

Funny, I must have watched Blazzing Saddles over 50 times when I was younger but I did not remember it was in it ... I've heard, Ella's version but not Cole Porter. MASH's Colonel Potter also says that line in the episode where Winchester gets his back pain cured by acupuncture performed by three visiting buddhist doctors refered as Larry, Curly and Moe ... a classic :)

[pod]

Zed, it looks fine, the logic is what counts anyway...

Wrap it up

Thank you

[zmbd]

I forgot about the M*A*S*H episode.
That was a show. They managed to jerk us from ROTFL to out-right bawling... but that was when writers could write.

Well worth a visit thru youtube to find the Cole Porter version - just because it's a classic.

Anyway, I'll clean up the thread here in an little bit (^_^)

[NeoPa]

Sorry PoD. I was away.

@Z.
Don't forget to clean up if you're all ready. This post can go too of course.

[pod]

Knock, knock Neo ... did you follow the white rabbit? :)

[NeoPa]

:-D

I seem to recall having him to a tea-party once. Always in a rush!

[zmbd]

I knew I should have taken the blue pill