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
16 5915
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.
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...
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 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...
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...
- 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. - 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?
- 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.
- Get back to me on this.
- 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. - 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?
- 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.
- 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...
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 (-.-)
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.
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. - 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
- 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
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.
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?
[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: - Microsoft Scripting Runtime
- 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?
[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:- Microsoft Scripting Runtime
- 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...
[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.
[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 : - )
[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
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
4 posts
views
Thread by James |
last post: by
|
17 posts
views
Thread by Susan Bricker |
last post: by
|
1 post
views
Thread by jason |
last post: by
|
3 posts
views
Thread by |
last post: by
|
8 posts
views
Thread by Tull Clancey |
last post: by
|
3 posts
views
Thread by Yuk Tang |
last post: by
|
6 posts
views
Thread by Wonder |
last post: by
|
7 posts
views
Thread by Peter Newman |
last post: by
|
7 posts
views
Thread by boyleyc |
last post: by
| | | | | | | | | | |