ADO Control to create Database Table using Visual Basic

'Using ADO control to create a database table from Visual Basic

'Connect to the database file and use the Connection object's Execute method to

'execute an SQL CREATE TABLE statement.

Private Sub BtnCreate_Click()

    Dim db_file As String

    Dim Conn_Data As ADODB.Connection

    Dim rs_Data As ADODB.Recordset

    Dim num_records As Integer

    ' Get the database name.

    db_file = App.Path

    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"

    db_file = db_file & "People.mdb"

    ' Open a Conn_Dataection.

    Set Conn_Data = New ADODB.Connection

    Conn_Data.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _

        "Pers_Dataist Security Info=False"

    Conn_Data.Open

    ' Drop the Employees table if it already exists.

    On Error Resume Next

    Conn_Data.Execute "DROP TABLE Employees"

    On Error GoTo 0

    ' Create the Employees table.

    Conn_Data.Execute "CREATE TABLE EmployeesTable(" & "EmployeeId INTEGER      NOT NULL," & _

        "LastName   VARCHAR(40)  NOT NULL," & "Firs_DatatName  VARCHAR(40)  NOT NULL)"

    ' Populate the table.

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, " & "'Anders_Dataon', 'Amy')"

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, 'Baker', " & "   'Betty')"

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, 'Cover', " & "   'Chauncey')"

    ' Add more records ...

    ' See how many records the table contains.

    Set rs_Data = Conn_Data.Execute("SELECT COUNT (*) FROM EmployeesTable")

    num_records = rs_Data.Fields(0)

    Conn_Data.Close

    MsgBox "Created " & num_records & " records", vbInformation, "Done"

End Sub

0 Comments

Read More »

Connect Oracle Database using Visual Basic

'How to connect Oracle Database from Visual Basic

'Sample code and connection strings to connect Oracle using Visual Basic:

Dim conn As ADODB.Connection

' Open a Conn_Dataection using Oracle ODBC.

Set Conn_Data = New ADODB.Connection

Conn_Data.ConnectionString = "Driver={Microsoft ODBC for Oracle};" & "UID=user_name;PWD=user_passsword"

Conn_Data.Open

'Open the table as in:

Dim rs_Data As ADODB.Recordset

' Open the table.

Set rs_Data = New ADODB.Recordset

rs_Data.Open "TableName", Conn_Data, adOpenDynamic, adLockOptimistic, adCmdTable

'Enter the user name password and table name as per the database.

'it must be valid one.

'To reads the data from the table and displays the values in a ListBox

' List the data.

Do While Not rs_Data.EOF

    txt = ""

    For Each fld In rs_Data.Fields

        txt = txt & Trim$(fld.Value) & ", "

    Next fld

    If Len(txt) > 0 Then txt = Left$(txt, Len(txt) - 2)

    List1.AddItem txt

    rs_Data.MoveNext

Loop

'Finally close the recordset and close the Conn_Dataection:

rs_Data.Close

Conn_Data.Close

0 Comments

Read More »

How to Use ADO Object to get data from a read only MS Access database

'How to Use ADO object to get data from a read-only MS Access database

'In the ConnDataect string, set Mode to Read.

Dim ConnData As ADODB.Connection

' Open a ConnDataection.

Set ConnData = New ADODB.Connection

ConnData.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _

    "Mode=Read;" & "PeRecordset1ist Security Info=False"

ConnData.Open

'This example reads data and displays the values in a List Box object.

Dim Recordset1 As ADODB.Recordset

' Open the Database Recordset.

Set Recordset1 = ConnData.Execute("SELECT * FROM Books", , adCmdText)

' List the data.

Do While Not Recordset1.EOF

    ' The following statement would cause an

    ' error because the ConnDataection is read-only.

    '   Recordset1!Title = "XXX"

    txt = ""

    For Each fld In Recordset1.Fields

        txt = txt & Trim$(fld.Value) & vbTab

    Next fld

    If Len(txt) > 0 Then txt = Left$(txt, Len(txt) - 1)

    List1.AddItem txt

    Recordset1.MoveNext

Loop

Recordset1.Close

ConnData.Close

0 Comments

Read More »

How to Copy data from an Access database and save in Excel spreadsheet

'How to Copy data from an Access database into an Excel spreadsheet

'Use ADO object to open the database. Using Excel as a server, open the spreadsheet.

'Read the field names from the ADO Recordset data and add them to the first row in

'the spreadsheet to make column headers.

'To enable excel application please add excel object first.

Private Sub Form_Load()

    Dim cSource_Data As String

    Dim cTarget_Data As String

    Dim oCon As ADODB.Connection

    Dim cSQL As String

    cSource_Data = App.Path & IIf(Right$(App.Path, 1) <> "\", "\", "") & "books.mdb"

    cTarget_Data = App.Path & IIf(Right$(App.Path, 1) <> "\", "\", "") & "Books.xls].[Table1]"

    Set oCon = New ADODB.Connection

    With oCon

        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & cSource_Data & _

            ";" & "Persist Security Info=False"

        .Open

        cSQL = "SELECT [Title],[URL],[ISBN],[Picture],[Pages],[CD],[Year] INTO " & _

            "[Excel 8.0;Database=" & cTarget_Data & " FROM [Books]"

        .Execute cSQL

    End With

End Sub

0 Comments

Read More »

How to Use ADO object and populate result in a ListBox object

Database - How to Use ADO to populate a ListBox Obkect with data values

'Open a Recordset that selects the desired information. Loop through the records and put the values to the ListBox.

Dim statement As String

Dim Conn_Data As ADODB.Connection

Dim rs_data As ADODB.Recordset

' db_file contains the Access database's file name.

' Open a Conn_Dataection.

Set Conn_Data = New ADODB.Connection

Conn_Data.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _

    "Pers_dataist Security Info=False"

Conn_Data.Open

' Select the data.

statement = "SELECT Title, URL FROM Books ORDER BY " & "Title"

' Get the records.

Set rs_data = Conn_Data.Execute(statement, , adCmdText)

' Load the Title field into the List Box Oject.

' Save the URL values in a collection.

Set URLs = New Collection

Do While Not rs_data.EOF

    List1.AddItem rs_data!Title

    URLs.Add CStr(rs_data!URL)

    rs_data.MoveNext

Loop

' Close the recordset and Conn_Dataection.

rs_data.Close

Conn_Data.Close

'This example saves the URL value for each record in a collection. When you

'click on a ListBox item, the program displays the URL in a Label. When you

'double click on a ListBox item, the program displays the corresponding Web

'document in a browser.

' Display the item's URL.

Private Sub List1_Click()

    If List1.ListIndex < 0 Then

        Label1.Caption = ""

    Else

        Label1.Caption = URLs(List1.ListIndex + 1)

    End If

End Sub

' Display the item's Web document.

Private Sub List1_DblClick()

    If List1.ListIndex >= 0 Then

        ShellExecute hWnd, "open", URLs(List1.ListIndex + 1), vbNullString, vbNullString, SW_SHOW

    End If

End Sub

0 Comments

Read More »