473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to earmark certain tables, differentiating them from others?

MLH
Have a database with 100 or more tables.
Would like to set some property to flag those
whose data is dynamic from those tables
containing essentially static reference data.

What's recommended method for this? Am
looking for a method other than some unique
string embedded in table name.
Nov 12 '06 #1
14 1294
1. Use the Database Window's Description field, which is accessible by
right-clicking on the table name and choosing Properties.

2. Do what you don't want to do, and add "some unique
string embedded in table name", like "lu_", as in, "lu_state_code"

--
Darryl Kerkeslager

"MLH" <CR**@NorthState.netwrote
Have a database with 100 or more tables.
Would like to set some property to flag those
whose data is dynamic from those tables
containing essentially static reference data.

Nov 12 '06 #2
MLH wrote:
What's recommended method for this? Am
looking for a method other than some unique
string embedded in table name.
Hon, the recommended method *is* to use part of the table's name, like
Bldg_Lkup or Bldg_Local or Bldg_Lcl, for the front end tables. You can
either use a utility to change the names throughout the application or you
can add a table property to each of the tables and read that property in code
to determine if it's special.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

Nov 12 '06 #3
MLH
Am having trouble reading the Description property
after setting it as you recommended. I do like your
recommendation, BTW. Here's the code I tried to
use to read the description. Of course, it is not
working...

Sub TableDefX()

Dim MyDB As Database
Dim MyTDF As TableDef
Set MyDB = CurrentDb

With MyDB
Debug.Print .TableDefs.Count & _
" TableDefs in " & .Name

' Enumerate TableDefs collection.
For Each MyTDF In .TableDefs
Debug.Print " " & MyTDF.Name & " ==" &
MyTDF.Properties.Description
Next MyTDF
End With

End Sub

Nov 14 '06 #4
MLH
I also tried this code to write table Description
values - having trouble with it as well...

Sub SetDescrips()
On Error GoTo Err_EditTableDescriptions

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim TableNameText As String, i As Byte
Set db = CurrentDb

For i = 0 To UBound(ExporTables, 1)
TableNameText = ExporTables(i)
Debug.Print TableNameText
Set tbl = db.TableDefs(TableNameText)
Set prp = tbl.Properties("description")
prp!Description = "ExportTable"
Next i

Exit_EditTableDescriptions:
db.Close
Exit Sub

Err_EditTableDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditTableDescriptions
End If

End Sub

ExporTables() is an array of table names.
Nov 14 '06 #5
The description is only a property in the Database Window. AFAIK, it cannot
be accessed via code. It does not exist outside Access, so cannot be seen
from DAO or ADO.

--
Darryl Kerkeslager
"MLH" <CR**@NorthState.netwrote
>I also tried this code to write table Description
values - having trouble with it as well...

Nov 14 '06 #6
MLH
Ahaaa. Well, that would explain a lot.

I found code on this NG that reads the
property in VBA. Are you saying it cannot
be written?
Nov 14 '06 #7
"MLH" <CR**@NorthState.netwrote
I found code on this NG that reads the
property in VBA. Are you saying it cannot
be written?
"Cannot" is such a strong word ... so unequivocal.

Not definitively, "no", just as far as I know, "no".

--
Darryl Kerkeslager


Nov 14 '06 #8
"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:PI******************************@comcast.com:
The description is only a property in the Database Window. AFAIK,
it cannot be accessed via code. It does not exist outside Access,
so cannot be seen from DAO or ADO.
Are you sure it can't be accessed through the properties collection
of the tabledef? Yes, that's correct -- I just tested it and it's
the 13th item in the properties collection. It won't always be the
13th item, but it should always be in the properties collection *if*
it has been set.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 14 '06 #9
"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:za******************************@comcast.com:
"MLH" <CR**@NorthState.netwrote
>I found code on this NG that reads the
property in VBA. Are you saying it cannot
be written?

"Cannot" is such a strong word ... so unequivocal.

Not definitively, "no", just as far as I know, "no".
The properties collection gives you access to it. That's the basica
way that all non-Jet properties are exposed via DAO.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 14 '06 #10
Darryl Kerkeslager wrote:
"MLH" <CR**@NorthState.netwrote
I found code on this NG that reads the
property in VBA. Are you saying it cannot
be written?

"Cannot" is such a strong word ... so unequivocal.

Not definitively, "no", just as far as I know, "no".
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaColum ns,
Array(Empty, Empty, "Table1"))
With r
While Not .EOF
Debug.Print .Fields("COLUMN_NAME").Value, .Fields("Description").Value
..MoveNext
Wend
End With

works for access >= 2k

I suppose one could create an independent ADO connection and get the
same info for <=97 but I have not checked that.

There is much more info available in other columns.

ADO is so bad. It does everything, smoothly, cleanly, quickly.

But Brutus said it was amitious, and Brutus is an honourable man?

Nov 14 '06 #11
RE: David's and Lyle's responses

That's why I wouldn't say "cannot."

Someone always seems to know how ...

--
Darryl Kerkeslager
Nov 14 '06 #12

You can add a custom Property to your tables, and set this to reflect
the nature of the data.

For example, running the following in the Immediate Window:

gfAddCustomTableProp("MyTable", "DataClass", "Static")

would add a new property called 'DataClass' and assign it as Static (or
Dynamic, as you require).

The code for the above function is:

' ========= Code Start ==============
Public Function gfAddCustomTableProp%(TableName$, PropName$, PropValue$)
On Error GoTo err_gfAddCustomTableProp
Dim db As Database: Set db = CurrentDb
Dim tdf As TableDef
Dim prp As Property
Dim PropExists%

Set tdf = db.TableDefs(TableName)
Debug.Print tdf.Name & " *********"

For Each prp In tdf.Properties
If prp.Name = PropName Then
PropExists = True
Debug.Print "Property Exists: " & prp.Name, prp.Value
Exit For
End If
Next prp

If Not PropExists Then
Set prp = tdf.CreateProperty(PropName, dbText, PropValue)
tdf.Properties.Append prp

Set prp = tdf.Properties(PropName)
Debug.Print "Property Created: " & prp.Name, prp.Value
End If
Debug.Print ""

gfAddCustomTableProp = True

exit_gfAddCustomTableProp:
Set db = Nothing: Set tdf = Nothing: Set prp = Nothing
Exit Function
err_gfAddCustomTableProp:
MsgBox Err & ": " & Err.Description
Resume exit_gfAddCustomTableProp
End Function
' ========= Code End ==============

HTH.
*** Sent via Developersdex http://www.developersdex.com ***
Nov 14 '06 #13
"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:pv******************************@comcast.com:
RE: David's and Lyle's responses

That's why I wouldn't say "cannot."

Someone always seems to know how ...
Why answer if all you have to say is "I really don't know" -- you
could have tried it and found out the answer yourself.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 15 '06 #14
You're exactly right. I was on the way to work, and penned a quick answer.
On the way to work, I thought again, that there probably was ... but you're
right, maybe I should have just waited.

--
Darryl Kerkeslager
"David W. Fenton" <XX*******@dfenton.com.invalidwrote
>
Why answer if all you have to say is "I really don't know" -- you
could have tried it and found out the answer yourself.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Nov 15 '06 #15

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

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
8
by: Greg | last post by:
Hello, I've to manage many 'table' (having same scheme) on the same server. And I ask myself what could be the best to do (and if you know, why) : Creating as many database (the name would...
81
by: sinister | last post by:
I wanted to spiff up my overly spartan homepage, and started using some CSS templates I found on a couple of weblogs. It looks fine in my browser (IE 6.0), but it doesn't print right. I tested...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
1
by: John | last post by:
Hi I need to perform two queries in sequence on two identical access tables. The source table is coming from a dataset returned by a web method and the destination table is from a local access...
11
by: dskillingstad | last post by:
I've been struggling with this problem for some time and have tried multiple solutions with no luck. Let me start with, I'm a novice at Access and I'm not looking for someones help to design my...
8
by: Pietro | last post by:
Hi, i would like to know if there is a way to differentiate between requests, someting like a number that is unique only in the execution of an individual request. Thank you Pietro
6
by: J Ames | last post by:
I have an ASP.NET (VB) form that has two drop downs, a horizontal rule and a button. The button invokes a stored procedure and several tables are created on the page with data populated. I want...
59
by: phil-news-nospam | last post by:
In followups by Brian O'Connor (ironcorona) to other posts, he repeats the idea that using tables in CSS is not something that should be done because IE doesn't support it. Of course I'm not happy...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.