VBA excel password problem

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

Did you add a reference to MS ActiveX Data Objects 2.x Object library?
Can you verify if the DSN is correctly connecting to the db?

i have referenced that particular library, im not sure about the DSN though

Have you Dim'd and Set rs?

yes i have, still not working! very strange

I just noticed that your event in Excel is Form_Load. There is no event like that in Excel. If its a UserForm then it would
be UserForm_Initialize(). If its when Excel opens then its Workbook_Open()

yes that should be it actually, had a feeling there was something different with vba in excel, thats great, thanks very much!

excellent, works perfect, thanks robdog