Retrieving Tables and Columns in a Database

This is how to retrieve tables & columns in the table from a database:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = con.OpenSchema(adSchemaColumns)

While Not rs.EOF
    Text1.Text = Text1.Text & rs!Table_Name & " - " & rs!COLUMN_NAME & vbCrLf
    rs.MoveNext
Wend

Read More

Retrieving Tables in a Database

This is how to retrieve all tables in a database:
Object needed:
A Textbox named Text1

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = con.OpenSchema(adSchemaTables)
While Not rs.EOF
   If rs!Table_Type = "TABLE" Then Text1.Text = Text1.Text & rs!TABLE_NAME & vbCrLf
   rs.MoveNext
Wend

Read More

How to get column names of a table

This is how to retrieve column / field names of a table from VB using adodb connection:

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database.mdb;Persist Security Info=False"

Set rs = New ADODB.Recordset
sql = "select * from table_name"
Set rs = con.Execute(sql)

Dim i As Integer
For i = 0 To rs.Fields.Count - 1
    MsgBox rs.Fields(i).Name
Next

Read More

Operation is not allowed when object is closed

Error: Operation is not allowed when object is closed.

Solution:
1. Check if a recordset or connection is opened before recordset operation is performed.

2. If stored procedure is used while retrieving the data to recordset, REMEMBER to use "SET NOCOUNT ON" at the beginning of the stored procedure. This is a problem which usually occurs when multiple actions are executed in a stored procedure.

3. Again, if stored procedure is used, make sure the stored procedure does return a record. Otherwise, make sure there is a routine in the code checking if the record doesn't return nothing.

e.g.

create procedure dbo.spr_getData
as
set nocount on

select ......

select ......

set nocount off

Read More

Open and close CD-ROM tray in VB

Create a module, and put this code in it:

Declare Sub mciSendStringA Lib "winmm.dll" (ByVal lpstrCommand As String, ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, ByVal hwndCallback As Long)

To Open CD-ROM tray, use this method:

Sub OpenCDTray()
    mciSendStringA "Set CDAudio Door Open", 0&, 0, 0
End Sub

To Close CD-ROM tray, use this method:

Sub CloseCDTray()
    mciSendStringA "Set CDAudio Door Closed", 0&, 0, 0
End Sub

Read More

Delete data by date

Sometimes, new Visual Basic developers may meet such problems as retrieving data by date. Just need to use # to make it right

e.g.
db.Execute "DELETE FROM table_name WHERE FieldDate < #" & Format(Date, "yyyy/mm/dd") & "#"

Read More

Read & Write .ini files

Reading from and writing to .ini files is very useful and is a widely used method to set or get some uncertain keys, such as database location, period, or any other things that are common, but are subject to change.

It is not necessarily .ini files. The extension may vary, depends on the developer will, but .ini is the most commonly used extension.

It's actually quite simple, with the utilization of API. API is very useful in VB. It can do a lot of things can't be done using classical VB components.

1. Make a text files with the format specified below: name it test.ini, and put it in the same folder where the application is made.

[Header_Key1]
key1=value1
key2=value2
...
[Header_Key2]
key3=value3
...

where:
Header_Key1, Header_Key2 are the keys used for a group of keys.
key1, key2, key3 are the keys of the values to be read and written.

e.g. (To get current period and semester from text files)
[Period]
Period=2009
Semester=2
[Content]
AnotherContent=this is that other content


2. Make a module and put the following functions (to read and write text files) into the module:

Option Explicit
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

Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" _
(ByVal lpApplicationname As String, ByVal lpKeyName As Any, ByVal lsString As String, ByVal lplFilename As String) As Long


Function GetfromINI(SectionHeader$, VarName$, FileName$) As String
   Dim RetStr As String
   RetStr = String(255, Chr(0))
   GetfromINI = Left(RetStr, GetPrivateProfileString(SectionHeader$, ByVal VarName$, "", RetStr, Len(RetStr), FileName$))
End Function

3. To get the value of the keys on the .ini files :
Objects used:
3 textboxes named Text1, Text2, Text3

Dim isi1 As String, isi2 As String, isi3 As String

isi1 = GetfromINI("Period", "Period", App.Path + "\test.ini")
isi2 = GetfromINI("Period", "Semester", App.Path + "\test.ini")
isi3 = GetfromINI("Content", "AnotherContent", App.Path + "\test.ini")
Text1.Text = isi1
Text2.Text = isi2
Text3.Text = isi3

4. To save the value on textbox to the .ini files :
Objects used:
3 textboxes named Text1, Text2, Text3

WritePrivateProfileString "Period", "Period", Text1.Text, App.Path + "\test.ini"
WritePrivateProfileString "Period", "Semester", Text2.Text, App.Path + "\test.ini"
WritePrivateProfileString "Content", "AnotherContent", Text3.Text, App.Path + "\test.ini"

That's all.

Read More

ADO: Recordcount return -1

Error : Recordcount May Return -1 while using ADO connection

The problem is ADO defaults to a Server side cursor. You can only use the RecordCount property with a Client side Cursor.

Do something like...
rsMyRecordset.CursorLocation = adUseClient

If I may guess, the reason the RecordCount doesn't work in your code is that the default cursortype, adOpenForwardOnly, of the recordset doesn't support it. ForwardOnly recordsets ("firehose cursors") are very fast when you just want to run through them from top to bottom. So, if you are using adOpenForwardOnly for the cursorType, you may have to change it.

Here's the code:

rs.CursorLocation = adUseClient
rs.Open sql, con, adOpenKeyset, adLockPessimistic

Read More

Check whether a file exists or not

This is actually a simple code, but someones may not know it when first using VB, so I post it here.
Here's the code to find a file named file.txt:

If Dir(App.Path & "\file.txt") <> "" Then
   MsgBox "Found: file.txt"
End If

Read More

How to detect running program

Make a module and use the functionality of API to make it work.

Code in the module:

Option Explicit
Private Declare Function GetWindowText Lib "user32.dll" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal nMaxCount As Long) As Long

Private Declare Function EnumWindows Lib "user32.dll" _
(ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

Private Target As String
Private IsOpen As Boolean

Private Function EnumCallback(ByVal app_hWnd As Long, ByVal param As Long) As Long
   Dim buf As String * 256
   Dim title As String
   Dim length As Long
   Dim hWndREC As Long
   length = GetWindowText(app_hWnd, buf, Len(buf))
   title = Left$(buf, length)
   If InStr(1, title, Target, vbTextCompare) Then
       IsOpen = True
   End If
   EnumCallback = 1
End Function

Public Function WindowOpen(app_name As String) As Boolean
   'app_name is the name of the application when tracked on Windows Task Manager
   IsOpen = False
   Target = app_name
   EnumWindows AddressOf EnumCallback, 0
   If IsOpen Then WindowOpen = True
End Function

To check whether a program is running, e.g. Notepad:

If WindowOpen("Notepad") Then
   MsgBox "Notepad is opened"
Else
   MsgBox "Notepad is not opened"
End If

Read More

How to clear all textboxes in a form

Sometimes, when we use a lot of textboxes in a form, and want to clean all the textboxes the form, it's not efficient to clean it one by one by using Text1.Text = "", Text2.Text = "", etc.
There is a short way to do it:

Dim a As Control
For Each a In Form1
   If TypeOf a Is TextBox Then
       a.Text = ""
   End If
Next

Read More

Get file name from a path in VB

One of my friend once asked how to get file name from a path. There're actually a lot of ways to do it. I'll try a simple one, using Split and UBound function.

e.g. the path is C:\Test\DeeperPath\EvenDeeperPath\Test.txt
and we're going to get the file name, i.e. Text.txt

The name of the controls:
textbox : Text1, containing the path

Some things we have to know:
1. To split the path into array: Split(Text1.Text, "\")
We'll get array of string:
Split(Text1.Text, "\")(0) = C:
Split(Text1.Text, "\")(1) = Test
Split(Text1.Text, "\")(2) = DeeperPath
Split(Text1.Text, "\")(3) = EvenDeeperPath
Split(Text1.Text, "\")(4) = Test.txt

2. To get upper bound of an array: UBound(array)

So, to get the file name from a path, we just need a line of code:
Split(Text1.Text, "\")(UBound(Split(Text1.Text, "\")))

The result will be: Test.txt

Read More