ADODB recordsets and connections

[patjones]

Hi -

I am just starting my study of ADO, and I have a purpose in my current project which seems to lend itself to using an ADODB recordset. The set up is this.

I have a table tblScreenShot that has three fields: fldTicketNum, fldScreenShotDe scription, and fldScreenShot. Basically, what this table will do is store screen shots (fldScreenShot is typed "OLE Object"). Perhaps 1 in 20 records in my project will necessitate a screen shot...so this table is where I want to store them.

Since screen shots take up so much memory, periodically I want to purge them from the database and put them in a separate file. Then, whenever the user needs to pull a record containing a screen shot - which won't be too often, the database can open the file back up and pull it out.

So, my first "shot in the dark" attempt at trying to save the contents of the table to a file goes something like this:

 Private Sub cmdSave_Click() 
 
    Dim rst As ADODB.Recordset
    Dim strSQL As String
 
    Set rst = New ADODB.Recordset
 
    strSQL = "SELECT fldScreenShot, fldScreenShotDescription, fldTicketNum FROM tblScreenShot"
 
    rst.Open strSQL
 
    rst.Save "C:\Documents and Settings\Employee\Desktop\testScreenShot.adtg", adPersistADTG
 
    rst.Close
 
    Set rst = Nothing
 
End Sub
 

When I attempt to run this, it stops at rst.Open strSQL, saying "Error 3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context."

For me, being new to ADO, this begs the question of what exactly a connection is, how it should be implemented here, and why it's needed in the first place.

Hopefully this isn't too big a can of worms, but if it is maybe my post can be used to teach the community here about some ADODB basics. Thanks so much.

Pat

[ADezii]

Step 1 doesn't fix the problem, but I'm going to keep it in the code anyway. For step 2, I have tried .jpg and .gif; by this I mean that I tried changing the file extension on line 21 of my code to those extensions. "temp123465 " is the only file name that I have tried using. When I persist the recordset in XML format, the save operation still works fine, but the retrieval problem still exists.

I should point out that the retrieval itself seems to go fine, because when I do a Debug.Print on the two non-image fields in the retrieved record (a text field and a number field) I get the proper values in the Immediate Window. This is what leads me to believe, still, that there is an issue specifically with the way the image (fldScreenShot) is being written to the .adtg / .xml file...

See Post #10 for an Updated Reply.

[patjones]

See Post #10 for an Updated Reply.

I still get Error 2114...

The table that you save the image to, is that field typed as OLE Object? Because that's how I have my fldScreenShot typed...

Is it possible that I am having a problem with references? Perhaps there's a library that I don't have checked off which is preventing the code from running properly?

[ADezii]

[quote=zepphead8 0]I still get Error 2114...

The table that you save the image to, is that field typed as OLE Object? Because that's how I have my fldScreenShot typed...

Yes. Was the Screen Shot originally saved as a BLOB?

Is it possible that I am having a problem with references?

Most definately, Error Number 2114 would point to that possibility. At a minimum, you would need References to:

1. Microsoft Scripting Runtime
2. Microsoft ActiveX Data Objects

P.S. - If your problems persist, would it be possible to send the Database to me, with a sub-set of the data, as an E-Mail Attachment?

[patjones]

[quote=ADezii]

I still get Error 2114...


Yes. Was the Screen Shot originally saved as a BLOB?


Most definately, Error Number 2114 would point to that possibility. At a minimum, you would need References to:

1. Microsoft Scripting Runtime
2. Microsoft ActiveX Data Objects

P.S. - If your problems persist, would it be possible to send the Database to me, with a sub-set of the data, as an E-Mail Attachment?

Well, I took the screen shot and pasted it into the table...not sure that it's in BLOB format necessarily. How do I verify that?

I made sure that I have Microsoft Scripting Runtime and Microsoft ActiveX Date Objects 2.8 Library referenced.

I have no problem emailing this to you...

[ADezii]

[quote=zepphead8 0]

Well, I took the screen shot and pasted it into the table...not sure that it's in BLOB format necessarily. How do I verify that?

I made sure that I have Microsoft Scripting Runtime and Microsoft ActiveX Date Objects 2.8 Library referenced.

I have no problem emailing this to you...

Well, I took the screen shot and pasted it into the table...not sure that it's in BLOB format necessarily. How do I verify that?

You would have had to specifically Save it as a Binary Large Object with either ADO or DAO code, such as that provided for in the BLOBs Tip. Also, if you tried to activate the Object in Table View, you would receive a 'Communication Error with the Server or ActiveX Control'. It seems that this may be at the root of your problem.

[patjones]

[quote=ADezii]

You would have had to specifically Save it as a Binary Large Object with either ADO or DAO code, such as that provided for in the BLOBs Tip. Also, if you tried to activate the Object in Table View, you would receive a 'Communication Error with the Server or ActiveX Control'. It seems that this may be at the root of your problem.

Haven't tried these most recent suggestions yet, but will tomorrow! So much to do : - )

[patjones]

[quote=zepphead8 0]

Haven't tried these most recent suggestions yet, but will tomorrow! So much to do : - )

Hi:

I ended up modifying the project slightly in order to accomodate the easier method of having an Attachment field in the table, and linking to external .JPG files. The whole BLOB method seems very promising, and I'm sure I'll return to it eventually; I just don't need it right now.

Thanks so much for all your help, as usual...

Pat