I've included a program below that will:

1) Open the MDB

2) Create a cursor containing the table names

3) Create a cursor containing all the field names

4) Select all the records from the Access table(s)

and copy them to a dbf

Have fun!

*-- GetAccess.prg

*--

*-- Extract list of tables, table structures

*-- and data (if any) from Access database.

*--

*-- If tlInit = .t. - It will Create new Structure table

*-- named MDB_Structures.dbf in a database

*-- container named AccessAccess

*--

*-- Otherwise, it will create one if one doesn't exist or

*-- use the existing one and add a new set of tables to it.

*--

*-- If you choose the same Access mdb it will delete previous

*-- structures for that mdb and add the new ones.

*--

*-- Chet Gardiner, 3/14/06 -- Feel free to use this code as you see fit...

PARAMETERS tlInit

SET DELETED ON

SET SAFETY OFF

SET EXCLUSIVE ON

*-- Create or Open AccessAccess Database

IF ! FILE('AccessAccess.dbc')

CREATE DATABASE AccessAccess

ELSE

OPEN DATABASE AccessAccess

ENDIF

*-- Start with new MDB_Structures table if Init = .t.

USE IN SELECT('MDB_Structures')

IF FILE('MDB_Structures.dbf') AND tlInit

REMOVE TABLE MDB_Structures DELETE

ENDIF

*-- Connect to Access

lcMDB = GETFILE('mdb')

lnHandle = SQLStringConnect("Driver={Microsoft Access Driver

(*.mdb)};Dbq=" + lcMDB)

*-- Get a list of all the Tables in the mdb file

*-- into cursor curTables

SQLTables(lnHandle, 'TABLE', 'curTables')

*-- Scan for Each table

SCAN

*-- Must trim the table_name

lcTableName = ALLTRIM(table_name)

WAIT WINDOW "Get Structure for " + lcTableName NOWAIT

*-- The structure will be placed in curColumns

IF SQLColumns(lnHandle, lcTableName, 'native', 'curColumns') < 0

*-- Shouldn't happen but if it does

*-- the error information will be in

*-- laErrorArray in debugger

AERROR(laErrorArray)

SET STEP ON

ENDIF

*-- Save in table's cursor named MDB_Structures

IF ! USED('MDB_Structures')

*-- Not used, either create or use it

IF tlInit OR ! FILE('MDB_Structures.dbf')

*-- Create new table

SELECT curColumns

=AFIELDS(latemp)

CREATE TABLE MDB_Structures FROM ARRAY latemp

ELSE

*-- Use existing Table

SELECT 0

USE MDB_Structures

*-- Delete any previous structure for this database

DELETE FOR ALLTRIM(table_cat) == LEFT(lcmdb, AT('.', lcmdb)-1)

ENDIF

ENDIF

*-- Add this tables' structure to result table

SELECT MDB_Structures

APPEND FROM DBF("curColumns")

*-- Delete Access table from database if exists

IF FILE(lcTableName + '.dbf')

REMOVE TABLE &lcTableName. DELETE

ENDIF

*-- Select all records from Access Table

WAIT WINDOW "Get records for " + lcTableName NOWAIT

USE IN SELECT("ac_table")

SQLEXEC(lnHandle, "select * from " + lcTableName, "ac_table")

IF USED('ac_table')

SELECT ac_table

IF RECCOUNT() > 0

*-- Write table if got some

COPY TO &lcTableName. DATABASE AccessAccess

ENDIF

ENDIF

*-- Back to tables' cursor (I just like to be sure)

SELECT curTables

EndScan

*-- Kill Handle

lnHandle = .NULL.

RETURN

henry.dagher@hrsdc-rhdcc.gc.ca wrote:

> I am trying to analyze an undocumented Oracle data model. The DB has been dumped into an Access mdb for me.

>

> Using VFP (of course), I'm trying to read in the names of all the tables (100+) in the mdb so I can programmatically create remote views. My ultimate goal is to scan all fields in all tables in search of data values.

>

> I have created a DB and made a connection to the MDB. What I'm missing is a way to populate an array with the names of the tables in the mdb. Then I can cycle and programmatically create my remote views. The actual search is NOT a problem. I am just struggling with getting the list of tables from the mdb.

>

> I want to avoid the mind-numbing prospect of 500+ clicks.

>

> Thanks,

>

> Henry

>

>

[excessive quoting removed by server]