Thursday, April 7, 2011

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

11 comments:

rexdgrey said...

Impressive as always

David W said...

The ADO brings me back !

Public Sub CLIENT_EXP(S_Date As String, C_Num As String)

Dim LCJ As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command
Set LCJ = New ADODB.Connection
strConnect = "DSN=TM9VBA;Uid=tm9user;Pwd=R1234xa.;"

cmd.CommandText = "Select mat_no, usr1_04_01, USR1_04_02, USR1_04_03, USR1_04_04, USR1_02_08 from timematters9.tm9user.usertype3 where CONVERT(CHAR,DATEADD(D, date, '1800-12-28'),101) >= '" & S_Date & "' and con_no = '" & C_Num & "'"

LCJ.ConnectionString = strConnect
LCJ.Open
Set cmd.ActiveConnection = LCJ
Set rs = New ADODB.Recordset
'Set rs = cmd.Execute
rs.Open cmd.CommandText, LCJ, adOpenKeyset, adLockOptimistic

Dim ArrMn() As String, ArrInj() As String, ArrDoa() As String, ArrDs() As String, ArrDe() As String, ArrCst() As String, ArrCde() As String
Dim reccnt
reccnt = rs.RecordCount
ReDim ArrMn(0 To reccnt)
ReDim ArrInj(0 To reccnt)
ReDim ArrDoa(0 To reccnt)
ReDim ArrDs(0 To reccnt)
ReDim ArrDe(0 To reccnt)
ReDim ArrCst(0 To reccnt)
ReDim ArrCde(0 To reccnt)
Dim m As Integer
m = 0
Do Until rs.EOF
ArrMn(m) = rs(0).Value
ArrInj(m) = rs(1).Value
ArrDoa(m) = rs(2).Value
ArrDs(m) = rs(3).Value
ArrDe(m) = rs(4).Value
ArrCst(m) = rs(5).Value
ArrCde(m) = rs(6).Value
rs.MoveNext
m = m + 1
Loop

End Sub

This stuff used to drive me nuts
!

Ecommerce web development said...

Well, we had this topic on ADO Control to create Database Table using Visual Basic in our graduation studies. This article has doubled my knowledge. Thanks for sharing.

riyu said...

nice share.. :)
keep posting

Cegonsoft said...

Thank you for your post, You are given the step by step process and code for visual basic.net..thank you.. i will try your code..
Cegonsoft

MLM Software India said...

Wow! very nice information. i was searching article like this.

Thanks

http://mlmdevelopers.com/products/mlm-software/mlm-software-beta/features.html

Anonymous said...

plz mujhe visul basic k source code bata do urdu me

Anonymous said...

i using visual basic 2010 ultimate to create a database. and i doing a project using c# in vb studio 2011....can anyone tell me how to link the database into project tat i create.....so that it can function....

sap support pack said...

I am learning database and visual basic. This information is very useful for me. The code for creating database table using Visual Basic is given in simple language. The code easy to understand. Is it possible to show the source code to connect database to any application?

Vasu Sundaran said...

Thanks for sharing…… For best MLM Software Visit MLM Software

Vasu Sundaran said...

Thanks for sharing…… For best MLM Software Visit MLM Software

Post a Comment

 
Powered by Blogger