Code to format a table property in vb 1

[Norminator]

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\

http://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

 

[Remou]

Not unless you want to create an instance of Access.

 

[Norminator]

what do you mean by that? sorry for all the questions, but this is just for an update to an already built software which has been distributed to customers, so we need them to run this patch to update their database with the new table. So i need this patch to create the table and apply all the necessary properties to the table. Creating an instance of Access what do you mean? can this solve the problem?

 

[Remou]

An example using VBScript:

'VB Script
Const cDatabaseToOpen = "C:\Docs\Tek-Tips.mdb" 

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

Set db = acapp.CurrentDb
Set tdf = db.TableDefs("Contacts")
Set fld = tdf.Fields("LDate")

fld.Properties("Format") = "Short Date"

MsgBox fld.Properties("Format")

AcApp.Quit

 

[Norminator]

thanx for the reply i'll c if it works

 

[Norminator]

ahmm can u tell me the datatypes for each of the variables please:

AcApp, db, tdf, fld where are the declarations?

 

[Remou]

It is VBScript. VBScript does not allow a standard VB declaration. I have shown declarations in the VBA example.

 

[Norminator]

oh ok not familiar with vb scripts still

 

[Norminator]

I tried it ok but it returned a error saying property not found.

Here is the code:

Const cDatabaseToOpen = "C:\Inetpub\

http://wwwroot\CallAccounting\Database\CallAccounting.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/

http://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 tblTranser( 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("tblTranser")
Set fld = tdf.Fields("unhold")

fld.Properties("Format") = "Short Date"

MsgBox fld.Properties("Format")

AcApp.Quit

 

[Remou]

Ok.

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

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

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

MsgBox fld.Properties("Format")

AcApp.Quit

 

[Norminator]

thanx very much remou it worked u surely know ur stuff!

It was the first time i've encountered this problem so i had a hard time solving it. There is always room to learn more when programming.
Nuff Respect!

 

[dhookom]

Remou,
Great job hanging in there with the code examples. Deserves a star.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]