Connection Ojbect

Can anyone help me with the following code? I'm getting, "Invalid use of New keyword", when I try to run it. Thanks so much for your help!

Private Sub cmdSubmit_Click()
Dim cnBusinessRule As Connection
Dim sSQL As String
Set cnBusinessRule = New Connection

'Establish a connection
With cnBusinessRule
.Provider = "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "User ID = sa; password=;" & _
"Data Source=C:\BusinessRules\Database\BusinessRule.mdb;" & _
"Initial Catalog=BusinessRule"
.Open
End With
sSQL = "INSERT INTO GOV_AUTHORITY(Name, Category) " & _
"VALUES (txtName, cmbCategory)"

cnBusinessRule.Execute sSQL
cnBusinessRule.Close
Set cnBusinessRule = Nothing
End Sub

Try:
private Sub cmdSubmit_Click()
Dim cnBusinessRule as new Adodb.Connection
Dim sSQL as string

set cnBusinessRule = adodb.Connection

'Establish a connection
With cnBusinessRule
.Provider = "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "User ID = sa; password=;" & _
"Data Source=C:\BusinessRules\Database\BusinessRule.mdb;" & _
"Initial Catalog=BusinessRule"
.Open
End With
sSQL = "INSERT INTO GOV_AUTHORITY(Name, Category) " & _
"VALUES (txtName, cmbCategory)"

cnBusinessRule.Execute sSQL
cnBusinessRule.Close
set cnBusinessRule = nothing
End Sub




Regards



Anne Wright
Wright Computing

Anne,

Thanks for your reply...I've tried with your code and am now getting, "Method or data member not found", message. In order to use the ADO connection, do I need to bind it to the form before I can use it?

Thanks again Anne.

Try:

private Sub cmdSubmit_Click()
Dim cnBusinessRule as new Adodb.Connection
Dim sSQL as string
'Establish a connection
With cnBusinessRule
.Provider "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "User ID = sa; password=;" & _ "Data Source=C:\BusinessRules\Database\BusinessRule.mdb;" & _ "Initial Catalog=BusinessRule"
.Open End With sSQL = "INSERT INTO GOV_AUTHORITY(Name, Category) " & _ "VALUES (txtName, cmbCategory)" cnBusinessRule.Execute sSQL cnBusinessRule.Close
set cnBusinessRule = nothing
End Sub

Hi.

I've tried with your code and I now get a runtime error on the .Open statement.

Any ideas?

Thanks again.
Morgan

There could be many reasons for this..
Since there is no error on your ADODB object model instantiation statments now this tells me that instantiation is fine.
Runtime errors could be due to the mdb file not being in the path specified..
Step thru your code and try to figure out the error

best of luck!!