473,573 Members | 2,545 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Enter a Description using scripts in MS-ACCESS

I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.

EX:

CREATE TABLE(
Pk_myPrimaryKey INTEGER CONSTRAINT pk PRIMARY KEY DESCRIPTION 'This
is the primary key of the table',

Field1 TEXT(50)
)

Thx
DoOmX
Nov 13 '05 #1
8 3978

doomx wrote:
I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.


Sorry, doomx, Jet Data Definition SQL statements can't do that. You'll
have to use DAO or ADOX to put those comments beside table fields.

-Ken

Nov 13 '05 #2
On 28 Jan 2005 08:30:09 -0800, "Ken Ismert" <ki*****@texass ystems.com>
wrote:

doomx wrote:
I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.


Sorry, doomx, Jet Data Definition SQL statements can't do that. You'll
have to use DAO or ADOX to put those comments beside table fields.

-Ken


ADOX? what property is it there?
Descriptions are second class citizens, not even a standard DAO
property let alone anything to do with SQL, and do not propagate
between linked tables unless things have changed. A great shame
indeed.
David

Nov 13 '05 #3
David,

The ADOX Column object has a Properties collection of providor-specific
properties. This is a rough analog of the DAO Field object's Properties
collection, where Jet stores the field description for a table.

Perhaps the Jet field description is also returned in an ADOX Column
property. I'll leave it to a more interested poster to verify/refute
that.

-Ken

Nov 13 '05 #4

"doomx" <do*****@hotmai l.com> wrote in message
news:eb******** *************** ***@posting.goo gle.com...
I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.

EX:

CREATE TABLE(
Pk_myPrimaryKey INTEGER CONSTRAINT pk PRIMARY KEY DESCRIPTION 'This is the primary key of the table',

Field1 TEXT(50)
)

Thx
DoOmX

To add a little more (at least for A2K and before):

The DAO QueryDef object (MS Access Query) has an SQL property, which
is a String. It retains no (or very little) formatting. Whenever
anything gets stored to it via the GUI, it gets reformatted to how
Access wants it, wiping out all spacing, and, on occasion, destroying
the executability/validity of the Query. Of course, no comments are
allowed. Basically, it's one step above useless. Unfortunate,
really.
Sincerely,

Chris O.
Nov 13 '05 #5
On 28 Jan 2005 07:03:48 -0800, do*****@hotmail .com (doomx) wrote:
I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.

EX:

CREATE TABLE(
Pk_myPrimaryKey INTEGER CONSTRAINT pk PRIMARY KEY DESCRIPTION 'This
is the primary key of the table',

Field1 TEXT(50)
)

Thx
DoOmX


Not with SQL statements, but you can with DAO (set a DAO reference in
Access 2K+). These are routine I use to check and correct application
table stuctures (there's another one to capture finished table
stuctures, but you can figure that out) as defined in the
"tTableLibraryF ields" table (partially defined below)

Field descriptions in access tables can be pissy becuse they're also
used on forms as default user prompts.

I know this is more than what you asked for, but use anything you find
value in, or ignor the whole thing.
Tom
Public Sub CheckTableDc(St ructure As Byte, Name As String)
' check for existance and correctness of user named table structure
Dim i As Integer ' array index
Dim DB As Database ' database object
Dim Rs As Recordset ' fields record set
Dim TD As TableDef ' table definition object
Dim FD As Field ' field definition object
Dim Pd As Property ' property definition object
Dim Ix As Index ' index definition object
Dim Found As Boolean ' table found flag
Dim Delete As Boolean ' delete table flag
Dim Verify As Boolean ' verify flag
Dim Sql As String ' sql

'On Error GoTo ErrorRouter ' on error, goto error
routine
Set DB = CurrentDb ' get current db reference
Set TD = DB.CreateTableD ef(Name) ' create table
Sql = "SELECT tTableLibraryFi elds.Name, tTableLibraryFi elds.Size,
tTableLibraryFi elds.Type, tTableLibraryFi elds.Caption,
tTableLibraryFi elds.DefaultVal ue, tTableLibraryFi elds.Descriptio n,
tTableLibraryFi elds.Index, tTableLibraryFi elds.Attributes " & _
"FROM tTableLibraryFi elds " & _
"WHERE (((tTableLibrar yFields.TableID ) = " & Structure & ")) " &
_
"ORDER BY tTableLibraryFi elds.Ordinal;"
Set Rs = DB.OpenRecordse t(Sql) ' open record set

For Each TD In DB.TableDefs ' loop through all tables in
db
If TD.Name = Name Then ' if table found
Found = True ' set found flag
Exit For ' break loop
End If ' if table found
Next TD ' next table

If Found = True Then ' if table found, TD =
tabledef
GoSub VerifyTable ' verify table
If Delete = True Then GoSub CreateTable ' if table did NOT verify,
it was deleted, so create new one
Else ' if table NOT found
GoSub CreateTable ' create table
End If ' if table found, TD =
tabledef
Rs.Close ' close recordset
DB.Close ' close database
Set DB = Nothing ' free resources
Set Rs = Nothing ' free resources
Set TD = Nothing ' free resources
Set FD = Nothing ' free resources
Set Pd = Nothing ' free resources
Set Ix = Nothing ' free resources
Exit Sub ' all done

CreateTable: ' table wasn't found, so
build it
Set TD = DB.CreateTableD ef(Name) ' create table definition
Rs.MoveFirst ' move to first record
Do Until Rs.EOF ' loop through all fields
for table
GoSub CreateField ' create field
Rs.MoveNext ' next field
Loop ' create next field
DB.TableDefs.Ap pend TD ' append all completed
communication table fields
TD.Fields.Refre sh ' refresh fields collection
TD.Indexes.Refr esh ' refresh indexes collection
Set TD = DB.TableDefs(Na me) ' get tabledef for table
For Each FD In TD.Fields ' loop through fields
Rs.MoveFirst ' move to first record
Do Until Rs.EOF ' loop through all fields
for table
If FD.Name = Rs!Name Then ' if field found, set
description
SetFieldPropert y FD, "Descriptio n", dbText, Rs!Description
SetFieldPropert y FD, "Caption", dbText, Rs!Caption
Exit Do ' break loop
End If ' if field found
Rs.MoveNext ' next field
Loop ' create next field
Next FD ' next field
Return ' return to caller

CreateField:
If Rs!Type = dbText Then ' if creating a text field
Set FD = TD.CreateField( Rs!Name, Rs!Type, Rs!Size) ' create field
with name, storage type, and size
FD.AllowZeroLen gth = True ' set field ZeroLength
string
ElseIf Rs!Type = dbMemo Then ' if creating a memo field
Set FD = TD.CreateField( Rs!Name, Rs!Type) ' create field with name
and storage type
FD.AllowZeroLen gth = True ' set field ZeroLength
string
Else ' if NOT creating a text
field
Set FD = TD.CreateField( Rs!Name, Rs!Type) ' create field with name
and storage type
End If ' if creating a text field
If Rs!Attributes <> 0 Then ' if user suFSlied
attribute(s)
FD.Attributes = Rs!Attributes ' set field attributes
End If ' if user suFSlied
attribute(s)
If Len(Rs!DefaultV alue) <> 0 Then FD.DefaultValue = Rs!DefaultValue
TD.Fields.Appen d FD ' append field to fields
collection

If Rs!Index <> ifNo Then ' if field has an index
If Rs!Index = ifPrimary Then ' if primary index
Set Ix = TD.CreateIndex( "PrimaryKey ") ' create index named
"PrimaryKey "
Set FD = Ix.CreateField( Rs!Name) ' for field
Ix.Primary = True ' set if index is primary
ElseIf Rs!Index = ifUnique Then ' if unique index
Set Ix = TD.CreateIndex( Rs!Name) ' create index named same as
field
Set FD = Ix.CreateField( Rs!Name) ' for field
Ix.Unique = True ' set if index is unique
ElseIf Rs!Index = ifUniqNo Then ' if NOT unique index
Set Ix = TD.CreateIndex( Rs!Name) ' create index named same as
field
Set FD = Ix.CreateField( Rs!Name) ' for field
Ix.Unique = False ' set if index is unique
End If ' if primary index
Ix.Fields.Appen d FD ' append field to fields
collection
TD.Indexes.Appe nd Ix ' append index to indexes
collection
End If ' if field has an index
Return ' return to caller

VerifyTable: ' table was found, so verify
it
Set TD = DB.TableDefs(Na me) ' get tabledef for table
Rs.MoveFirst ' move to first record
For Each FD In TD.Fields ' loop through fields
Verify = False ' default verify flag
GoSub VerifyField ' verify field
If Verify = False Then ' if field does NOT match
field description
Delete = Delete
DB.TableDefs.De lete Name ' delete table
Delete = True ' set table deleted flag
Return ' break loop, return to
caller
End If ' if database does NOT match
field description
Next FD ' next field
For Each Ix In TD.Indexes ' loop through indexes
Verify = False ' default verify flag
GoSub VerifyIndex ' verify index
If Verify = False Then ' if index does NOT match
field description
Delete = Delete
DB.TableDefs.De lete Name ' delete table
Delete = True ' set table deleted flag
Return ' break loop, return to
caller
End If ' if database does NOT match
field description
Next Ix ' next index
Return ' return to caller

VerifyField:
Rs.MoveFirst ' move to first record
Do Until Rs.EOF ' loop through all fields
for table
If FD.Name = Rs!Name Then ' if field found, verify
If FD.Type <> Rs!Type Then Return ' if types don't match,
return
If FD.Type = dbText Then ' if Text type, check size
If FD.Size <> Rs!Size Then Return ' if sizes don't match,
return
If FD.AllowZeroLen gth <> True Then Return
ElseIf FD.Type = dbMemo Then ' if Memo type, check size
If FD.AllowZeroLen gth <> True Then Return
End If ' if Text type
If (FD.Attributes And Rs!Attributes) <> Rs!Attributes Then Return
If FD.DefaultValue <> Rs!DefaultValue Then Return
Verify = True ' signal caller that request
succeeded
Exit Do ' exit loop
End If ' if found description
Rs.MoveNext ' next field
Loop ' verify next field
Return ' return to caller

VerifyIndex:
Rs.MoveFirst ' move to first record
Do Until Rs.EOF ' loop through all fields
for table
If Ix.Fields = "+" & Rs!Name Then ' if field found
Select Case Rs!Index ' get field description
index code
Case ifNo ' if no index, this field is
not defined as a index, but no harm done
Case ifPrimary ' if primary index
If Ix.Name <> "PrimaryKey " Then Return
If Ix.Primary = False Then Return '
If Ix.Unique = False Then Return '
Case ifUnique ' if unique index
If Ix.Name <> Rs!Name Then Return '
If Ix.Primary = True Then Return '
If Ix.Unique = False Then Return '
Case ifUniqNo ' if non-unique index
If Ix.Name <> Rs!Name Then Return '
If Ix.Primary = True Then Return '
If Ix.Unique = True Then Return '
Case Else ' if coding boo-boo
Return
End Select ' get field description
index code
Verify = True ' signal caller that request
succeeded
Exit Do ' exit loop
End If ' if field found
Rs.MoveNext ' next field
Loop ' verify next field
Return ' return to caller

ErrorRouter: ' error router
ERR.Raise ERR.Number, ERR.Source, ERR.Description ' let caller handle
error reporting
End Sub

'
*************** *************** *************** *************** *************** ***************
' Disk-Comm Private Routines
'
*************** *************** *************** *************** *************** ***************
Private Sub SetFieldPropert y(FD As Field, PropName As String, PropType
As Integer, PropValue As Variant)
' Create Property object, setting its Name, Type, and Value properties
Const ERR_PROPERTY_NO NEXISTENT = 3270 ' property not set,
therefore does not exit
Dim MyProperty As Property ' property object

On Error Resume Next ' function handles errors
FD.Properties(P ropName) = PropValue ' force the property to be
set, this creates an error if the property does not exist
If ERR <> 0 Then ' if error occurred when
property value was set
If ERR = 3270 Then ' if the error is "Property
does not exist"
On Error GoTo 0 ' reset error
On Error Resume Next ' function handles errors
(again because of reset)
Set MyProperty = FD.CreateProper ty(PropName, PropType, PropValue)
FD.Properties.A ppend MyProperty ' Append this property
On Error GoTo 0 ' reset error
End If ' if property does not exist
End If ' if error occurred when
property value was set
End Sub
Nov 13 '05 #6
rkc
Ken Ismert wrote:
David,

The ADOX Column object has a Properties collection of providor-specific
properties. This is a rough analog of the DAO Field object's Properties
collection, where Jet stores the field description for a table.

Perhaps the Jet field description is also returned in an ADOX Column
property. I'll leave it to a more interested poster to verify/refute
that.


Not that interested, but I can verify that Description is a member
of the ADOX Columns.Column. Properties collection when using the Jet
OLEDB provider.

Along with:

Autoincrement
Default
Nullable
FixedLength
Seed
Increment
JetOLEDBColumnV alidationText
JetOLEDBColumnV alidationRule
JetOLEDBIISAMNo tLastColumn
JetOLEDBAutoGen erate
JetOLEDBOneBLOB perPage
JetOLEDBCompres sedUNICODEStrin gs
JetOLEDBAllowZe roLength
JetOLEDBHyperli nk
Nov 13 '05 #7
"Ken Ismert" <ki*****@texass ystems.com> wrote in message news:<11******* *************** @z14g2000cwz.go oglegroups.com> ...
doomx wrote:
I'm using SQL scripts to create and alter tables in my DB

I want to know if it's possible to fill the description(lik e in the
Create table UI) using these scripts.


Sorry, doomx, Jet Data Definition SQL statements can't do that. You'll
have to use DAO or ADOX to put those comments beside table fields.

-Ken


Thx, but i'm using Acces as a backend with VS.net 2003(VB.net), so I
don't want to start using VBA just to have a description of my
columns, it would be a waste of time. I'll but comments in my sql
scripts and give significant names to my columns instead.
Nov 13 '05 #8
doomx,

You don't have to use VBA, just the DAO library in .NET via COM
InterOp. Tom Warren's code sample basically show you how.

-Ken

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4175
by: Ali Eghtebas | last post by:
Hi, I have 3 questions regarding the code below: 1) Why can't I trap the KEYDOWN while I can trap KEYUP? 2) Is it correct that I use Return True within the IF-Statement? (I've already read the documentation but it is rather hard to understand so please don't refer to it :) 3) Many examples in the newsgroups use Return...
6
14545
by: Alex Vilner | last post by:
Hello! We have a set of individual .SQL scripts which we would like to execute against a MS SQL Server 2000. Is there any way to have ISQL utility (or any other means) to execute all of them without having to establish a separate database connection for each script: isql -Ux -Py -Ss -i script1.sql isql -Ux -Py -Ss -i script2.sql isql...
3
1825
by: markaelkins | last post by:
Hi. I am trying to enter a variable in the treenodesrc of a treenode. I am basically trying to send an ID variable into sql to return different records. I've searched everywhere and cannot find the answer. I'd appreciate and help. Thanks. What I'm doing is creating a treeview with the structure as follows (this is the expanded view): -...
1
1896
by: Ted | last post by:
I managed to get it installed OK, along side MS Visual Studio 2005 (with which I received it). During the install, I made sure I installed everything. I have developed a number of applications using MySQL v 5 and PostgreSQL, but I have not worked with MS SQL. Playing with it after installing it, and running through several tutorials, I...
1
4626
by: dcnicholls | last post by:
This is my first post to this forum: I use ASP occasionally but don't know a lot of it, and the Windows IIS was invented by MS to torture me :) So I'm not sure if this should be herre or on the IIS area, but here goes: For the local mirror on my development PC of a site I've been managing for some years, I use the built in IIS (XP pro sp2) to...
13
3226
by: Rick | last post by:
The following code will enter an infinate loop when in ReadChars. I can only make it happen when reading a Stream and with this particular XML. If I use the ReadInnerXml call rather than my own ReadElementBodyAsXml the code works, but is less efficent. ReadElementBodyAsXml is required by my application with .Net Framework 1.1. The code...
1
11635
by: Alex | last post by:
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to manage them all. When I tried to install MS SQL 2000 Enterprise Manager on Vista, it gave me a message saying it's not compatible, so I'll have to...
18
4588
by: Zytan | last post by:
I want the same function to be run whether you press Enter or double click the listbox. It seems really verbose to write both handlers to both events everytime, even if they both call the same function, or one calls the other. Isn't there an event for 'ListBox selection selected' that is automatically called by ALL the standard GUI ways of...
24
8403
by: MichaelK | last post by:
Who knows how to prevent submitting a form on the press Enter button before all fields on the form are filled up. People just enter the first field hit Enter and it submits the form and doing validation, of course flushing with the bunch of messages because rest of the fields are empty. I remember I had some pages where wrote the code to go...
4
1928
by: tyv | last post by:
Hi, I encountered a bug on a website that I am working on. The enter/ return key function works in certain parts of the website and doesn't work in other parts. I went through all the scripts but I'm not sure why the function does not work for this part. Does anyone have any suggestions/scripts that would work in this case? I am...
0
7746
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8035
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5556
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5257
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3694
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
992
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.