INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automation

Import DB from MS Access by baltman
Posted: 31 Mar 04 (Edited 28 Sep 06)

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q260405

Microsoft Knowledge Base Article - 260405

HOWTO: Import Table Structures and Data from Microsoft Access

This article was previously published under Q260405

SUMMARY
This article illustrates how to programmatically create table structures that correspond to tables in Microsoft Access Master Data Base (MDB) files. This article also illustrates how to copy the data in Microsoft Access tables to Visual FoxPro tables.

MORE INFORMATION
In situations where developers want to copy data from Microsoft Access tables to Visual FoxPro tables, the SQLTABLES, AFIELDS, and SQLEXEC functions may be used to:
Retrieve information regarding the number of tables contained in the MDB file.
Retrieve the names of tables in the MDB file.
Retrieve the structure of each of the tables contained in the MDB file.
Create tables that correspond to the structures of the identified tables.
Copy data from Microsoft Access tables to Visual FoxPro tables.

The following code snippet illustrates the process of copying data from Microsoft Access tables and information from the MDB file into Visual FoxPro tables. The code sample browses the Table_name field of the SQLResult cursor.

CODE

LOCAL lcMyFile, lnConnHandle, lnResult, lcSQLCommand, lnGetData, lcNewName
LOCAL ARRAY laWhatErr(1)

lcMyFile = GETFILE('MDB')

*!* No point if no MDB file is selected
IF !EMPTY(lcMyFile)
   *!* Use a SQLStringConnect to connect to the MDB file
   lnConnHandle = ;
      SQLSTRINGCONNECT('DRIVER=MICROSOFT ACCESS DRIVER (*.MDB);DBQ=' + ;
         lcMyFile)
      
   IF lnConnHandle > 0
      *!* Need to know what tables are contained in the MDB file
      lnResult = SQLTABLES(lnConnHandle, 'TABLE')
      
      IF lnResult > 0
         *!* Create a new DBC named "MYNEW"
         CREATE DATABASE mynew
         *!* Select SQLResult and scan through the cursor.
         SELECT SQLResult
         SCAN
            *!* Build a SQL SELECT statement to pass to SQLEXEC()
            lcSQLCommand = ;
               "SELECT * FROM [" + ALLTRIM(SQLResult.table_name) + "]"
               
            lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')
            IF lnGetData > 0
               *!* SQLEXEC() was successful
               lcNewName = STRTRAN(ALLTRIM(SQLResult.table_name), " ", "_")

               SELECT newdata
               *!* Copy Table Structure and Data to a DBF file
               *!* VFP 3.0 - loses long field names
               *!* COPY TO (lcNewName)
               *!* ADD TABLE (lcNewName)
           *!* VFP 5.0/6.0 - keeps long field names
               COPY TO (lcNewName) DATABASE myNew
            ENDIF
         ENDSCAN
      ENDIF
      *!* Done Getting Data, so Disconnect
      =SQLDISCONNECT(lnConnHandle)
   ELSE
      *!* The SQLStringConnect failed
      =MESSAGEBOX('ODBC Connection Failed', MB_ICONEXCLAMATION, 'ODBC')
      *!* Call AERROR()
      =AERROR(laWhatErr)
      *!* Display the error
      DISPLAY MEMORY LIKE laWhatErr
   ENDIF
ENDIF

BROWSE FIELDS table_name
                
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.

FoxPro programming rocks!

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close