Using multiple databases in FoxPro

[Samalia]

I'm trying to create an application that allows the user to have multiple companies each with their own database. The initial form the user sees allows them to pick which copany they want to work with. The form runs off a table with its own database, the table contains the company names and where each respective database is located. After the user has selected a company, a form is opened where they can create new or select old purchase order records based on the company. I'm having trouble getting foxpro to switch between databases using the same form. Is this even possible?

Tnx,
Taeya

 

[KenMurphy]

You don't need to change the name of the database - just change the location.

lcDatabasePath = MyCompanyTable.cDBPath
CLOSE TABLES ALL
CLOSE DATABASES ALL
SET PATH TO &lcDatabasePath
OPEN DATABASE MyDBC

Assume that your Data directory now has a series of sub-directories (one for each company.) You store these paths in MyCompanyTable.DBPath. For example, if record 1 has a cCompanyName of "Ajax Corp" and cDBPath of "\\Server\Share\Data\AjaxCorp"
when you set the path to &lcDatabasePath, it will set it to \\Server\Share\Data\AjaxCorp and the database opened will be Data\AjaxCorp\MyDBC.DBC. If record 2 has "Acme Corp" and it's directory is "\\Server\Share\Data\AcmeCorp" it will open "\\Server\Share\Data\AcmeCorp\MyDBC.DBC" and you will not have to worry about renaming the database in the form.

As an added bonus, you could use GENDBC to create a database generator for when your users want to generate a new company database.

Ken

 

[bborissov]

Yes. I do it all time )
Just put your development DataBase in folder where you sure it doesn't exists in user side. When creating a new company database put it in separate folder. then play with PATH. What I do:
My development database is placed in b_Data folder, on client side when client creates a new company database I placed it in DATA1...DATAx folders. That just switch paths:
SET PATH TO DATA1 (or DATA2..DATAx) Everything is perfect.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Samalia]

thanks

 

[Samalia]

I'm still having trouble making it work, is there a trick to it when creating the form? Do I need to create fields that are linked to a field in my table but not have any tables in the data environment? I'm using all the code that i've been given but the form still goes back to the original database and doesnt use the one that I set the path to and open.

Taeya

 

[vaxman9]

Hiyas Taeya,

I added a method to my baseform class called POINTTABLES that I call in the BeforeOpenTables of the Dataenvironment of the app form. I pass the folder location and optionally the DBC name. The method will check each cursor object of the dataenvironment and will change the path of the cursor object to the path passed in as pActivePath. If you pass the DBC name, only the cursor objects of the DBC will be adjusted.

(I got this idea years ago not sure, but I think I got the idea from this forum and I apologize for not remembering the original author)

hth,
msc

Parameter pActivePath, pDBCName

*-- Declarations
LOCAL lnCursorIndex     && Index pointer to next item to add to cursor list
LOCAL lcMemberList[1]   && Temporary array of all members in the data environment
LOCAL lnCnt             && Loop counter
LOCAL lcExec            && Command to execute
local lcCursor			
local lcDBCName         && The DBC name
local llDoit
local lcActivePath
*---

lcActivePath = addbs(pActivePath)	&& Make sure there is a \ on base path
*
*	Addition Parameters?
*
if pCount() = 1
   pDBCName = ""
endif

pDBCName = JUSTFNAME(allt(upper(pDBCName)))
IF EMPTY(JUSTEXT(pDBCName))
   pDBCName = pDBCNAME+".DBC"
ENDIF

*!*			*-- Initialization
lnCursorIndex = 1

*-- Build an internal list of all cursors, aliases, and databases in the 
*-- current data session's data environment

=AMEMBERS(lcMemberList, THISFORM.DataEnvironment, 2)

*
IF TYPE("lcMemberList[1]") == "C"
   *
   * Find all "cursor" objects
   *
   FOR lnCnt = 1 TO ALEN(lcMemberList, 1)
      lcCursor = "THISFORM.DataEnvironment." + lcMemberList[lnCnt]
      IF &lcCursor..BaseClass == "Cursor"
         *
         * What is the name of the DBC this Cursor belongs to?
         *
         lcDBCName = JustFNAME(allt(upper(&lcCursor..Database)))
         *
         * Do I Need to change this cursor's path?
         *
         llDoit = iif(empty( pDBCName), .t., (lcDBCName = pDBCName))
         if llDoit 
            *
            *  Yep, change it ...
            *
            lcExec = "THISFORM.DataEnvironment." + ;
                        lcMemberList[lnCnt] + ;
                        ".Database = '" + lcActivePath +  lcDBCName + "'"
            &lcExec
         endif
      endif
   NEXT 
ENDIF

 

[Samalia]

I've tried the new code as well, but when I run the form at the bottom of my screen I get a message that says MASTER INDEX, then points at the wrong database and the form loads with the fields all containing data from the wrong database. I've never heard of a master index before, and was wondering if that could be what's stopping my form from switching databases?

 

[vaxman9]

Samalia,

I apologize, I mistyped 1 thing:
The IF should have a !

pDBCName = JUSTFNAME(allt(upper(pDBCName)))
IF ! EMPTY(JUSTEXT(pDBCName))
   pDBCName = pDBCNAME+".DBC"
ENDIF

Also, this code should be in the baseclass the form is based on.

 

[vaxman9]

Samalia,

Please disregard my correction, my orginal post contains the working code - I apologize for the confusion. (sorry not enough coffee, late in the day =P).

Are the two DBCs exactly the same? Is it possible that develepment one has a tag that the production one is missing?

I would also use the debugger to verify that the paths are being change successfully.

Again, I apologize for the confusion
msc

 

[pthalacker]

Samalia,

Is there anything in the form's DataEnvironment? You probably want to make sure that is empty.

pamela

 

[KenWMurphy]

If you are using tables in your D/E, this may be the problem. Normally, the relative path "AppDir\Data" remains constant. For example,

\\Server\Production\AppDir\Data VS
\\Server\Development\AppDir\Data

But in this case you are changing the relative path:

\\Server\Production\AppDir\Company1Data
\\Server\Production\AppDir\Company2Data

You have a couple of options here:

1: Remove all tables from the D/E and then open your tables programmatically in the .OpenTables() method of the D/E.

SET PATH TO &Path
USE Table1 IN 0 TAG Tagname SHARED
...

2: You can create a custom data envronment class and use this instead of the existing data environment.

Ken