Here is some vba code I use to connect to three types of databases.
The "somefunctionca ll" 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 ]
The "somefunctionca ll" 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 ]
Code:
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
Comment