473,708 Members | 2,404 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 3990

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
4189
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 MyBase.ProcessKeyPreview(m) as the last code line while I have used Return MyBase.ProcessKeyEventArgs(m)
6
14550
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 -Ux -Py -Ss -i script3.sql
3
1838
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): - Item Status - Item Status Details
1
1914
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 generally like what I see, but there are a few issues (so far - I am sure others will crop up as...
1
4630
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 act as a server for local web pages, and some of them use a little bit of simple ASP scripting. ...
13
3273
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 breaks on the second call to ReadElementBodyAsXml with the inner xml: </EGDConfigExtension>...
1
11649
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 use 2005 I guess. We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM work with...
18
4603
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 doing it? C'mon, this is C#, we aren't supposed to be programming the GUI. There has to be such...
24
8432
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 on the next field when pressed the Enter button (Tab like), because it didn't go anywhere at all....
4
1940
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 working with javascript, ASP files and html.
0
8697
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9290
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9159
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9061
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
9001
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7925
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5939
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4454
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3151
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.