back end table naming

[Mark Kubicki]

what's the correct format for naming a table which will be linked as a back
end?

btblTableName
betTableName
be_tblTableName
tbl_beTableName

thanks in advance,
mark

 

[tina]

the correct format for a tablename (or any object you name in Access) is "no
spaces, no special characters other than underscore ( _ ), and *consistency*
in your naming convention". from your post, you have the first two down pat
already. so pick a convention that makes sense to you, and apply it
*consistently* to every table object you name in the database. i personally
don't have a special designation for "backend" tables, because virtually all
the tables i build in a database will be split to a backend and linked to a
separate frontend, as is the standard for multi-user databases in Access.

hth

 

[(PeteCresswell)]

Per Mark Kubicki:

what's the correct format for naming a table which will be linked as a back
end?

I started with the Lysinski & Reddick conventions and wound up
with:
----------------------------------------------------------------
tblWhatever = "Active" table within the application as indicated
by expected volatility

tlkpWhatever = Low-volatility "lookup" tables.

zmtblWhatever = FE-resident "model" tables that I use to create
on-the-fly work tables in a temp db.

zstblWhatever = Tables that are used by the application, but
which wouldn't make any sense to the user
e.g. zstblConnectionInfo and zstblRecordNumbers
----------------------------------------------------------------

I don't have a prefix that specifically indicates FE-resident
tables, unless one counts "zmtbl..." bc I never have any tables
except model tables, a "Program Changes" table, and maybe a
zstblReportNames table in the front end.

Using different prefixes is useful bc somebody can get an idea of
the core tables by glancing at a list of table names sorted
alphabetically.

I'll use the occasional underscore; but not embedded spaces or
special characters.

The one exception is an FE-resident table named:
"------------------- Program Changes -----------------", which
isn't so much a "table" in the database sense but more of a
notepad for my to-do list and a record of what changes are in
what versions. It could just as well be a basic module full of
remarks or an Excel spreadsheet... or even an MS Word doc.

For consistency's sake, it probably sb "zstblProgramChanges", but
the dashes make it float to the top of the list, identify it as
something out-of-the-ordinary, and haven't come back to haunt me
(yet....).

 

[Tony Toews [MVP]]

what's the correct format for naming a table which will be linked as a back
end?

btblTableName
betTableName
be_tblTableName
tbl_beTableName

None of the above. I see no reason to use any prefix based naming
conventions with the possible exception of VBA variables. Indeed in
large MDBs with hundreds of objects such can really slow you down. It
is very nice to be at the database container window, hit the p on the
keyboard and be instantly at the first Purchase Order object.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

Now I should add that my conventions have a few adherents but many
disagree with me. That's fine with me although I'm right. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

I think that you'd be much better off keeping all your tables and
queries in a single place- on the database server, where they belong.

it's silly to have different prefix for frontend and backend-- crap
like that is just an unnecessary PITA
it's silly to have different field names with IH prefix-- crap like
that is just an unnecessary PITA

frontend and backend isn't necessary, it's just a hassle.
And Tony, the fag that he is-- thinks that it's beneficial to use a
3rd, or even a 4th database as a temp database.

Now that's just stupid, don't listen to that stupid fucking canadian.

keep all your tables short, and of course you should name your tables
TBL.
With SQL Server-- if you used views, you would be quite glad that you
stuck to a convention (with a real mans database, it's easy to build
'virtual tables' that act just like tables).

In Jet, queries on top of queries frequently crap out with 'the
parameter is incorrect'.

Microsoft has demonstrated for the past decade that Jet has no future.
Windows7 is moving to a SQL Server native format (WinFS) and it is
laughable to use Jet on top of SQL Server.

SQL Server native is always fastest / best / simplest.

Access Data Projects. The so-called professionals out here don't use
them because they don't have enough intelligence to learn the 'worlds
most popular database'-- SQL Server.

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

None of the above. I see no reason to use any prefix based naming
conventions with the possible exception of VBA variables. Indeed in
large MDBs with hundreds of objects such can really slow you down. It
is very nice to be at the database container window, hit the p on the
keyboard and be instantly at the first Purchase Order object.

What about MS Access UI lists where queries and tables are mixed?

 

[a a r o n . k e m p f]

Yeah, I agree Tony!

Slow you down?? Jet doesn't really support hundreds of objects, SQL
Server does though!

 

[Tony Toews [MVP]]

What about MS Access UI lists where queries and tables are mixed?

So what? There's seldom any practical difference between a query and
a table when it comes to most places in Access where there's a UI
list.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Slow you down?? Jet doesn't really support hundreds of objects, SQL
Server does though!

Rubbish. My largest app had 160 tables, 1200 queries, 450 forms, 350
reports and 70K lines of code. Worked well.

We should've upsized the BE to SQL Server. Indeed I was halfway
through the upsizing when a PHB (Dilbert's Pointy Haired Boss)
canceled it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

You're rubbish.

Access doesnt' handle 25mb of data.
Access just doesn't have filters to filter for object names-- it's not
just designed for this.

70k lines of code, to what.. right connection strings, relink tables?

What a fucking waste of time, dude.

Learn a real database you faggot stalker

 

[a a r o n . k e m p f]

and sorry that you suck at upgrading.
that's what you're admitting-- is that you don't know WTF you're
talking about.

you don't know the 'best half of MS Access = ADP'.
And yet you sit there and attack everything I say?

Maybe you should take some classes on SQL Server, and learn how to
spell SQL... before you (incorrectly) run around telling everyone that
SQL Server sucks.

-Aaron

 

[Tony Toews [MVP]]

before you (incorrectly) run around telling everyone that
SQL Server sucks.

I've never said that SQL Server sucks. It's a darned good product.
However ADPs are generally a dead end.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arvin Meyer [MVP]]

message
and sorry that you suck at upgrading.
that's what you're admitting-- is that you don't know WTF you're
talking about.

you don't know the 'best half of MS Access = ADP'.
And yet you sit there and attack everything I say?

Maybe you should take some classes on SQL Server, and learn how to
spell SQL... before you (incorrectly) run around telling everyone that
SQL Server sucks.

==================================================

Perhaps you need to learn something about what you are doing. Both Access
MDBs and ADPs support exactly the same number of objects: 32,768

 

[a a r o n . k e m p f]

how about the # of columns?

how about having 32k objects PER DATABASE

 

[a a r o n . k e m p f]

you can't fire queries in an external jet db

 

[Tony Toews [MVP]]

you can't fire queries in an external jet db

Big deal. I can run queries from one MDB against another MDB. Check
out the IN clause in Access help.

IN Clause
Identifies tables in any external database to which the Microsoft Jet
database engine can connect, such as a dBASE or Paradox database or an
external Microsoft® Jet database.

Syntax
To identify a destination table:

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

So what? There's seldom any practical difference between a query
and a table when it comes to most places in Access where there's a
UI list.

I not infrequently have as many as three tables/queries that have
the same base name and a different 3-letter prefix. I find this
useful because of the way I organize my tables and queries.

While there may be little difference between a query or table as a
data source when using a form or report wizard, there surely *is* a
difference between two objects of similar name and function. Say you
had a query that aliased some fields and had some calculated fields
for a single base table. To me, the easiest thing to do is to have
tblMyTable and qryMyTable. But you'd have to put that information in
the verbose part of the name itself.

Or, another example:

In SQL Server, I often have the table linked, but also will link a
view on the same table that does certain things (in one legacy app,
the base tables names have been updated to be correct, but the app
hasn't been updated yet, so I use a view with the fields aliased for
compatibility with the old application objects). One I call
tblMyTable and the other vblMyTable. Yes, sort of silly, but it lets
me know that one is the view of the table and the other is the table
itself.

You would probably name it something like MyTable and MyTableView.
That sorts the two together, but I almost never need them sorted
together -- I find it more useful to keep object types separate.

I'm sure you're happy with your approach and would hate mind. And
vice versa.

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m"


I've never said that SQL Server sucks. It's a darned good
product. However ADPs are generally a dead end.

And Jet *doesn't* suck, despite Aaron's repeated ineffectual and
ill-informed claims to the contrary.

 

[a a r o n . k e m p f]

Jet isn't good enough to Suck.

It's obsolelte, and it has been for a decade.
Same thing with DAO.
Same thing with Replication.
Same thing with ULS.

That's why they took out DAO and Replication and ULS and that's why it
STILL doesn't make it easy to edit the connection strings on SQL
Passthroughs-- because they just don't give a shit about MS Access.

 

[a a r o n . k e m p f]

rE:
Say you
had a query that aliased some fields and had some calculated fields
for a single base table. To me, the easiest thing to do is to have
tblMyTable and qryMyTable. But you'd have to put that information in
the verbose part of the name itself.




mAYBE IF YOUR DATABASE OF CHOICE ALLOWED YOU TO EDIT A FIELD WITHOUT
RENAMING IT-- THEN MAYBE YOU WOULDN'T HAVE TO WORRY ABOUT THIS HASSLE


SELECT ISNULL(ManagerID, EmployeeID) AS ManagerID
FROM Employees

Maybe if Jet didn't make you give that field another alias-- you know
how it won't let you call this field manager?

In other words-- if you want to trim a field, or uppercase it-- it's
got to get a different field name (meaning that you've got to change
all your queries).
Furthermore, Jet tables don't support computed / calculated columns
(in a table, not in a query).

So Jet's pretty much worthless to me.

Add to that the fact that it crashes, and the fact that it takes an
extra 3 seconds to run _ANY_ query-- and anyone with a clue would STFU
and learn a man-sized database.

-Aaron