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]

This may help:

http://msdn2.microsoft.com/en-us/library/Aa164917(office.10).aspx

Are you using Access to do this? If so, it is very easy to set the format with DAO and TableDefs.

 

[dhookom]

I never set formats, input masks, captions, or lookups in table definitions. I leave all of this stuff for the "presentation layer" of forms and reports.

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]

 

[Norminator]

thanx but I dont think that helps. Let me explain a bit more, I'm using ACCESS Database.

When u create a table in design view and you look down the bottom and see FIELD PROPERTIES with the GENERAL and LOOKUP TABS and in the listing for GENERAL you have Format, Input Mask, Caption .... etc I want to create the table and be able to access the Format and set the Date/Time Fields to Short Time; as you see i'm using vb.

Need urgent help

 

[dhookom]

If you are using VB what difference does the format property have within your application? A date is a date is a date. I wasn't aware that VB cared about how the date would be displayed in an Access table/datasheet view.

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]

 

[JoeAtWork]

I'm with Duane here. Presentation of data is at the UI layer (or if you are ultra n-tier orthodox, at the "presentation" layer just below the UI layer).

Apply whatever format you want when you load the textboxes on a form, etc. Databases are for storing the raw data and no end-user should be concerned with what it looks like under the hood.

 

[Norminator]

well i'm writing a patch to update a database, which is already running with a different software that uses a table similar to the one i'm creating that must have these specific field properties as the software communicates with it this way.

So are there any answers?

 

[dhookom]

Norminator,
Have you read the reference that Remou suggested? Any comments?

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]

 

[Norminator]

Its giving me an error whenever i use it and plus it doesnt shows how to use the key word Format in the field properties.

 

[Norminator]

thanx for all the replies though, i've been searching on google and still havent found a solution! Appreciate any help!

 

[Remou]

I think you will just have to go with DAO.

 

[Norminator]

thanx for all the replies though, i've been searching on google and still havent found a solution! Appreciate any help!

 

[Norminator]

DAO what do you mean

 

[Remou]

An example.

Sub AddFormat()
'Requires reference to Microsoft DAO 3.6 Object Library
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As Database

Set db = CurrentDb

Set tdf = db.TableDefs("Contacts")

Set fld = tdf.Fields("LDate")
fld.Properties("Format") = "Short Date"
Debug.Print fld.Properties("Format")
End Sub

 

[Norminator]

thanx remou but what is the variable CurrentDB? I've seen this code elsewhere in other examples! I'm using vb.net would it be correct if I do that?

 

[Norminator]

is there a vb.net code?

 

[Remou]

That was the point of my earlier question, "Are you using Access to do this?". As you are not, you must go with some other option, such as a query, which will allow you to apply a format. This is what dhookom and JoeAtWork were talking about (at least I think it is )

 

[Norminator]

well i used a query as u saw in my code but how do i access the format properties through sql?

 

[Remou]

That is a query to create a table. You can then use a select query.

[tt]SELECT transfer_id, call_id, exchange, unhold, hold, ringing,
Format(transferred,'Short Time')
FROM tblTranser[/tt]

 

[Norminator]

hey thanx remou but cant you format the table while creating it?