ADODC connection to use INSERT INTO Access DB 2

[WilliamHBonney]

I can get the connection to work but get an error 'Operation is not allowed when object is closed'
I have a private sub to which I pass the SQL statement and this works fine for all of my SELECT statements. This also works for the INSERT INTO because it is adding the information to the database but then I get the error.
the sub I use is below:

Private Sub connect(SQL)

AdcAddRecord.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Chris5.mdb;Persist Security Info=False"


'adds the sql queries in the adodc1
AdcAddRecord.RecordSource = SQL

'sets the property of datagrid to be the recorded result
Set DgrRecWrite.DataSource = AdcAddRecord
AdcAddRecord.Refresh

'if no record found then msgbox
If AdcAddRecord.Recordset.EOF Then
MsgBox "There was no matching record"
End If

End Sub

so the SQL is passed into this sub and it writes the record to the database but then the error. I think it is something to do with the AdcAddRecord.Refresh but not sure what to do instead cause it don't work at all without this in.
Any help appreciated. thanks

 

[vbkris]

it is better to seperate the recordset from the connection.

create a connection(con) object.

con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Chris5.mdb;Persist Security Info=False"

AdcAddRecord.open sql,con

Known is handfull, Unknown is worldfull