VBA database connectivity and type mismatch error

[Lucky Girl]

I'm trying to connect to an Access Database but i want to modularize the
database connectivity code for reuse. My database class module has the
following code.

Option Explicit

Function dbconnect()

Dim conn As ADODB.Connection

'creating the database connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=D:\My Documents\PMB
files\CorporateFinanceSystem\main.mdb;"

dbconnect = conn

End Function

'-----------------------------------------------------

Function addUser(myconn As ADODB.Connection)

Set rs = New ADODB.Recordset

rs.Open "Employee_Details", myconn, adOpenKeyset, adLockOptimistic

With rs
.AddNew
.Fields("Name") = "Joseph"
.Fields("Address") = "New York"
.Update
End With

End Function

and the code in which i actually call the above function goes like this.

Option Explicit

Sub btnAdd_Click()

Dim mydb As Database
Dim myconn As ADODB.Connection

Set mydb = New Database
Set myconn = New ADODB.Connection

myconn = mydb.dbconnect()

mydb.addUser (myconn)



End Sub

I get the error for the line ' mydb.addUser (myconn) ' and it says Compile
Error: type mismatch. I'm a complete newcomer to VBA and i cant figure out
what i'm doing wrong here. can someone please be kind enough to help?

 

[Pesach Shelnitz]

Hi,

The problem that I can see in your code is the absence of a line that sets
the return value in the code of the addUser function. The code of the
dbconnect function sets the return value in the line "dbconnect = conn", but
addUser does not have a similar line and is therefore a subprocedure rather
than a function. Also, although it is not required in VBA, you can add "As
ReturnDataType" to your function declarations.