Converting VFP data into ACCESS database

[csr]

I have just had a request from a customer to convert a visual foxpro file (including memo fields) into an access database.

Here is some code I used a while back to create an Access database with some data. I assume this would work for the present task also.

Just looking for confirmation from some of you.

Also, is there a field type in Access to deal with unlimited sized fields like memo fields ?



oAccess = createObject("Access.Application&quot
oAccess.NewCurrentDatabase(cWebDataFile)
oMDB = oAccess.CurrentDB

oTable = oMDB.CreateTableDef("KBEVENTS&quot

oField = oTable.CreateField("EVENT_ID&quot
oField.Type = 10 && Character Field
oField.Size = 10
oTable.Fields.Append(oField)

oField = oTable.CreateField("CATEGORY&quot
oField.Type = 10
oField.Size = 20
oTable.Fields.Append(oField)

oField = oTable.CreateField("YEAR&quot
oField.Type = 7 && Numerical Field
oField.Size = 4
oTable.Fields.Append(oField)

oField = oTable.CreateField("MONTH&quot
oField.Type = 10
oField.Size = 15
oTable.Fields.Append(oField)

oField = oTable.CreateField("DATES&quot
oField.Type = 10
oField.Size = 30
oTable.Fields.Append(oField)

oField = oTable.CreateField("CITY&quot
oField.Type = 10
oField.Size = 20
oTable.Fields.Append(oField)

oField = oTable.CreateField("STATE&quot
oField.Type = 10
oField.Size = 2
oTable.Fields.Append(oField)

oField = oTable.CreateField("PHONE&quot
oField.Type = 10
oField.Size = 30
oTable.Fields.Append(oField)

oField = oTable.CreateField("LOCALE&quot
oField.Type = 10
oField.Size = 40
oTable.Fields.Append(oField)

oField = oTable.CreateField("REP_NAME&quot
oField.Type = 10
oField.Size = 40
oTable.Fields.Append(oField)

oField = oTable.CreateField("REP_EMAIL&quot
oField.Type = 10
oField.Size = 50
oTable.Fields.Append(oField)

oField = oTable.CreateField("REP_URL&quot
oField.Type = 10
oField.Size = 200
oTable.Fields.Append(oField)

oMDB.TableDefs.Append(oTable)

inkey(1)

oRecordSet = oMDB.OpenRecordSet("KBEVENTS&quot
go top
do while not eof()
oProgress.setPercentage(40 + 60 * recno() / nTotalEvents)
oRecordSet.AddNew
oRecordSet.Fields("EVENT_ID&quot.value = events.event_id
oRecordSet.Fields("CATEGORY&quot.value = events.category
oRecordSet.Fields("LOCALE&quot.value = events.locale
oRecordSet.Fields("YEAR&quot.value = events.year
oRecordSet.Fields("MONTH&quot.value = events.month
oRecordSet.Fields("DATES&quot.value = events.dates
oRecordSet.Fields("CITY&quot.value = events.city
oRecordSet.Fields("STATE&quot.value = events.state
oRecordSet.Fields("PHONE&quot.value = events.phone
oRecordSet.Fields("REP_NAME&quot.value = events.web_name
oRecordSet.Fields("REP_EMAIL&quot.value = events.web_email
oRecordSet.Fields("REP_URL&quot.value = events.web_url

oRecordSet.Update
skip
enddo

inkey(1)
oRecordSet.Close


Don
dond@csrinc.com

 

[nirajj]

I was wondering if it was just one time task. It looks to me as if it is infact one time task because you are creating a table in your program.

You can probably export data from your foxpro table as a xls (COPY TO Command )and import it into ur access table... I think its much more time saving than writing programs for 1 time use.

 

[csr]

Well, that sounds like a good idea ... if it works.

Does Excel handle memo fields ?



Don
dond@csrinc.com

 

[mkrausnick]

No, exporting to Excel will not capture memo fields. But there may be an easier way. I found a reference to a program called DATAACC.EXE, part of Microsoft MDAC I believe, that can be used to allow Access to link to Foxpro tables. So the conversion may be easier than you think - import or link to the FOxpro tables and duplicate them in Access.

I would search Microsoft Technet for DATAACC.EXE and try installing that.

Hope this helps.



Mike Krausnick

 

[nirajj]

Here is what you need
In FoxPro...
COPY TO "c:\testing.dbf" TYPE FOXPLUS
This saves as dbase IV type.

In Access...
File -> Get External Data -> Import ( set you file type to dbase IV .dbf ) and select c:\testing.dbf

This also imports memo fields.

You can even link table using your ODBC connection. Just create DSN that points to your foxpro db and then link access to to those tables. I think this will help you if you want need time data in Access.

In short I dont feel any need to write program for one time conversion.

Hope this helps you

 

[rgbean]

A non-brainer variant: Go to the UT (

http://www.universalthread.com)

click on the Download picture on the left, and choose the Visual FoxPro area. Next enter 'DBC2MDB' (without the quotes) in the Title area and press Enter. You should get back an entry for:

"DBC2MDB - Fox to Access Downsizer January 16, 2001 13:30
The Upsizing Wizards in VFP help us move our data and database schema to Oracle or SQL Server. But what about "downsizing" to Access? DBC2MDB is a wizard that allows you to move your database and table structure to a new or existing MDB and, optionally, move the data itself."

Rick