A failsafe opening of a table without ever getting "Alias name is already in use" or "File is in use" errors is this:
Code:
USE table.dbf ALIAS table IN SELECT("table") AGAIN
To understand this, you first have to know the USE command is both opening and closing the file related to a workarea (no matter if dbf file or .tmp files of cursors or views, which sometimes are only virtual). the simplest USE command, only USE, closes what is open in the current workarea, this contributes to the fact of Foxpro always having shorthand notations or parameterizations, if you only want to act on the current workarea, which also makes code shorter and readable, though I prefer more verbose code for sake of being explicit about what to do, the above USE command is almost as explicit, as it can be, I only leave out a few clauses like SHARED/EXCLUSIVE/ADMIN/NODATA/CONNSTRING, so this also is not the extreme maximum possible USE command. If you don't know any one of these clauses or options, please update your knowledge of the USE command and read its help topic, too.
To avoid double alias use, IN SELECT("table") ensures, that if the table.dbf file already is open, this command reopens it in exactly the same workarea it is already opened in. So, in that case, the USE both closes and reopens the table.dbf file, but there will be no "Alias name is already in use" error. What if no workarea has the alias name "table"? Then SELECT("table") is 0, and what does that mean? Bingo, if you paid attention to what I said earlier, this opens the table.dbf in a free yet unused workarea, which you always get with IN 0. Fine isn't it?
Now, why AGAIN? If some other developers code, for example, Microsofts referential integrity triggers, opens a table with another alias name than its automatic alias name, its file name, then you still would be getting "File is in use" error, which you don't want. AGAIN tells VFP you don't want that error in case the file is already open, you use the DBF again in that case and you don't care if other code opened table.dbf with an alternative alias name, which is best practice to do. So just notice: Even if you program with best practices in mind, that should include programming defensive against other people not doing so.
Side note: In the case of triggers, it is forgivable, as necessary to have the same table open twice for being able to move record pointer to other records even in case row buffering is used. As referential integrity is all about checks of correct referencing of data with foreign keys before saving something, it would be dumb to do that in the workarea the developer uses and might only row buffer, because that would "spill the milk".
Now I explained IN SELECT("table") AGAIN, but why ALIAS table? Actually, this is just being verbose, it is the alias name the workarea gets when you open table.dbf, but let's just think about the unusual case your dbf file name has spaces in it or begins with a digit, which both are not allowed for workarea alias names, then you may want to control the alias name instead of letting VFP decide automatically to convert spaces to underlines, you may opt for having aliases without underlines.
And finally notice AGAIN is not harmful, if the file was not already used, VFP doesn't test, if you really use the table AGAIN now and also while the ALIAS name specification may seem unnecessary it ensures the SELECT("table") uses that exactly same alias name. It becomes more apparent when generalizing the code.
So let's put this in a more general form of a function with two parameters DBF and ALIAS, then it becomes:
Code:
Procedure OpenDBF()
Lparameters tcDBF, tcAlias
tcAlias = Chrtran(Evl(tcAlias,JustStem(tcDBF))," .!,-+","_")
If Isdigit(tcAlias)
tcAlias = "_"+tcAlias
Endif
Use (tcDBF) Alias (tcAlias) In Select(tcAlias) Again
SELECT (tcAlias)
Endproc
Now I leave it as open homework, why the first few lines treat the tcAlias value this way. Just notice, if it is passed in, it is used as given by the caller, otherwise it just automatically creates a valid alias name, also if tcDBF is not a (fully qualified with path) dbf file name but just the table name of the currently selected database. You may even try to pass in "dbname!tablename".
I bet now reading all this it confuses you and seems complicated. You think numbers then are still simpler than alias names, but using a number is making a definition you don't ever need when you can be more verbose. When you talk with your customers, do you address them with their name or with their customer number? I know dbfs are not people, but the point is not being polite to your dbf files, the point is being polite with your next developer and with yourself coming back to your own code, maybe daily. Also, numbers don't solve the file or alias in use errors. You gave dbfs a name for knowing what data they contain, VFP now allows you to work with that same name, and you still want to work with numbers instead? Is that sane? Do you also work with RAM addresses instead of variable names?
Finally, I most often write USE commands with the caution of such a procedure in mind, but not really implementing and calling that procedure. It's just an educational definition, over the top used in production. But I often enough write it in the specific form of [tt]USE sometable IN SELECT("sometable") AGAIN[/tt] without the alias clause as the specific sometable name will cause that alias automatically anyway and there is no reason to be more verbose about it. Of course, a procedure definition has to be more pedantic about it to work - sometimes against all odds or developer quirks.
You can use it, for example:
Code:
CLOSE TABLES ALL
CLOSE DATABASES ALL
OPEN DATABASE (ADDBS(_samples)+"NORTHWIND\NORTHWIND.DBC")
OpenDBF("northwind!customers")
? ALIAS(), AUSED(laDummy) && telling how many workareas are used
OpenDBF("customers")
? ALIAS(), AUSED(laDummy)
OpenDBF("northwind!customers","customers")
? ALIAS(), AUSED(laDummy)
There are a few minor issues with the procedure in comparison with only opening a dbf when it isn't open already via [tt]IF NOT USED("alias")[/tt]: If the table is already open, it will be closed and opened, which stores buffered changes, moves record pointer back to the top and puts buffering back to a default (normally no buffering), removes all relations starting or ending in the workarea, but on the other side USED("alias") can't test a DBF file being open, it just looks for the already used alias name and so the cases of a table being used with an alternative alias name are not covered by this simple check, which gives the OpenDBF procedure an advantage over the simpler only USE if NOT USED() approach, if your main concern is error free code execution. It doesn't unbind you from knowing what you want anyway. I for example rather avoid relations and combine data with SQL queries, mostly from MSSQL Server anyway, so all techniques specific to DBFs ahve limited use. And in the case of buffered changes existing and coming to a point you (re)open a dbf, that's questionable code, overall.
The last sample case of using the syntax with "!" might make it a good exercise to extend the code to automatically not just remove the "!" but in case tcDBF is passed in with that nomenclature, you just take the part after the "!" as the alias name. I thought it's nice the dbc name then becomes a prefix of the alias name, so you might also take that as is.
Last not least, the opposite CloseDBF() function would be harder to write if you really take DBF serious and want to close all workareas having a certain DBF file open, but taken from the perspective of closing an ALIAS, it's simply USE IN SELECT("alias"), as Mike already said. There is no procedure necessary for that, you might also leave it to the destroy of a form and its date session to automatically close all its open workareas. There is no limit, no memory or file handle limit, not today, that was a thing in the past, but we're in more modern times where RAM is no limit anymore.
Workareas don't cost the dbf size in memory bytes, that cost only emerges with fetching data into the cache memory and also remains when closing DBFs, that's never fully in your hand anyway. But work areas are really not the data in them, they hold their alias name (a few bytes) they have many attributes associated with them, though they are not objects, but all the CURSORSETPROP properties belong to the workarea, also FILTER, KEY, ORDER, the record pointer and maybe some more I forget to mention all belong to workarea and not DBF in it, they just affect the DBF and what you see in it. People often forget they work with the workareas and only indirectly with the DBF files. the record pointer, for example, feels like a file position pointer of low-level file functions, but if the record pointer really would be associated with the dbf file, it would not be possible to have a separate pointer when having the same DBF file open twice.
OKAY, and finally this doesn't care about EXCLUSIVE or SHARED, so it still can fail on opening a DBF, which another user has open exclusive, but it covers the nastiest cases of file/alias already used errors.
Bye, Olaf.
![[hammer]](/data/assets/smilies/hammer.gif "[hammer] [hammer]")
