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
[EMAIL PROTECTED] 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]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
