We help IT Professionals succeed at work.

Using ADOX to create Date field in Access

hi,
   I currently are using ADOX to create a field in the selected table in Ms Access Database, the basic operation is work fine but i need to know how to create a DATE field in the table with the format i want(access default format for date is mm-dd-yy), but what i want is that the date field should accept the format i given as the default format(example dd-mmm-yyyy).
 I wish to know how can i achieve this objective at runtime using vb coding. Please gave me some code to refer if possible. Thank
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:
Hi,

Here is an example of using ADOX to create an MDB that posted by TimCottee on the question below:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20178543

Add a reference to Microsoft ADO Extenstion
for Security etc (or something like that) to your project and use a variant of the following example:

Dim catAccess As New ADOX.Catalog
  Dim tblValExp As New ADOX.Table
  Dim cnnAccess As New ADODB.Connection
  Dim rstValExp As New ADODB.Recordset
  catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Jet OLEDB:Engine
Type=4;"
  catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
  With tblValExp
      .Name = "tblValExp1"
      .Columns.Append "Stock Name", adVarWChar, 40
      .Columns.Append "Stock Symbol", adVarWChar, 6     '1
      .Columns.Append "Stock Price", adSingle       '2
      .Columns.Append "Est Date", adVarWChar, 10           '3
      .Columns.Append "Est Year", adVarWChar, 6         '4
      .Columns.Append "Price High(0)", adSingle     '5
      .Columns.Append "Price High(1)", adSingle     '6
      .Columns.Append "Price High(2)", adSingle     '7
      .Columns.Append "Price High(3)", adSingle     '8
      .Columns.Append "Price High(4)", adSingle     '9
      .Columns.Append "Price High(5)", adSingle     '10
  End With
  catAccess.Tables.Append tblValExp
  Set tblValExp = Nothing
  Set catAccess = Nothing
  cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security

Info=False"
  rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
  rstValExp.AddNew
  rstValExp.Fields(0).Value = "Name"
  rstValExp.Fields(1).Value = "Symbol"
  rstValExp.Fields(2).Value = 1.5
  rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
  rstValExp.Fields(4).Value = "2000"
  rstValExp.Fields(5).Value = 1.5
  rstValExp.Fields(6).Value = 1.5
  rstValExp.Fields(7).Value = 1.5
  rstValExp.Fields(8).Value = 1.5
  rstValExp.Fields(9).Value = 1.5
  rstValExp.Fields(10).Value = 1.5
  rstValExp.Update
  rstValExp.MoveFirst
  Set rstValExp = Nothing
  Set cnnAccess = Nothing

'Hope will help.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:
and no matter how, please do to evaluate the effort of experts and try to Not giving him a "C" if possible.

regards.

Author

Commented:
i think i will explain a bit extra here, what i want is to create a date field in the database and set the format properties(you know that in the Ms Access there got a properties field name Format)to the format i want at runtime.
Hope this will make my question clear. Thanks for help
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:
Hi,

Try this:

> .Columns.Append "myDate", adDBDate

regards.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:
sorry, ignore my second comment.
BRONZE EXPERT
Author of the Year 2009

Commented:
Hi tayleeming,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.
    *** question was not answered.

tayleeming, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
Per recommendation, points refunded and question closed.

Netminder
CS Moderator