Results 1 to 3 of 3

Thread: Creating a table or Importing a table

  1. #1
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default Creating a table or Importing a table

    Is there a specific way to import a table or create a table using vb.net? What are the procedures and the commands to use? Need urgent help!
    Last edited by norminator; Oct 23, 2007 at 12:23 PM.
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

  2. #2
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default

    Ok I found out how to create the table. However I need to know how to format each column (for example the date/time column i need to change it to Short Time) please offer any help.

    Code is below:
    Try
    Const strConnection As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source= C:\Inetpub\wwwroot\CallAccounting\Database\******. mdb"

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    cnn.ConnectionString = strConnection
    cnn.Open()

    cmd.ActiveConnection = cnn
    cmd.CommandText = "create table tblTranser (transfer_id counter, call_id number, exchange number, unhold DATETIME, hold DATETIME, ringing DATETIME, transferred DATETIME Format (Short Time))"
    'cmd.CommandText = "create table tnn1 (a varchar(10), b number)"

    cmd.Execute()
    cnn.Close()
    MsgBox("Table created successfully")
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

  3. #3
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default

    Got the solution with a bit of research here is a vb script that does it:

    Const cDatabaseToOpen = "C:\Inetpub\wwwroot\CallAccounting\Database\CallAc counting.mdb"
    Dim oCA_Conn,oRecordset, sSQL, sCA_ConnStng

    Set oCA_Conn=CreateObject("ADODB.Connection")
    Set oRecordset=CreateObject("ADODB.Recordset")

    'Call Accounting Database Connection String
    sCA_ConnStng="Driver={Microsoft Access Driver (*.mdb)};Dbq=" & _
    ("C:/Inetpub/wwwroot/CallAccounting/Database/CallAccounting.mdb")

    'Open the Database connection
    oCA_Conn.open(sCA_ConnStng)

    'Add a new table tblTransfer to CallAccounting.mdb
    sSQL="CREATE TABLE tblTransfer( transfer_id counter, call_id Number, extension Number, unhold DateTime, hold DateTime, ringing DateTime, transferred DateTime, call_released DateTime)"
    oRecordset.Open sSQL, oCA_Conn


    Set AcApp = CreateObject("Access.Application")
    AcApp.OpenCurrentDatabase cDatabaseToOpen

    Set db = acapp.CurrentDb
    Set tdf = db.TableDefs("tblTransfer")
    Set fld = tdf.Fields("unhold")

    Set fld = tdf.Fields("unhold")
    Set prp = fld.CreateProperty("Format", 10, "Short Date")
    fld.Properties.Append prp

    Set fld2 = tdf.Fields("hold")

    Set fld2 = tdf.Fields("hold")
    Set prp = fld2.CreateProperty("Format", 10, "Short Date")
    fld2.Properties.Append prp

    Set fld3 = tdf.Fields("ringing")

    Set fld3 = tdf.Fields("ringing")
    Set prp = fld3.CreateProperty("Format", 10, "Short Date")
    fld3.Properties.Append prp

    Set fld4 = tdf.Fields("transferred")

    Set fld4 = tdf.Fields("transferred")
    Set prp = fld4.CreateProperty("Format", 10, "Short Date")
    fld4.Properties.Append prp

    Set fld5 = tdf.Fields("call_released")

    Set fld5 = tdf.Fields("call_released")
    Set prp = fld5.CreateProperty("Format", 10, "Short Date")
    fld5.Properties.Append prp

    'MsgBox fld.Properties("Format")

    AcApp.Quit
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •