I don't see any possibility to get a subclass of VTable that is connected to the database server
I am not sure I understand this.
When accessing the .table method of a vDatabase, it doesn't matter if it is a local or remote db.
dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")
This instantiates a VTable using the mDatabase instance. The only difference lies in how you instantiated your VDatabase, Using a local method or through VConnection to a remote server. Therefore, all the .table methods are available to both local and remote db's.
For local DB:
dim f as folderitem
f = getfolderitem("localDBname")
dim mDatabase as New VDatabase
mDatabase.open ( f )
dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")
You can now access all the myFancyTable methods on the local DB.
For a Remote Server:
dim f as folderitem
f = getfolderitem("serverDBname")
dim mConnection as New VConnection("server IP address","userName","userPwd")
mConnection,Open
dim mDatabase as New VDatabase(mConnection)
mDatabase.open ( f )
dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")
Same access to .table methods - just using a remote server. I think this is what you were asking.
Through trial and error, I came up with a few pointers when I write VDB solutions. Your mileage may vary.
If it will be a local application, I would recommend the API method for almost everything except very complex and specific searches to the db. API access to a valentina db is brutally fast. But then again, it depends on what you're searching for and what and how many records you will be retrieving. In a single user local app, you have exclusive access to the db and you can leave the connection open and just pick out everything you need piece by piece - and although that sounds inefficient (and technically is), the API method to a local db is beyond BLAZING fast (I think I said that already...). Using the table methods .addRecord(), .updateRecord(), etc is just too easy.
If a client/server app sits on a modern fast Gb ethernet local network, you can still rely on the API method for most operations. There is a small performance hit. Use SQL where needed to increase efficiency. Table methods still work here reasonably well. You can use the API method to lock records by changing a record flag before and after operations. Make a column called "editable" and make it boolean. Just change the value as needed. Make sure that your check for this flag before writes.
If the client/server app sits on a physically remote server, stay with SQL. It's more efficient making usually one call to the server for all your information versus going back and forth. I would use .SQLSELECT(), or .SQLExecute(), etc.. methods in this case.
I would absolutely concern yourself with record locks in any multi-user environment. Not addressing this issue will cause more errors or cause data corruption that will cost you more than you will ever know. VCursor record locks are very easy and ensures your data stays safe. Just lock the server side cursor and destroy it when done. When making writes to the db, use a try/catch block and look for the verror stating the record is locked and let user know. I write my methods to check for a lock, and to make several attempts to write/update (whatever) while giving the user something to look at to distract him/her. If it takes longer than (whatever you determine as a timeout) inform the use and give them the ability to either message the user locking the record (valentina's api notification class works like a champ!) or cancel out of the operation.
When linking records, I can't say enough goodness about binary links. I use them almost exclusively. Makes retrieving data effortless. Although this will make Ruslan and Ivan cringe, you can pull almost limitless fields (and as many record sets as needed) from numerous tables using a pretty simple request:
SELECT fld1, fld2... fld100 FROM tbl1, tbl2, tbl3, tbl4 (etc) WHERE tbl1.RecID = (whatever)
As long as all your tables have their various links, this just works. (Of course, this is over simplified - but you get the point of how easy binary links can be)
When storing documents, I would not typically store them directly in the db. I would rather store them on the server's file system and just store the path variable in the db. When looking for a document, search for its name, or whatever you want and get its file path from the db. Then use Xojo's file methods to get the document. It's much more work to do this, but after time has passed and you now have bazillions of documents - your db may become unwieldy and humongous.
OK - where I was going with this I have no idea... But, you will find VDB a relatively easy and powerful solution. And, to add more fuel to the fire - I am not sure how you are generating your PDFs, but you should take a look at VReports. It's another outstanding tool from the Valentina guys.
Scott