Derek,
I concur on using ODBC to convert the Access tables, but I'd like to comment on that conversion.
I am the unfortunate recipient of a great volume of data that originates in Access (over 3 million records worth as of this date). Conversion of this data has led me to believe that the Access export routines, while convienient, leave much to be desired in less than ideal situations.
If the tables that you are converting have that imperfect data (carriage returns/tabs/line feeds in text fields) then you will need to programmatically remove those problems during conversion.
Below are examples of some code that has made my conversion job easier.
Open the Access MDB and populate a listbox with the tables within the Access database:
PRIVATE res, tablcnt
DIMENSION THISFORM.accesstables[1]
THISFORM.accesstables[1] = ''
THISFORM.sqlhandle = SQLCONNECT()
THISFORM.Text2.Value = ''
IF THISFORM.sqlhandle > 0
SELECT 0
res = SQLSETPROP(THISFORM.sqlhandle, 'Asynchronous', .F.)
res = SQLTABLES(THISFORM.sqlhandle, "TABLE", "ACCTABLES")
IF res > 0
tablcnt = 0
IF USED("ACCTABLES")
SELECT acctables
GO TOP
THISFORM.Text2.Value = ALLT(acctables.table_cat)
DO WHILE !EOF('acctables')
IF !EMPTY(acctables.table_name)
tablcnt = tablcnt + 1
DIMENSION THISFORM.accesstables[tablcnt]
THISFORM.accesstables[tablcnt] = ALLT(acctables.table_name)
ENDIF
SKIP
ENDDO
USE
ENDIF
ENDIF
ENDIF
THISFORM.List1.NumberOfElements = ALEN(THISFORM.accesstables)
THISFORM.List1.ListIndex = 1
THISFORM.List1.TopItemID = 1
THISFORM.List1.Requery()
Once you have a list box with the Access names you may programmatically create a VFP table or compare the number of columns in the source and target tables (here the target VFP tables already exist, but it would be just as easy to create the VFP tables from the array THISFORM.accessfields):
PARAMETERS tablname
PRIVATE vfldcnt, afldcnt, res, fldcnt
DIMENSION THISFORM.accessfields[1,4]
THISFORM.accessfields[1,1] = ''
THISFORM.accessfields[1,2] = ''
THISFORM.accessfields[1,3] = 0
THISFORM.accessfields[1,4] = 0
THISFORM.Text4.Value = ''
THISFORM.Text5.Value = ''
IF USED('vfptable') AND THISFORM.sqlhandle > 0
THISFORM.Text3.Value = 'Checking VFP Table'
SELECT vfptable
vfldcnt = AFIELDS(THISFORM.vfpfields)
THISFORM.Text5.Value = ALLT(STR(vfldcnt))
SELECT 0
THISFORM.Text3.Value = 'Getting Access Table Info'
res = SQLCOLUMNS(THISFORM.sqlhandle, tablname, "FOXPRO", "accesscols")
IF res > 0
IF USED("accesscols")
afldcnt = RECCOUNT("accesscols")
THISFORM.Text4.Value = ALLT(STR(afldcnt))
IF afldcnt = vfldcnt
THISFORM.Text3.Value = 'Awaiting User Input'
THISFORM.Commandgroup1.Buttons(1).Enabled = .T.
THISFORM.ColCheck.Caption = "Column Count Match"
ELSE
THISFORM.Text3.Value = ''
THISFORM.Commandgroup1.Buttons(1).Enabled = .F.
THISFORM.ColCheck.Caption = "Tables Do Not Match"
ENDIF
SELECT accesscols
GO TOP
fldcnt = 0
DO WHILE !EOF('accesscols')
IF !EMPTY(accesscols.field_name)
fldcnt = fldcnt + 1
DIMENSION THISFORM.accessfields[fldcnt,4]
THISFORM.accessfields[fldcnt,1] = ALLT(accesscols.field_name)
THISFORM.accessfields[fldcnt,2] = ALLT(accesscols.field_type)
THISFORM.accessfields[fldcnt,3] = accesscols.field_len
THISFORM.accessfields[fldcnt,4] = accesscols.field_dec
ENDIF
SKIP
ENDDO
USE
ELSE
THISFORM.Commandgroup1.Buttons(1).Enabled = .F.
WAIT WINDOW "Could Not Get Access Column Info" TIMEOUT 2
ENDIF
ELSE
THISFORM.Commandgroup1.Buttons(1).Enabled = .F.
WAIT WINDOW "Could Not Get Access Column Info" TIMEOUT 2
ENDIF
ELSE
THISFORM.Commandgroup1.Buttons(1).Enabled = .F.
IF !USED('vfptable')
WAIT WINDOW "You Must Open The VFP Target Table" TIMEOUT 2
ENDIF
IF THISFORM.sqlhandle < 1
WAIT WINDOW "You Must Open The Access Source Database" TIMEOUT 2
ENDIF
ENDIF
Now that the Access database is opened and the source and target fields have been resolved, go to work:
PARAMETERS tablname
PRIVATE vfldcnt, afldcnt, res, indx, mvarname, dataval, reccnt
IF USED('vfptable') AND THISFORM.sqlhandle > 0
*ACTIVATE WINDOW DEBUG
*SUSPEND
SELECT 0
THISFORM.Text3.Value = 'Getting Access Data...'
res = SQLEXEC(THISFORM.sqlhandle, "Select * From " + tablname, "accessdata")
IF res > 0
IF USED("accessdata")
SELECT accessdata
GO TOP
reccnt = 0
DO WHILE !EOF('accessdata')
SELECT vfptable
SCATTER MEMO MEMVAR BLANK
SELECT accessdata
reccnt = reccnt + 1
THISFORM.Text3.Value = 'Converting Data Rec#' + ALLT(STR(reccnt))
FOR indx = 1 TO ALEN(THISFORM.accessfields,1)
mvarname = 'm.' + ALLT(THISFORM.vfpfields[indx,1])
dataval = EVALUATE('accessdata.' + ALLT(THISFORM.accessfields[indx,1]))
DO CASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'C','M')
DO CASE
CASE ISNULL(dataval)
STORE '' TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE ALLT(dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE ALLT(STR(dataval)) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE ALLT(STR(dataval,THISFORM.accessfields[indx,3],THISFORM.accessfields[indx,4])) ;
TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
IF dataval
STORE 'Y' TO (mvarname)
ELSE
STORE 'N' TO (mvarname)
ENDIF
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE ALLT(DTOC(dataval)) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE ALLT(TTOC(dataval)) TO (mvarname)
ENDCASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'I')
DO CASE
CASE ISNULL(dataval)
STORE 0 TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE INT(VAL(dataval)) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE (dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE INT(dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
IF dataval
STORE 1 TO (mvarname)
ELSE
STORE 0 TO (mvarname)
ENDIF
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE 0 TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE 0 TO (mvarname)
ENDCASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'N','F','B')
DO CASE
CASE ISNULL(dataval)
STORE 0 TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE VAL(dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE (dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE (dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
IF dataval
STORE 1 TO (mvarname)
ELSE
STORE 0 TO (mvarname)
ENDIF
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE 0 TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE 0 TO (mvarname)
ENDCASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'L')
DO CASE
CASE ISNULL(dataval)
STORE .F. TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE .F. TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE .F. TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE .F. TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
STORE (dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE .F. TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE .F. TO (mvarname)
ENDCASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'D')
DO CASE
CASE ISNULL(dataval)
STORE '' TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE CTOD(ALLT(dataval)) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE (dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE TTOD(dataval) TO (mvarname)
ENDCASE
CASE INLIST(ALLT(THISFORM.vfpfields[indx,2]),'T')
DO CASE
CASE ISNULL(dataval)
STORE '' TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'C','M')
STORE CTOD(ALLT(dataval)) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'I')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'N','F','B')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'L')
STORE {} TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'D')
STORE DTOT(dataval) TO (mvarname)
CASE INLIST(ALLT(THISFORM.accessfields[indx,2]),'T')
STORE (dataval) TO (mvarname)
ENDCASE
ENDCASE
ENDFOR
SELECT vfptable
INSERT INTO vfptable FROM MEMVAR
SELECT accessdata
SKIP
ENDDO
THISFORM.Text3.Value = 'Conversion Complete'
THISFORM.Commandgroup1.Buttons(1).Enabled = .F.
ELSE
THISFORM.Text3.Value = ''
WAIT WINDOW "Could Not Get Access Info" TIMEOUT 2
ENDIF
ELSE
THISFORM.Text3.Value = ''
WAIT WINDOW "Could Not Get Access Info" TIMEOUT 2
ENDIF
ELSE
THISFORM.Text3.Value = ''
IF !USED('vfptable')
WAIT WINDOW "You Must Open The VFP Target Table" TIMEOUT 2
ENDIF
IF THISFORM.sqlhandle < 1
WAIT WINDOW "You Must Open The Access Source Database" TIMEOUT 2
ENDIF
ENDIF
Within the above loop you may perform any conditional formatting or whatever.
HOPE THIS HELPS,
ED
