I created a typical password program with VB using adodc's that
asked the user to input a username and password when logging in etc.
This works fine in VB but i have tryed to run this in VBA for Excel
and the connection doesnt seem to work. Here is my FormLoad:

Private Sub Form_Load()

Set con = New ADODB.Connection
Dim ssql As String
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YYYYYYYYY"
con.Open

end sub

Here is the code under my OK command button on the login form:

ssql = "select * from login where username='" & Text1.Text & "' and password='" & Text2.Text & "'"
rs.Open ssql, con, adOpenDynamic, adLockOptimistic


When i click OK after inputting my username and password I receive an
error message saying "the connection cannot be used to perform this operation.It is either closed or invalid in this context".
Everything seems to be in order except for "con"

Any suggestions or advice otherwise? It would be greatly appreciated