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, fldScreenShotDescription, 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

[jaxjagfan]

Just a thought....

It would be far better to link to the screenshots than embed them. Nowhere near as much space taken in database. I have an application here (I work for an insurance company) that links to thousands of MS Word documents (regulations, laws, rules, forms, etc.). When we get a new doument it is put on our file server. A user then adds the a link to the document to our database. We also have several fields of information that describe the document (type, state, descrip, valid dates, etc) that allows the user to search for a document.

I also have a function which allows each user to save/remove a document record to/from their "Favorites" list. Some of our people work constantly with only a select few forms.

When you export records with embedded OLE objects, I think you lose the objects - not sure on this one.

[patjones]

Just a thought....

It would be far better to link to the screenshots than embed them. Nowhere near as much space taken in database. I have an application here (I work for an insurance company) that links to thousands of MS Word documents (regulations, laws, rules, forms, etc.). When we get a new doument it is put on our file server. A user then adds the a link to the document to our database. We also have several fields of information that describe the document (type, state, descrip, valid dates, etc) that allows the user to search for a document.

I also have a function which allows each user to save/remove a document record to/from their "Favorites" list. Some of our people work constantly with only a select few forms.

When you export records with embedded OLE objects, I think you lose the objects - not sure on this one.

Initially, using links was what I wanted to do. The problem is that screenshots will only be needed when the application (a web-based payroll/timekeeping application) encounters some sort of run-time error and raises possibly cryptic error message boxes.

The application administrators want to have a record of exactly what the screen looks like at the point where the error is raised. By taking a screen shot of the application's window (an Internet Explorer window) at that point and then pasting it into an OLE box back in the Access window, I can in theory keep each screen shot in the database. So it's not really a question of simply linking to a document.

It's like you say though, each screen shot takes up so much memory (5 MB or more for the ones I've tried so far), so I want to at least purge them to some separate file periodically, so as not to weigh down the database itself...

[ADezii]

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, fldScreenShotDescription, 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

Take a look at these 2 Tips, they should be very helpful to you:
Persisting a Recordset

BLOBs

[patjones]

Take a look at these 2 Tips, they should be very helpful to you:
Persisting a Recordset

BLOBs

Well, I've spent a good part of the day digesting your links. They ARE extremely useful. For the saving part of the project, I followed your code in the first link, and it works great. I get the .adtg file just fine.

For the retrieval of the information, I took from both your code, and code in the BLOB project to get this:

 Private Sub Form_Open(Cancel As Integer) 
 
    Dim strFullPath As String, msTemporaryFolder As String, strSourceFileADTG As String
    Dim rstGet As ADODB.Recordset
    Dim mstream As ADODB.Stream
 
    msTemporaryFolder = getTempFolder & "\"
 
    strSourceFileADTG = CurrentProject.Path & "\testScreenShot.adtg"
 
    Set rstGet = New ADODB.Recordset
    rstGet.Open strSourceFileADTG, , adOpenKeyset, adLockOptimistic
    rstGet.MoveFirst
    rstGet.Find "fldTicketNum = 123465"
 
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rstGet!fldScreenShot
 
    strFullPath = msTemporaryFolder & "temp123465.bmp"
 
    mstream.SaveToFile strFullPath, adSaveCreateOverWrite
 
    Me.imgScreenShot.Picture = strFullPath
    Me.txtTicketNum.Value = rstGet!fldTicketNum
 
    rstGet.Close
    Set rstGet = Nothing
 
End Sub
 

This code actually runs fine up to Me.imgScreenShot.Picture = strFullPath. At that point, Access raises Error 2114: Microsoft Access doesn't support the format of the file C:\DOCUME~1\Employee\LOCALS~1\temp\temp123465.bmp, or the file is too large. Try converting the file to BMP format.

When I look in the Temp folder, I find that it does indeed create a bitmap file, with a size corresponding to what I would expect for a screen shot. But when I try to open that file by double clicking on it, I just get "No preview available". It's almost as if it's not writing the image to the file, but yet the size of the file indicates that something is in there...

[ADezii]

Well, I've spent a good part of the day digesting your links. They ARE extremely useful. For the saving part of the project, I followed your code in the first link, and it works great. I get the .adtg file just fine.

For the retrieval of the information, I took from both your code, and code in the BLOB project to get this:

 Private Sub Form_Open(Cancel As Integer) 
 
    Dim strFullPath As String, msTemporaryFolder As String, strSourceFileADTG As String
    Dim rstGet As ADODB.Recordset
    Dim mstream As ADODB.Stream
 
    msTemporaryFolder = getTempFolder & "\"
 
    strSourceFileADTG = CurrentProject.Path & "\testScreenShot.adtg"
 
    Set rstGet = New ADODB.Recordset
    rstGet.Open strSourceFileADTG, , adOpenKeyset, adLockOptimistic
    rstGet.MoveFirst
    rstGet.Find "fldTicketNum = 123465"
 
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rstGet!fldScreenShot
 
    strFullPath = msTemporaryFolder & "temp123465.bmp"
 
    mstream.SaveToFile strFullPath, adSaveCreateOverWrite
 
    Me.imgScreenShot.Picture = strFullPath
    Me.txtTicketNum.Value = rstGet!fldTicketNum
 
    rstGet.Close
    Set rstGet = Nothing
 
End Sub
 

This code actually runs fine up to Me.imgScreenShot.Picture = strFullPath. At that point, Access raises Error 2114: Microsoft Access doesn't support the format of the file C:\DOCUME~1\Employee\LOCALS~1\temp\temp123465.bmp, or the file is too large. Try converting the file to BMP format.

When I look in the Temp folder, I find that it does indeed create a bitmap file, with a size corresponding to what I would expect for a screen shot. But when I try to open that file by double clicking on it, I just get "No preview available". It's almost as if it's not writing the image to the file, but yet the size of the file indicates that something is in there...

1. After Opening the Recordset, try re-connecting it to the Database as in:
   

   rstGet.ActiveConnection = CurrentProject.Connection

   This code line would be inserted between lines 12 and 13 in your code display.
2. Do you get this Error consistently on all Graphic File Formats, or just this Format specifically? Do you get this Error on only this File specifically?
3. Try persisting the Recordset in XML Format instead of ADTG. Although ADTG is smaller and probably more efficient, it is nonetheless proprietary to Microsoft and XML is a wider Standard.
4. Get back to me on this.

[patjones]

1. After Opening the Recordset, try re-connecting it to the Database as in:
   

   rstGet.ActiveConnection = CurrentProject.Connection

   This code line would be inserted between lines 12 and 13 in your code display.
2. Do you get this Error consistently on all Graphic File Formats, or just this Format specifically? Do you get this Error on only this File specifically?
3. Try persisting the Recordset in XML Format instead of ADTG. Although ADTG is smaller and probably more efficient, it is nonetheless proprietary to Microsoft and XML is a wider Standard.
4. Get back to me on this.

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...

[werks]

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

hi there zepphead80,

The problem is the connection of the system to the database, check my revise code

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

Hope this help ^^


Better Than Yesterday (-.-)

[patjones]

hi there zepphead80,

The problem is the connection of the system to the database, check my revise code

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

Hope this help ^^


Better Than Yesterday (-.-)

That doesn't work: I get runtime Error 3079.

As I mentioned previously, the save operation seems to function properly because when I do the retrieval I get back all the fields in their proper form, except that I can't connect the image in fldScreenShot to the image object in my form. So, I think the connection used when I initially save the .adtg file is OK, unless there is something about the connection that is preventing the image from getting saved properly.

[ADezii]

Here we go, zepphead80! I made slight modifications to your code and adapted it to my BLOBs Demonstration Database, and guess what, it works perfectly. I Captured an existing Screen (Screen Shot), saved it as a BLOB to a Table, created a Recordset based on the Table, then Persisted it (Saved it to Disk). The Screen Shot was 2,143,000 Bytes in a 24-bit BMP Format. I then created code to Retrieve the Persisted Recordset, locate the exact Record corresponding to the Screen Shot (rst.Find "[ID]=17"), integrate BLOB code to Write the Binary Stream to a File, and then set the Picture Property of an Image Control to this File. I'll post both the Save and Retrieve code, hope this solves your problem.

1. Code to Persist the Recordset:
   

   Dim rst As ADODB.Recordset, strFile As String
    
   Set rst = New ADODB.Recordset
    
   'Open the recordset from the database
   rst.Open "tblInventoryPics", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
   'Construct a file name to use
   strFile = "C:\Test\Inventory.adtg"
    
   'Destroy any existing file because the Save Method will fail if the file already exists
   On Error Resume Next
    
   Kill strFile
    
   Err.Clear   'Clear the Error Object
    
   'Now save the recordset to disk
   rst.Save strFile, adPersistADTG
    
   'Close the recordset in memory
   rst.Close
   Set rst = Nothing

2. Code to retrieve Recordset and display Image:
   

   Dim rst As ADODB.Recordset, strFile As String, mstream As ADODB.Stream
    
   Set rst = New ADODB.Recordset
   Set mstream = New ADODB.Stream
    
   'Construct a file name to use
   strFile = "C:\Test\Inventory.adtg"
    
   ' Make sure the file exists
   If Len(Dir(strFile)) > 0 Then
     'Open the recordset from the file
     rst.Open strFile, , adOpenKeyset, adLockOptimistic
     'Reconnect the Recordset to the database
     rst.ActiveConnection = CurrentProject.Connection
       rst.Find "[ID]=17"  'This will be the Screen Capture (2,143 Kb)
   End If
    
   mstream.Type = adTypeBinary
   mstream.Open
   mstream.Write rst![oPicture]
   mstream.SaveToFile "C:\Test\Test.bmp", adSaveCreateOverWrite
    
   Me![imgTest].Picture = "C:\Test\Test.bmp"
    
   rst.Close
   Set rst = Nothing

[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=zepphead80]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=zepphead80]

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=zepphead80]

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