How can I retrieve tables from Access databse into VFP tables?

[Nifrabar]

I've asked this before but the threat seems te be removed:
(

http://www.tek-tips.com/viewthread.cfm?qid=1256763)

How can I retrieve tables from Access database into VFP tables?

-Bart

 

[mikeopolo]

Bart - try this; I have made the code more anonymous than the original, but I hope it works. I think the original Access db was 97.

Regards
Mike




PathName = 'c:\yourfolder\'

Select 5

lcNewDBC = &pathname+'test'

Create Database &lcnewdbc

gnConnHandle=SQLCONNECT('DSNName','','')
&& If required put username and password
&& in the blank fields above

IF gnConnHandle <= 0
* cannot make connection
= MessageBox('Cannot make connection! ',;
'SQL Connect Error')
=SQLDISCONNECT(gnConnHandle)
RETURN
ENDIF

&& CREATE CURSOR OF TABLE NAMES
&& AND SCAN TO CREATE TABLES FOR ALL OF THEM

Set safety off
STORE SQLTABLES(gnConnHandle, 'TABLE', 'mdbtables') TO nTables

IF nTables = 1
SELECT mdbtables
*brow
Go Top
Scan
CursorNm=""
Tablename = ""
TblName = ""

STORE 'SELECT * FROM ' + table_name to MValue

CursorNm = "[z"+alltr(table_name)+"]"
&& Resolves to eg [zNameOfTable] ie with quotes
Tablename = alltr(table_name)
&& Resolves to eg NameOfTable
TblName = &pathname+alltr(table_name)
&& Resolves to eg C:\yourfolder\NameOfTable

Mc = SQLEXEC(gnConnHandle,mValue,&CursorNm)
COPY TO &TblName DATABASE &lcnewdbc NAME &Tablename

EndScan
ENDIF

=SQLDISCONNECT(gnconnhandle)

Set safety on
Close tables
Close Database

 

[baltman]

Import DB from MS Access faq1251-5002

 

[JRB-Bldr]

Or, if you need this data on an on-going basis, you can use a Remote View into the Access table by means of an ODBC connection.

NOTE - Some Access data may be in one of their Binary fields (sort of like VFP Memo fields). If some of what you need comes from one of those 'fields', I'm not certain how you can get that particular data. If someone else knows I'd be interested in finding out how.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Bart,

Is this something you want to do programmatically? Or is it a one-off job that you can do interactively?

If the latter, you can always export the table from within Access. Export to a delimited text file, which you can then easily import into VFP.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[alvechurchdata]

Export to a delimited text file, which you can then easily import into VFP.

Or even to a dbf - but be aware that any long field names will be truncated.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Nifrabar]

Hi to all !

Thanks for the many replies.
I just have to be able once to convert tables from Access into VFP for I am transforming an application.
FAQ1251-5002 as pointed to by Baltman did the job.

Thanks again for your helpness on this issue.

-Bart

 

[baltman]

Watch out for loss of decimal precision if you have > 2 decimals in the DB.

Brian