database error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

muyBN - 03 Feb 2007 07:14 GMT

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?

Signature

Bryan

Reply to this Message

Harold Druss - 03 Feb 2007 13:07 GMT

> This is my macro code...:
>
[quoted text clipped - 19 lines]
>
> 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

Reply to this Message

Perry - 03 Feb 2007 13:41 GMT

Replace

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

by
csConn.Execute strSQL
csConn.Close

Krgrds,
Perry

> This is my macro code...:
>
[quoted text clipped - 19 lines]
>
> Any answers?

Reply to this Message

Perry - 03 Feb 2007 13:55 GMT

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

> Replace
>> Set rsRecSet = CreateObject("adodb.recordset")
[quoted text clipped - 32 lines]
>>
>> Any answers?

Reply to this Message

muyBN - 03 Feb 2007 20:29 GMT

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.

Signature

Bryan

> csConn.Open '< you (and me) forgot to open the connection as well :-)
> csConn.Execute strSQL
[quoted text clipped - 36 lines]
> >>
> >> Any answers?

Reply to this Message