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

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]

Awesome! Thanks, Chet.

Henry

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On Behalf Of Chet Gardiner

Sent: 2007-03-22 3:30 PM

To: profoxtech@leafe.com

Subject: Re: List of tables in an ODBC Access mdb

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!

Vince,

Thanks for introducing me to the cool SQLgang. I was aimlessly looking in DBGetThis() and DBGetThat()

Henry

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On Behalf Of Vince Teachout

Sent: 2007-03-22 3:59 PM

To: profoxtech@leafe.com

Subject: Re: List of tables in an ODBC Access mdb

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.

SQLTables() is your friend.

SQLColumns() will be your friend, after you make friends with SQLTables()

_______________________________________________

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.

SQLTables() is your friend.

SQLColumns() will be your friend, after you make friends with SQLTables()

try MDB Browser -- Author: S.Arnold

I think I got it from this list - otherwise Google is your friend <s>.

Andrew Davies  MBCS CITP

  - AndyD        8-)#

**********************************************************************

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.

Please contact internet.administrators@manchester.gov.uk with any queries.

**********************************************************************