Frequently Asked VB Questions

[dee-u]

In order to make this thread as tidy as possible pls. PM me all your comments, suggestions, improvements, modifications.... And pls. post only those that are only truly "Frequently Asked Questions".... Thanks!

1. How to read/write ini?

Option Explicit
 
'declares for ini controlling
Private Declare Function GetPrivateProfileSection Lib "kernel32" Alias "GetPrivateProfileSectionA" (ByVal lpAppName As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
 
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal 
lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
 
Private Declare Function WritePrivateProfileSection Lib "kernel32" Alias "WritePrivateProfileSectionA" (ByVal lpAppName As String, ByVal lpString As String, ByVal lpFileName As String) As Long
 
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
 
Private Sub Form_Load()
On Error Resume Next
 
Dim File As String, OFLen As Double,  Str As String
 
File = "C:\temp.txt"
OFLen = FileLen(File)
 
'write few example sections:
WriteIniSection File, "Test1", ""
WriteIniSection File, "Test2", "Here shoud be found some text"
 
'write few ini strings
WriteIni File, "Test3", "Ini1", "This is ini 1"
WriteIni File, "Test1", "Ini2", "This is ini 2"
 
'inform we're written the data
MsgBox Format((FileLen(File) - OFLen) / 1024, "0.00") & " KB data written to " & Chr(34) & File & Chr(34)
 
'read the ini file
Str = Str & "Test2 section: " & vbTab & ReadIniSection(File, "Test2") & vbCrLf
Str = Str & "Test1 section: " & vbTab & ReadIniSection(File, "Test1") & vbCrLf
Str = Str & "Ini1 string: " & vbTab & ReadIni(File, "Test3", "Ini1") & vbCrLf
Str = Str & "Ini2 string: " & vbTab & ReadIni(File, "Test1", "Ini2") & vbCrLf
 
'show data
MsgBox Str
 
'end application
End
 
End Sub
 
'// INI CONTROLLING PROCEDURES
 
'reads ini string
Public Function ReadIni(Filename As String, Section As String, Key As String) As String
Dim RetVal As String * 255, v As Long
v = GetPrivateProfileString(Section, Key, "", RetVal, 255, Filename)
ReadIni = Left(RetVal, v - 1)
End Function
 
'reads ini section
Public Function ReadIniSection(Filename As String, Section As String) As String
Dim RetVal As String * 255, v As Long
v = GetPrivateProfileSection(Section, RetVal, 255, Filename)
ReadIniSection = Left(RetVal, v - 1)
End Function
 
'writes ini
Public Sub WriteIni(Filename As String, Section As String, Key As String, Value As String)
WritePrivateProfileString Section, Key, Value, Filename
End Sub
 
'writes ini section
Public Sub WriteIniSection(Filename As String, Section As String, Value As String)
WritePrivateProfileSection Section, Value, Filename
End Sub

2. How to send e-mail?
Have a look at this official FAQ article: How do I send an email using Outlook

3.How to work with registry?

'Savesetting
Private Sub Form_Unload(Cancel As Integer) 
   SaveSetting "RegCust", "Startup", "Backup", strDate
   SaveSetting "RegCust", "Startup", "LastEntry",intLastEntry
End Sub
'GetSetting
Private Sub Form_Load()
   Dim intLastEntry As Integer
   intLastEntry = GetSetting("RegCust", "Startup", _
   "LastEntry", "0")
End Sub
'DeleteSetting
Private Sub cmdDelKey_Click()
   DeleteSetting "RegCust", "StartUp", "LastEntry"
End Sub
Private Sub cmdDelSection_Click()
   DeleteSetting "RegCust", "StartUp"
End Sub
Private Sub cmdUnInstall_Click()
   DeleteSetting "RegCust"
End Sub

4. How to open Password protected Access db?

'connection to secure database
Dim Cnn As ADODB.Connection
 
'In the place where you want to establish your connection, such 
'as the Initialize event of a class module, enter the following:
 
Dim strConnect As String
Set Cnn = New ADODB.Connection
 
'Substitute your own User IDs, Password, Data Source, and System
'database in the connection string below
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=MyPassword;User ID=Administrator;" & _
        "Data Source=C:\AccessDBs\DB1.mdb;" & _
        "Persist Security Info=True;" & _
        "Jet OLEDB:System database=C:\AccessDBs\system.mdw"
 
'if you don't need you can eliminate the last line in connection string
 
    With Cnn
        .CursorLocation = adUseClient
        .Open strConnect
    End With

5. How to save image in db?
Have a look at this official FAQ article: Database - How can I store images (or other files) in a database?

6. How to write/read text file?

'write
Option Explicit
'Set a reference to Microsoft Scripting Runtime 
Private Sub Command1_Click()
    Dim fs As FileSystemObject
    Dim ts As TextStream
     
    Set fs = New FileSystemObject
    'To write
    Set ts = fs.OpenTextFile("C:\mytestfile.txt", ForWriting, True)
    ts.WriteLine "I Love"
    ts.WriteLine "VB Forums"
    ts.Close
     
    'To Read
    If fs.FileExists("C:\mytestfile.txt") Then
        Set ts = fs.OpenTextFile("C:\mytestfile.txt")
         
        Do While Not ts.AtEndOfStream
            MsgBox ts.ReadLine
        Loop
        ts.Close
    End If
    Set ts = Nothing
    Set fs = Nothing
End Sub
 
'read
Function FileText(ByVal filename As String) As String
    Dim handle As Integer
     
    ' ensure that the file exists
    If Len(Dir$(filename)) = 0 Then
        Err.Raise 53  ' File not found
    End If
     
    ' open in binary mode
    handle = FreeFile
    Open filename$ For Binary As #handle
    ' read the string and close the file
    FileText = Space$(LOF(handle))
    Get #handle, , FileText
    Close #handle
End Function

7. How to fix invalid characters in sql criteria?

Crit="Mark's"
strSQL = "SELECT * FROM MyTable WHERE ComField ='" & CleanText(Crit) & "'"
Public Function CleanText(strIn As String) As String
    On Error GoTo VBError
    Dim iAcnt As Long
    Dim strString As String
    Dim vLimit As Long
    vLimit = Len(strIn)
    For iAcnt = 1 To vLimit
        Select Case Asc(Mid$(strIn, iAcnt, 1))
        Case 10, 13
            strString = strString & Mid$(strIn, iAcnt, 1)
        Case 124
            strString = strString & "!"
        Case 39
            strString = strString & "''"
        Case 34
            strString = strString & """"
        Case Is < 32
            strString = strString & " "
        Case Is > 126
            strString = strString & " "
        Case Else
            strString = strString & Mid$(strIn, iAcnt, 1)
        End Select
    Next
    CleanText = strString
    CleanText = Trim$(CleanText)
Exit Function
VBError:
    MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
    Resume Next
End Function

8. How to dynamically add controls?

Dim WithEvents vTextBox As TextBox
Set vTextBox = Controls.Add("vb.textbox", "DeeU")