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:

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

Jan 8 '08 #1

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.

Jan 8 '08 #2

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

Jan 9 '08 #3

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:

Expand|Select|Wrap|Line Numbers

1.  Private Sub cmdSave_Click() 
2.  
3.     Dim rst As ADODB.Recordset
4.     Dim strSQL As String
5.  
6.     Set rst = New ADODB.Recordset
7.  
8.     strSQL = "SELECT fldScreenShot, fldScreenShotDescription, fldTicketNum FROM tblScreenShot"
9.  
10.     rst.Open strSQL
11.  
12.     rst.Save "C:\Documents and Settings\Employee\Desktop\testScreenShot.adtg", adPersistADTG
13.  
14.     rst.Close
15.  
16.     Set rst = Nothing
17.  
18. End Sub
19.  

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

Jan 9 '08 #4

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:

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

Jan 9 '08 #5

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:

Expand|Select|Wrap|Line Numbers

1.  Private Sub Form_Open(Cancel As Integer) 
2.  
3.     Dim strFullPath As String, msTemporaryFolder As String, strSourceFileADTG As String
4.     Dim rstGet As ADODB.Recordset
5.     Dim mstream As ADODB.Stream
6.  
7.     msTemporaryFolder = getTempFolder & "\"
8.  
9.     strSourceFileADTG = CurrentProject.Path & "\testScreenShot.adtg"
10.  
11.     Set rstGet = New ADODB.Recordset
12.     rstGet.Open strSourceFileADTG, , adOpenKeyset, adLockOptimistic
13.     rstGet.MoveFirst
14.     rstGet.Find "fldTicketNum = 123465"
15.  
16.     Set mstream = New ADODB.Stream
17.     mstream.Type = adTypeBinary
18.     mstream.Open
19.     mstream.Write rstGet!fldScreenShot
20.  
21.     strFullPath = msTemporaryFolder & "temp123465.bmp"
22.  
23.     mstream.SaveToFile strFullPath, adSaveCreateOverWrite
24.  
25.     Me.imgScreenShot.Picture = strFullPath
26.     Me.txtTicketNum.Value = rstGet!fldTicketNum
27.  
28.     rstGet.Close
29.     Set rstGet = Nothing
30.  
31. End Sub
32.  

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:
   
   Expand|Select|Wrap|Line Numbers

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

Jan 10 '08 #6

patjones

1. After Opening the Recordset, try re-connecting it to the Database as in:
   
   Expand|Select|Wrap|Line Numbers

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

Jan 10 '08 #7

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

Hope this help ^^


Better Than Yesterday (-.-)

Jan 10 '08 #8

patjones

hi there zepphead80,

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

Expand|Select|Wrap|Line Numbers

1.  Private Sub cmdSave_Click() 
2.  
3.     Dim rst As ADODB.Recordset
4.     Dim con As ADODB.Connection
5.     Dim strSQL As String
6.  
7.     Set rst = New ADODB.Recordset
8.     Set con = New ADODB.Connection
9.  
10.     strSQL = "SELECT fldScreenShot, fldScreenShotDescription, fldTicketNum FROM tblScreenShot"
11.  
12.     rst.Open strSQL,con,3,3
13.  
14.     rst.Save "C:\Documents and Settings\Employee\Desktop\testScreenShot.adtg", adPersistADTG
15.  
16.     rst.Close
17.     con.Close
18.  
19.     Set rst = Nothing
20.     Set con = Nothing
21.  
22. End Sub
23.  

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.

Jan 10 '08 #9

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:
   
   Expand|Select|Wrap|Line Numbers

   1. Dim rst As ADODB.Recordset, strFile As String
   2.  
   3. Set rst = New ADODB.Recordset
   4.  
   5. 'Open the recordset from the database
   6. rst.Open "tblInventoryPics", CurrentProject.Connection, adOpenStatic, adLockOptimistic
   7.  
   8. 'Construct a file name to use
   9. strFile = "C:\Test\Inventory.adtg"
   10.  
   11. 'Destroy any existing file because the Save Method will fail if the file already exists
   12. On Error Resume Next
   13.  
   14. Kill strFile
   15.  
   16. Err.Clear   'Clear the Error Object
   17.  
   18. 'Now save the recordset to disk
   19. rst.Save strFile, adPersistADTG
   20.  
   21. 'Close the recordset in memory
   22. rst.Close
   23. Set rst = Nothing
2. Code to retrieve Recordset and display Image:
   
   Expand|Select|Wrap|Line Numbers

   1. Dim rst As ADODB.Recordset, strFile As String, mstream As ADODB.Stream
   2.  
   3. Set rst = New ADODB.Recordset
   4. Set mstream = New ADODB.Stream
   5.  
   6. 'Construct a file name to use
   7. strFile = "C:\Test\Inventory.adtg"
   8.  
   9. ' Make sure the file exists
   10. If Len(Dir(strFile)) > 0 Then
   11.   'Open the recordset from the file
   12.   rst.Open strFile, , adOpenKeyset, adLockOptimistic
   13.   'Reconnect the Recordset to the database
   14.   rst.ActiveConnection = CurrentProject.Connection
   15.     rst.Find "[ID]=17"  'This will be the Screen Capture (2,143 Kb)
   16. End If
   17.  
   18. mstream.Type = adTypeBinary
   19. mstream.Open
   20. mstream.Write rst![oPicture]
   21. mstream.SaveToFile "C:\Test\Test.bmp", adSaveCreateOverWrite
   22.  
   23. Me![imgTest].Picture = "C:\Test\Test.bmp"
   24.  
   25. rst.Close
   26. Set rst = Nothing

Jan 11 '08 #10

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.

Jan 11 '08 #11

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?

Jan 11 '08 #12

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?

Jan 11 '08 #13

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

Jan 11 '08 #14

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.

Jan 11 '08 #15

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 : - )

Jan 14 '08 #16

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

Jan 25 '08 #17