I was able to persuade a client that we should create his application in Visual FoxPro rather than in Microsoft Access 97, but he asked that I create a way to pull his data back into Access 97. I'm sharing a routine I created that exports all the tables from a Visual FoxPro database into an Access 97 database.

I have one problem I need help with. After the export completes, I am unable to exit from Access. Even if I switch to it and attempt to exit manually, it only minimizes. However, when I quit from Visual FoxPro, Access also closes. Any ideas?

By the way, this won't work with Access 2000, because Access 2000 doesn't allow the "FoxPro DBC" databasetype parameter (it requires ODBC). My client uses Access 97, and since I'm not sure what ODBC drivers he has installed (and he's two hours away), I decided to avoid using ODBC so that I
won't have to go there and fine-tune it.

PROCEDURE Acc_Exp
** Assumes database is currently open and is the one we want
** Database names/paths could easily be passed as parameters
** and status could be checked

WAIT WINDOW "Create Microsoft Access database ..." NOWAIT NOCLEAR

* Create array of all tables in FoxPro database
=ADBOBJECTS(gaTables, "TABLE")

PUBLIC oAccess,oDB

oAccess = CREATEOBJECT("Access.Application.8")

* If Access database already exists, open it. Otherwise, create it
* in the current directory
IF FILE(sys(5)+sys(2003)+"\AccessDB.MDB")
oAccess.OpenCurrentDatabase(sys(5)+sys(2003)+"\AccessDB.MDB")
ELSE
oAccess.NewCurrentDatabase(sys(5)+sys(2003)+"\AccessDB.MDB")
ENDIF
oDb = oAccess.CurrentDb

** Variables for command parameters
LOCAL lcDataBaseType,lcDataBaseName,lcDestination
lcDataBaseType = "FoxPro DBC" &&Note: This won't work in Access 2000 -
&& you have to use ODBC to import from a FoxPro database
lcDataBaseName = SYS(5)+SYS(2003)+"\Data\FoxProDB.DBC"
lcDestination = sys(5)+sys(2003)+"\AccessDB.MDB"

* First delete any tables in Access database that match
* names of tables in FoxPro database (otherwise, TABLE.DBF
* will come in as TABLE1, etc)
FOR EACH lcTable IN gaTables
IF TYPE('odb.TableDefs[lcTable]') = "O"
oAccess.DoCmd.DeleteObject(0,lcTable)
ENDIF
NEXT lcTable


FOR EACH lcTable IN gaTables
*IF lcTable#'SOURCE' && Use to elminate specific tables
&& Access will "USE AGAIN" each table
&& Therefore, tables must not be open exclusively
WAIT WINDOW "Create Microsoft Access database ..."+CHR(13)+ ;
"Exporting "+PROPER(lcTable) NOWAIT NOCLEAR
oAccess.DoCmd.TransferDatabase;
(0,lcDataBaseType,lcDataBaseName,0,lcTable,lcTable)
*ENDIF
NEXT lcTable

WAIT CLEAR

** These are all things I've tried to get Access to close
oAccess.DoCmd.Close()
oDb.Close
RELEASE oDb
oAccess.Quit
RELEASE oAccess
***

WAIT WINDOW "Create Microsoft Access database ..."+CHR(13)+ ;
"Export complete." TIMEOUT 3

ENDPROC