Discussion:
Switching Database
(too old to reply)
Andy Trezise
2004-11-08 14:01:36 UTC
Permalink
I have an age old problem when trying to switch from one database (and the
tables contained within it) to another during my application (VFP8). I just
want the user to be able to switch from a LIVE database to an ARCHIVE
database.

1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.

2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns the
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to call
a routine as follows:

Function Switch_DB()

lArchive = !lArchive

IF lArchive

cDB = C:\DATAFILES\MYDB.DBC

ELSE

cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC

ENDIF

CLOSE ALL DATABASE

OPEN DATABASE &cDB

SET DATABASE TO &cDB

RETURN
Olaf Doschke
2004-11-08 15:54:25 UTC
Permalink
Hi Andy,
Post by Andy Trezise
CLOSE ALL DATABASE
Maybe it's just as easy as changing that line to
Close Databases all

Bye, Olaf.
Andy Trezise
2004-11-09 10:57:24 UTC
Permalink
Sorry a typo error...my code actually says

CLOSE DATABASES ALL
Post by Olaf Doschke
Hi Andy,
Post by Andy Trezise
CLOSE ALL DATABASE
Maybe it's just as easy as changing that line to
Close Databases all
Bye, Olaf.
Stefan Wuebbe
2004-11-08 17:08:31 UTC
Permalink
Post by Andy Trezise
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.
Right, there are paths stored in some of the DataEnvironment memebers'
properties unfortunately.
You can modify them either at design time / before build.
Or at runtime in yourForm.DE.BeforeOpenTables()

In addition to what Olaf said, Close Databases All might fail to
close them actually, as long as there are any data sessions referring
to the DBC you want to close. IOW, you'd need to close all forms etc.
beforehand.


hth
-Stefan
Post by Andy Trezise
I have an age old problem when trying to switch from one database (and the
tables contained within it) to another during my application (VFP8). I just
want the user to be able to switch from a LIVE database to an ARCHIVE
database.
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.
2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns the
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to call
Function Switch_DB()
lArchive = !lArchive
IF lArchive
cDB = C:\DATAFILES\MYDB.DBC
ELSE
cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC
ENDIF
CLOSE ALL DATABASE
OPEN DATABASE &cDB
SET DATABASE TO &cDB
RETURN
Andy Trezise
2004-11-09 11:15:29 UTC
Permalink
Thanks for your help...

I make sure that I don't have any forms open when I close the database
(CLOSE DATABASES ALL). The confusing thing is that the application is
opening a foreign database and tables when it starts up for the first time
OK (i.e. in a different place to the development database). It's not until I
close the database and try and reopen the archive that the problem shows up.
If I use DBC() it shows the correct database but still opens the tables from
the previously opened database.
Post by Stefan Wuebbe
Post by Andy Trezise
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.
Right, there are paths stored in some of the DataEnvironment memebers'
properties unfortunately.
You can modify them either at design time / before build.
Or at runtime in yourForm.DE.BeforeOpenTables()
In addition to what Olaf said, Close Databases All might fail to
close them actually, as long as there are any data sessions referring
to the DBC you want to close. IOW, you'd need to close all forms etc.
beforehand.
hth
-Stefan
Post by Andy Trezise
I have an age old problem when trying to switch from one database (and the
tables contained within it) to another during my application (VFP8). I just
want the user to be able to switch from a LIVE database to an ARCHIVE
database.
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.
2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns the
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to call
Function Switch_DB()
lArchive = !lArchive
IF lArchive
cDB = C:\DATAFILES\MYDB.DBC
ELSE
cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC
ENDIF
CLOSE ALL DATABASE
OPEN DATABASE &cDB
SET DATABASE TO &cDB
RETURN
Mark
2004-11-09 16:58:02 UTC
Permalink
Hi Andy,
I had the same problem and I added the code below to each form's
DE.BeforeOpenTable.
You have to define your own gcDrive (e.g. C:\) and your own gcPath (e.g.
Data\Backup\). If your new database has a different name you also have to
define the variable cDatabase.

Local cObjClass, cObjName, cDatabase, cNewPath, oReference
Local Array aCursors[1]


If Not (Empty(gcDrive) Or Empty(gcPath))
= AMEMBERS(aCursors, THISFORM.Dataenvironment, 1)
For i = 1 to ALEN(aCursors,1)
If aCursors(i,2) = "Object"
cObjClass = "THISFORM.DATAENVIRONMENT." + aCursors(i,1) + ".Class"

If EVAL(cObjClass) = "Cursor"
cObjName = "THISFORM.DATAENVIRONMENT." + aCursors(i,1) + ".DATABASE"
cDatabase = EVAL(cObjName)
cNewDatabase = AllTrim(gcDrive) + AllTrim(gcPath) +
AllTrim(Substr(cDatabase, RAT("\",cDatabase) + 1))
oReference = EVAL("THISFORM.DATAENVIRONMENT." + aCursors(i,1))
oReference.Database = cNewDatabase

Endif
Endif
EndFor
Endif

HTH
Mark
Post by Andy Trezise
Thanks for your help...
I make sure that I don't have any forms open when I close the database
(CLOSE DATABASES ALL). The confusing thing is that the application is
opening a foreign database and tables when it starts up for the first time
OK (i.e. in a different place to the development database). It's not until I
close the database and try and reopen the archive that the problem shows up.
If I use DBC() it shows the correct database but still opens the tables from
the previously opened database.
Post by Stefan Wuebbe
Post by Andy Trezise
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the
one
Post by Stefan Wuebbe
Post by Andy Trezise
I used under development then the application seems to instinctively to
use
Post by Stefan Wuebbe
Post by Andy Trezise
that database rather that the implicit one that I tell it.
Right, there are paths stored in some of the DataEnvironment memebers'
properties unfortunately.
You can modify them either at design time / before build.
Or at runtime in yourForm.DE.BeforeOpenTables()
In addition to what Olaf said, Close Databases All might fail to
close them actually, as long as there are any data sessions referring
to the DBC you want to close. IOW, you'd need to close all forms etc.
beforehand.
hth
-Stefan
Post by Andy Trezise
I have an age old problem when trying to switch from one database (and
the
Post by Stefan Wuebbe
Post by Andy Trezise
tables contained within it) to another during my application (VFP8). I
just
Post by Stefan Wuebbe
Post by Andy Trezise
want the user to be able to switch from a LIVE database to an ARCHIVE
database.
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the
one
Post by Stefan Wuebbe
Post by Andy Trezise
I used under development then the application seems to instinctively to
use
Post by Stefan Wuebbe
Post by Andy Trezise
that database rather that the implicit one that I tell it.
2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns
the
Post by Stefan Wuebbe
Post by Andy Trezise
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to
call
Post by Stefan Wuebbe
Post by Andy Trezise
Function Switch_DB()
lArchive = !lArchive
IF lArchive
cDB = C:\DATAFILES\MYDB.DBC
ELSE
cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC
ENDIF
CLOSE ALL DATABASE
OPEN DATABASE &cDB
SET DATABASE TO &cDB
RETURN
Andy Trezise
2004-11-10 14:33:27 UTC
Permalink
That's worked a treat. Thanks very much.
Post by Olaf Doschke
Hi Andy,
I had the same problem and I added the code below to each form's
DE.BeforeOpenTable.
You have to define your own gcDrive (e.g. C:\) and your own gcPath (e.g.
Data\Backup\). If your new database has a different name you also have to
define the variable cDatabase.
Local cObjClass, cObjName, cDatabase, cNewPath, oReference
Local Array aCursors[1]
If Not (Empty(gcDrive) Or Empty(gcPath))
= AMEMBERS(aCursors, THISFORM.Dataenvironment, 1)
For i = 1 to ALEN(aCursors,1)
If aCursors(i,2) = "Object"
cObjClass = "THISFORM.DATAENVIRONMENT." + aCursors(i,1) + ".Class"
If EVAL(cObjClass) = "Cursor"
cObjName = "THISFORM.DATAENVIRONMENT." + aCursors(i,1) + ".DATABASE"
cDatabase = EVAL(cObjName)
cNewDatabase = AllTrim(gcDrive) + AllTrim(gcPath) +
AllTrim(Substr(cDatabase, RAT("\",cDatabase) + 1))
oReference = EVAL("THISFORM.DATAENVIRONMENT." + aCursors(i,1))
oReference.Database = cNewDatabase
Endif
Endif
EndFor
Endif
HTH
Mark
Post by Andy Trezise
Thanks for your help...
I make sure that I don't have any forms open when I close the database
(CLOSE DATABASES ALL). The confusing thing is that the application is
opening a foreign database and tables when it starts up for the first time
OK (i.e. in a different place to the development database). It's not until I
close the database and try and reopen the archive that the problem shows up.
If I use DBC() it shows the correct database but still opens the tables from
the previously opened database.
Post by Stefan Wuebbe
Post by Andy Trezise
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the
one
Post by Stefan Wuebbe
Post by Andy Trezise
I used under development then the application seems to instinctively to
use
Post by Stefan Wuebbe
Post by Andy Trezise
that database rather that the implicit one that I tell it.
Right, there are paths stored in some of the DataEnvironment memebers'
properties unfortunately.
You can modify them either at design time / before build.
Or at runtime in yourForm.DE.BeforeOpenTables()
In addition to what Olaf said, Close Databases All might fail to
close them actually, as long as there are any data sessions referring
to the DBC you want to close. IOW, you'd need to close all forms etc.
beforehand.
hth
-Stefan
Post by Andy Trezise
I have an age old problem when trying to switch from one database (and
the
Post by Stefan Wuebbe
Post by Andy Trezise
tables contained within it) to another during my application (VFP8). I
just
Post by Stefan Wuebbe
Post by Andy Trezise
want the user to be able to switch from a LIVE database to an ARCHIVE
database.
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the
one
Post by Stefan Wuebbe
Post by Andy Trezise
I used under development then the application seems to instinctively to
use
Post by Stefan Wuebbe
Post by Andy Trezise
that database rather that the implicit one that I tell it.
2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns
the
Post by Stefan Wuebbe
Post by Andy Trezise
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to
call
Post by Stefan Wuebbe
Post by Andy Trezise
Function Switch_DB()
lArchive = !lArchive
IF lArchive
cDB = C:\DATAFILES\MYDB.DBC
ELSE
cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC
ENDIF
CLOSE ALL DATABASE
OPEN DATABASE &cDB
SET DATABASE TO &cDB
RETURN
Anders Altberg
2004-11-15 12:37:11 UTC
Permalink
If you use distinctive names for the databases that helps because you can
open both at the same time more easily and refer to them accurately:
USE BackUps!Customer ALIAS Cust0 IN 0
USE MyDBC!Customers ALIAS Cust1 IN 0
or
SELECT ... FROM Backups!Customers AS C1 ;
FULL JOIN MyDBC!Customeres AS C2;
ON C1.pkey=C2.pkey ;
WHERE C1.pkey IS NULL OR C2.pkey IS NULL ;
INTE CURSOR Diffs

-Anders
Post by Andy Trezise
I have an age old problem when trying to switch from one database (and the
tables contained within it) to another during my application (VFP8). I just
want the user to be able to switch from a LIVE database to an ARCHIVE
database.
1. The initial problem is that if my database is held directly under the
folder from where the EXE runs and this folder has the same name as the one
I used under development then the application seems to instinctively to use
that database rather that the implicit one that I tell it.
2. Whatever I try I can't seem to get my application to switch from one
database to the other. The operation succeeds and DBC() always returns the
correct database name but when I try and open a table for some reason it
opens the table from my live database. I am using a hot key (ALT+A) to call
Function Switch_DB()
lArchive = !lArchive
IF lArchive
cDB = C:\DATAFILES\MYDB.DBC
ELSE
cDB = C:\DATAFILES\ARCHIVES\MYDB.DBC
ENDIF
CLOSE ALL DATABASE
OPEN DATABASE &cDB
SET DATABASE TO &cDB
RETURN
Loading...