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!
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 11:23 AM.
The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!
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!
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!