patjones 931
Recognized Expert Contributor
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
16 6220
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 931
Recognized Expert Contributor
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 8,834
Recognized Expert Expert
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
Take a look at these 2 Tips, they should be very helpful to you: Persisting a Recordset BLOBs patjones 931
Recognized Expert Contributor 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.imgScreenSho t.Picture = strFullPath. At that point, Access raises Error 2114: Microsoft Access doesn't support the format of the file C:\DOCUME~1\Emp loyee\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 8,834
Recognized Expert Expert
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.imgScreenSho t.Picture = strFullPath. At that point, Access raises Error 2114: Microsoft Access doesn't support the format of the file C:\DOCUME~1\Emp loyee\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.
patjones 931
Recognized Expert Contributor - 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 (-.-)
patjones 931
Recognized Expert Contributor
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 8,834
Recognized Expert Expert
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: James |
last post by:
Quick question about closing recordsets and connection objects. We're in
the process of rewriting a TON of bad code. None of it is even remotely
tabbed properly, it's impossible to read half the time and it never closes
connection objects or recordsets. Unfortunately, I've been assigned the
task of closing them. I assume I'm just doing:
recordset.close
Set recordset = Nothing
conn.close
|
by: Susan Bricker |
last post by:
Hi. I am a relative "newbie" who has been asked to create a database
for a Dog Competition Organization. I have created databases (for my
use) just for fun to organize a practice Dog Obedience Competition
another to organize those fun projects, I felt comfortable enough to
tack a database for work to manage software releases (I'm a programmer
for a major US Bank) for my department. I did this on my own time and
"sold" it to my manager...
|
by: jason |
last post by:
hello,
i have a C# class library that i recently had to add ADODB
functionality to, in order to support consumer applications that can
only work with recordsets.
i added the ADODB library reference, and implemented recordsets without
a problem, however now my class library dll won't register. when i
attempt to register it with COM+, the MMC reports "could not find the
file specified". since this occured right AFTER i added the ADODB
|
by: |
last post by:
Hello ppl,
I have snippet that works fine udner ADODB with VB6, but something wrong
with it in Vb.NET. Anyone can help?
Recordset1 (ADODB.Recordset) Error: Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.
Error Source:
SELECT * FROM Accounts
|
by: Tull Clancey |
last post by:
Hi, hope someone can help.
I'm writing a decent size app as a first project in Net, have been writing
in VB for years.
I've stumbled on a small problem, I can't open two connections to a database
at the same time, even if the connections are in different classes. Am I
doing something compeltely idiotic or is this not as easy at it used to be?
Cheers,
| |
by: Yuk Tang |
last post by:
I'm trying to grab the fieldnames and values from a recordset, but
I'm getting errors. I have an idea what the error might come from,
but I'm not sure how to correct it.
I'm connecting to an Access db which has the table 'Users', and I
want to grab the fieldnames and values without necessarily knowing
their numbers and formats. The msgboxes are there to illustrate the
error.
To run this, you need an Access db called Users.mdb in the...
|
by: Wonder |
last post by:
We have a VB.NET project with a reference to ADO 2.5
When I open it on my machine, I get the following build error: "The
referenced component 'ADODB' has an updated custom wrapper available."
When I double click on it, I get the following dialog appear:
If I click 'No', for every object declared from the ADODB library, I get a
build error as follows: "Reference required to assembly 'ADODB' containing
the type 'ADODB.Connection'. Add...
|
by: Peter Newman |
last post by:
Im still trying to get my head around this, but its the only way i have seen
to be able to create a 'recordset' and bind text boxex to it and navigate
throw each record by the click of a button ... ok it may not be the prettist
of code but i have that bit working . i have however stumbled across another
problem ...
I make a connection to a SQL2005 database when my app loads using the
following
CON_BOSSCONNECTION.ConnectionString =
|
by: boyleyc |
last post by:
Hi all
I have written a database in access and used ADODB recordsets all the
way through. The only recordsets that are not ADODB are the listbox
navigation code automatically generated by access 2003 as follows :
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |