Create Table - With Alias

Posted on 2014-01-08
Medium Priority
Last Modified: 2014-01-13
OK, so I'm creating a table and making the name dymanic...bu when it shows in the Data Session Window under Aliases is shows as "H"...why and how can I assign it a name?

PUBLIC TableName
TableName = PADL(ALLTRIM(STR(client)),4,"0") + "_Items"

So my table name is for example 0101_Items

CREATE TABLE &TableName (field1, field2, field3...)

But as I mentioned above in the Data Session Window under Aliases it shows as "H"???
Question by:smithmrk
LVL 30

Accepted Solution

Olaf Doschke earned 1600 total points
ID: 39767276
Table names names are not allowed to begin with a number. Only letters and undersore.
File names don't have that restriction, but workarea names.

I would have expected an error, but obviously VFP handles this by giving the workarea the letter corresponding to the workarea number.

Look at your harddrive, the DBF will be named as you wanted, but opening it the workare will have a single letter name.

Don't confuse the workarea name with the table name, nor the file name. This three names can differ:

Create Database nametest
Create Table filename NAME tablename (field1 I)
Use && close the table
* reopen it in three ways
* specify databasename!tablename
Use nametest!tablename IN 0 Alias workareaname1
* specify dbf filename
Use filename In 0 AGAIN Alias workareaname2
* specify just the tablename, it will be looked for in the currently set database
Use tablename IN 0 AGAIN Alias workareaname3

Open in new window

This is not to demonstrate the effect of a file name beginning with a digit, this is showing how many ways there are to address a table and workarea. People often confuse the workarea name with the table name, as it normally is the same, but some rules apply to workarea names (eg not using an alias=workarea name twice) but you can have as many files with the same name (in different directories) all belonging to the same DBC, as long as they are added with unique tablenames.

You have to know the details in VFP to avoid the pitfalls.

Bye, Olaf.
LVL 30

Expert Comment

by:Olaf Doschke
ID: 39767278
By the way: It's recommended to only specify a differing alias name, if you need to use a table twice in the same datasession, to have two record pointers or use the two aliases as controlsource to feed two comboboxes, for example. Therefore you normally also don't use file names starting with a digit, because while that'spossible, you'dalways need to open those files with ALIAS. It's bad for writing generic code relying on workareas getting the same name as the file. For the same reason avoid spaces in the dbf file name.

You know better than me, how you would like to alter your naming of the DBFs, perhaps add the "items_" in front or just add an underscore or add a "client_" in front.

Bye, Olaf.
LVL 12

Assisted Solution

jrbbldr earned 200 total points
ID: 39768115
I think the bigger issue here is the confusion between the term "TableName" and "Alias".

Your code shows you are allowing users to create a TableName (albeit possibly violating the name convention rules), not necessarily an Alias.

True, if you do not specifically assign a different Alias name to a Table, the Alias will default to the TableName.  However note that spaces within the TableName will be changed to under-scores within the defaulted Alias.

But most often when differing Aliases are required that is done via an option of the USE command and does not need to be directly associated with the TableName.

Example of specifically assigning (within the code) a different Alias to a Data Table:
[b]USE MyTable IN 0 [u]ALIAS CustNames[/u][/b] 

Open in new window

If you want your users to create their own Data TableNames then put in code to prevent them from violating the naming convention.

Then, within your code, you can utilize these individually named Data Tables but assign them to a common Alias for use if that is so desired.

Good Luck
LVL 43

Assisted Solution

pcelba earned 200 total points
ID: 39773152
To create a table having an alias assigned is very easy. You just have to create database:

TableName = PADL(ALLTRIM(STR(client)),4,"0") + "_Items"
CREATE TABLE &TableName  NAME YourNewAlias  (field1, field2, field3...)

Above code just shows "Everything is possible in FoxPro" but this solution is not the one I would recommend to use... So please forget it and read above posts :-)

Author Closing Comment

ID: 39776600
Thanks to everyone!
I ended up just moving my client_id field to the end as Olaf said tables can not start with a number so I just moved my number to the other end and so now I have:

TableName = "Items_" + PADL(ALLTRIM(STR(client)),4,"0")

This works for me!
Thanks again to everyone,


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Most folks would know the basics of how Dropbox works, so that’s not the purpose of this article. Security is what it’s all about, so here I’ll share how I choose to secure my Dropbox Account and the Data it contains.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question