By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

ADODB recordsets and connections

patjones
Expert 100+
P: 931
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
Jan 8 '08 #1
Share this Question
Share on Google+
16 Replies


jaxjagfan
Expert 100+
P: 254
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
Expert 100+
P: 931
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
Expert 5K+
P: 8,607
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
Expert 100+
P: 931
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:

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...
Jan 9 '08 #5

ADezii
Expert 5K+
P: 8,607
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
Expert 100+
P: 931
  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
100+
P: 220

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

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 (-.-)
Jan 10 '08 #8

patjones
Expert 100+
P: 931
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
Expert 5K+
P: 8,607
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
Expert 5K+
P: 8,607
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
Expert 100+
P: 931
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
Expert 5K+
P: 8,607
[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
Expert 100+
P: 931
[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
Expert 5K+
P: 8,607
[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
Expert 100+
P: 931
[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
Expert 100+
P: 931
[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

Post your reply

Sign in to post your reply or Sign up for a free account.