database error

[muyBN]

This is my macro code...:

Sub ToDB()
Dim csConn, rsRecSet As Recordset, strSQL As String, strUser As String

strSQL = "INSERT INTO jbDBTable (CO, CREATED) VALUES ('xyz', date())"
Set csConn = CreateObject("adodb.connection")
csConn.ConnectionString = "data source=C:\Data\0\jbDB.mdb;
Provider=Microsoft.Jet.OLEDB.4.0;"
Set rsRecSet = CreateObject("adodb.recordset")
rsRecSet.Open strSQL, csConn
csConn.Close
End Sub

....and this is my error with bugs:

Run-time error: '3709':
The connection cannot be used to perform this operation. It is either closed
or invalid in this context.

Any answers?

 

[Harold Druss]

This is my macro code...:

Sub ToDB()
Dim csConn, rsRecSet As Recordset, strSQL As String, strUser As String

strSQL = "INSERT INTO jbDBTable (CO, CREATED) VALUES ('xyz',
date())"
Set csConn = CreateObject("adodb.connection")
csConn.ConnectionString = "data source=C:\Data\0\jbDB.mdb;
Provider=Microsoft.Jet.OLEDB.4.0;"
Set rsRecSet = CreateObject("adodb.recordset")
rsRecSet.Open strSQL, csConn
csConn.Close
End Sub

...and this is my error with bugs:

Run-time error: '3709':
The connection cannot be used to perform this operation. It is either
closed
or invalid in this context.

Any answers?

Hi Bryan
Try this, works for me.
============================================================================
Sub ToDB()
Dim csConn As Object, rsRecSet As Object, strSQL As String, strUser As
String

Set csConn = CreateObject("adodb.connection")

Set rsRecSet = CreateObject("adodb.recordset")

csConn.ConnectionString = "data
source=C:\Data\0\jbDB.mdb;Provider=Microsoft.Jet.OLEDB.4.0;"

csConn.Open

strSQL = "INSERT INTO jbDBTable (CO, CREATED) VALUES ('xyz', date())"

rsRecSet.Open strSQL, csConn

Set rsRecSet = Nothing

csConn.Close
Set csConn = Nothing

End Sub
=============================================================================

Good luck
Harold

 

[Perry]

Replace

Set rsRecSet = CreateObject("adodb.recordset")
rsRecSet.Open strSQL, csConn
csConn.Close

by
csConn.Execute strSQL
csConn.Close

Krgrds,
Perry

 

[Perry]

csConn.Open '< you (and me) forgot to open the connection as well
csConn.Execute strSQL
csConn.Close

 

[muyBN]

Thanks, Perry. So, after the Dim statement, the procedure should look like
this:

Set csConn = CreateObject("adodb.connection")
csConn.ConnectionString = "data
source=C:\Data\[FileName].mdb;Provider=Microsoft.Jet.OLEDB.4.0;"
strSQL = "INSERT INTO [TableName] (CO, CREATED) VALUES ('xyz', date())"
csConn.Open
csConn.Execute strSQL
csConn.Close

I had wanted to do it with a RecordSet mainly for the practice, but as long
this works I guess I'll go with it.