Creating a Connection String

I was wondering if I could create a connection string with a function, so that I can call the function once, instead of creating connections within each function. Can anyone let me know if this is possible?

VB Code:

Private Function connectToDB(database as String) as ADODB.Connection
     Dim connection as ADODB.connection     
     Set connection = New ADODB.connection
     connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & database 
     connection.Open
     connectToDB = connection
End Function

If you'd try it, you might find that it works.

-tg

Well I keep getting a user defined datatype error. Is there something I need to add in the references or how do I go about defining a datatype?

On what line to do you get the error (as tg intimated, on the surface, it looks just fine)?

It's complaining that ADODB.Connection is not a return type, and is a user defined type

Why don't you just use 1 public connection object?

Did you set a reference to ADO?

-tg

OK so I'm still working on creating a function that inputs a database name and outputs a connection to that database. Currently I am getting a "Run-time error '91': Object variable or With block variable not set" whenever I try to return the connection (i.e. connect = connection). Any help would be greatly appreciated. Thanks

VB Code:

Private Sub Form_Load()
    Dim conn As ADODB.connection
    conn = connect("Test")
    Dim sql As String
    sql = "SELECT * FROM [TEST]"
    conn.Execute sql
 
    Debug.Print sql
End Sub
 
Private Function connect(database As String) As ADODB.connection
    Dim connection As ADODB.connection
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source= C:\Documents and Settings\P6B0438\My Documents\Test\" & _
       database & ".mdb"
    connection.Open
 
    connect = connection
    
End Function

bump

As it is an object variable you need to use Set, eg:

VB Code:

Set connect = connection

You should also clear the memory used by the local variable, eg:

VB Code:

Set connection = Nothing

There are several things that you need to change.

First, your connection object declaration, as it is, is confined to your Function and is not available outside of your function (I'm guessing that is your object not set.) Move your these declares to the Form's declaration section so they they will be available throughout your form.

VB Code:

Option Explicit
 
Private conn As ADODB.Connection
Private adoRS As ADODB.Recordset
Private sSQL As String

Second, you Execute action queries such as INSERT, UPDATE, DELETE etc. SELECT statements create recordsets. They are not executed. The code for this would be

VB Code:

Set adoRS = NEW ADODB.Recordset
 
sSQL = "SELECT * FROM [TEST]"
adoRS.Open sSQL, conn

Thanks did not know that about when to use the execute command