Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Atttachments on my form?

Posted on 2010-09-13
Medium Priority
Last Modified: 2012-06-27
I have VB6.

Is there a way to store a file on my SQL server using my VB6 form?  For example we have to scan articles and then attach them to the VB6 form somehow.  Then the form will have to store in the SQL database.

Any guidance would be appreciated.

Question by:al4629740
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
LVL 20

Expert Comment

ID: 33669826
What you want is certainly possible.  I would like to suggest a much easier way.

Why not store these articles in a safe place somewhere and then store the path to this place in the database.

This way you are not storing various files in a database which will enlarge the size of the database and you will not have to deal with different file types.

On the form people will have a link to the file and be able to click on the link allowing the Operating System to chooes the appropriate program to view the file.
LVL 11

Expert Comment

ID: 33671067
I'd like to add that although I agree with darbid73 that it is easier to just store the file on the network and retain a path in the db, there is a caveat to be aware of with this approach.  I know, because I use this method in one of our databases and I've had this problem:  If the folder where the files are stored is easily available to users, they *will* change paths, filenames, add & delete files, all at will without regard to how it affects the database, no matter how many times you tell them, and then your paths stored in the db will be invalid and can't easily be fixed.  If you use this method, store the files where the users don't have easy access.  They still need some access to the files because otherwise they wouldn't be able to open the files later.  So you'd be relying on obfuscation, or a complex process of mapping to a network share within your app using an alternate user acct.  In the latter case, when the user needs to open the file, you may want to copy to the local temp folder and then disconnect your temp network mapping, just so they don't modify the original except through your managed process.

On the other hand, regarding yor idea, as darbid73 suggested, you can do it, but you need some special coding to be able to store BLOB's in any SQL database.  BLOB's are Binary Large OBjects.  And you're likely looking for how to use GetChuck and AppendChunk methods of ADO.

Commonly, images will be stored this way.  In the case of files, you should look at the population of your files to get a sampling of file sizes and be aware of the impact this may have on your db, not just for storage, but also backup and maintenance, both very critical points.  (Make sure you follow best practices with any SQL database, because it's not fun or quick if you have to recover from any kind of failure.)

If you go the folder\file route instead of embedding in the db, try to generalize in case your target storage folder has to be moved to a new server/share name.  One way to do this would be to create an INI file that stores the root path (i.e. "\\server\shareName\PathToFiles\") and concatenate that onto the path name you actually store in th db.  That way if the stored files are restored to a different server/path, you can just change the INI file, not the stored path names.

That being said, here are some suggestions for storing files in a SQL db.

1. Know that it's not trivial.

2. Keep the images in a different database than your transactional data, kind of like the old days where a memo field was stored in a different table.  It needed different care and feeding that traditional transactional data.  This also helps avoid potential issues with schema changes on the transactional data.

3. Here are some articles that discuss how to store BLOBs to SQL (I'm assuming you're using MSSQL?)

4. Search the Internet for "store BLOB sql using VB6" (without the quotes).  You'll find oodles of other articles.

Let us know how it goes.

Author Comment

ID: 33688511
Can I store it onto a webserver and then save the link?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 11

Assisted Solution

kbirecki earned 800 total points
ID: 33690694
If I understand your question, you're thinking of pushing the file to a server that hosts a website of some kind, and that web server would provide a fully qualified URL link that would allow someone to open that file, correct?

Option 1) In that case, you are talking about more than just copying a file on a network, unless you have read/write access to a folder on the web server, indepnedant of http access, maybe something like a shared folder on any typical Windows server or a Samba share on a Linux server.  If you have that in the form of read/write access to a folder on the web server (assuming it is internal on your network and you keep in mind security concerns), you can treat it like any file share.  (Actually, I have this concept setup for limited users on our network to upload files easily to our internal-only Intranet: they can copy and paste a file to a drive mapped to a specific folder on the Intranet server and separately create a link that points to that file.)

Option 2) If you are talking about Internet-based options, to do it programatically, you'd have to use something like ftp, and that can be implemented as well in VB6, but differently, of course, because you have to use ftp protocol, not something like the FileCopy comand in VB6.  And your web server would have to have FTP server functionality implemented as well.  Here's a VB6 example to upload a file to an FTP server:
Again in this case, you'd have to already know the URL path to the folder you are uploading the file and you could store that URL in your db.

Comments: If the concept of storing the files to a web server was to prevent anyone from messing with the folder structure, that would work.  However, it just adds a small level of complexity that you may or may not be able to take advantage of (i.e. re-use or expand to include, for instance, expiration dates for the files that should be deleted after their expiration, or any other meta data about the file, etc.)  One problem that pops into my mind about this concept is the number of files you'd be storing in any given folder.  And file naming.  If you have only one folder that you push all you files to, you have to make sure on upload that the filename is unique now and into the future.  Or you'd have to manage a folder structure on the FTP server.  Windows and Linus have different limitations for numbers of files you can store in any given folder, but neither allow duplicate file names.

Another way might be this: Assuming users don't need to access the saved files directly in Windows Explorer, go back to your basic idea (I always try to keep things simple if I can), and use a network share.  But to obfuscate the location of the folder easily, create a hidden share, one that has a $ at the end of the share name, and give read/write permissions.  In your code you can reference the share like "\\Server\Share$\Path1\FileA.txt" and the visible link in your UI might be "FileA.txt" or "\Path1\FileA.txt".  I'm not sure if you are planning on showing one file per screen, listing multiple files and/or folders, or creating your own folder navigation for subsets of the files, but you can just not show the "\\Server\Share$" portion in what you display.

Hope that helps.
LVL 11

Expert Comment

ID: 33690707
Reviewing my message I noticed I typed "Linus", probably a Freudian slip.  I meant Linux, of course.
LVL 20

Accepted Solution

darbid73 earned 1200 total points
ID: 33690927
Just rounding out the options -

I would not like to takle uploading files to a webserver with VB6.  VB6 is pretty old and whilst you can do it, it is not easy.  If you were in a .NET world then it would be a lot easier.

I still am suggesting that you only save paths to your files.  Yes people will play around with folders etc.  The way I do this is have a shared folder that the user does NOT have access to.  Only User_XY has acces to this folder.

In VB6 you can "log in" or impersonate a user.  So when access to a file is needed your use the below code for User_XY.  Get the file and deal with it. Then log out.

Option Explicit
Private Declare Function RevertToSelf Lib "advapi32.dll" () As Long
Private Declare Function LogonUser Lib "advapi32.dll" Alias "LogonUserA" (ByVal lpszUsername As String, ByVal lpszDomain As String, ByVal lpszPassword As String, ByVal dwLogonType As Long, ByVal dwLogonProvider As Long, phToken As Long) As Long
Private Declare Function ImpersonateLoggedOnUser Lib "advapi32.dll" (ByVal hToken As Long) As Long
Private Sub Command1_Click()
   Dim strLine As String
   Logon "username", "password", "domain"  'I used computer name if no domain.
   Open "C:\path\to\secure\file.txt" For Input As #1
   Line Input #1, strLine
   Close #1
   MsgBox strLine
End Sub
Public Sub Logon(ByVal strAdminUser As String, ByVal strAdminPassword As String, ByVal strAdminDomain As String)
  Dim lngTokenHandle, lngLogonType, lngLogonProvider As Long
 Dim blnResult As Boolean
 lngLogonProvider = LOGON32_PROVIDER_DEFAULT
  blnResult = RevertToSelf()
  blnResult = LogonUser(strAdminUser, strAdminDomain, strAdminPassword, lngLogonType, lngLogonProvider, lngTokenHandle)
 blnResult = ImpersonateLoggedOnUser(lngTokenHandle)
End Sub
Public Sub Logoff()
 Dim blnResult As Boolean
  blnResult = RevertToSelf()
End Sub

Open in new window

LVL 11

Expert Comment

ID: 33691059
darbid73's suggestion may be better than my hidden share idea.  It's a matter of how you plan to use it.

Good suggestion, darbid73.

Author Closing Comment

ID: 33775366
thank you guys.  sorry about the delay in grading.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question