Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > VBA to Import Visual FoxPro database (DBC) table (DBF) into Access
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 23rd March 22:50
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Access


I need your help with this!

I have a Visual FoxPro version 9 database (DBC container) that contains
several DBF's that I'm trying to write VBA code to import them into Access.

The application is still in Access 97. I found that the
DoCmd.TransferDatabase command is looking for FoxPro 2.6 version files.

I have been able to create a ODBC to the VFP database container and manually
select and import the files.

I must be missing a step because I'm getting an error "Operation is not
supported for this type of object DAO.Recordset":

Dim mycon As ADODB.Connection
Dim myrst As ADODB.Recordset
Dim strFolder, strTabName as string
Set mycon = CreateObject("ADODB.Connection")
Set myrst = New ADODB.Recordset
strfolder = "C:\myfolder\mydb.dbc"
strTabNmae = "myfile.dbf"

mycon = "Provider=vfpoledb;" & _
"Data Source=" & strfolder & ";" & _
"Mode=ReadWrite;" & _
"Collating Sequence=MACHINE:" & _
"Password="

mycon.Open
myrst.Open strTabName, mycon, adOpenDynamic, adLockReadOnly
' In both cases I get the error message:
'Try number one
' DoCmd.TransferDatabase acImport, myrst, strfolder, acTable,
strTabName, strTabName, False
'Try number two
DoCmd.TransferDatabase acImport, , strFolder, acTable,
strTabName, strTabName, False

This is the only code in the app referring to ADODB.

How do I correct this to automate the file import process?

Your help is appreciated!
  Reply With Quote


  sponsored links


2 23rd March 22:51
cindy winegarden
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Access


Hi,

I'm not an Access developer but when I look in the MSDN Library at the
TransferDatabase method's arguments, for the DatabaseType argument the list
is about the same as the list in the Access Get external data dialog:
Access, Jet, dBase, Paradox, ODBC Databases, WSS.

Your connection string uses OLE DB and it appears that OLE DB is not
supported.

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com
  Reply With Quote
3 23rd March 22:51
douglas j. steele
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Access


I believe that's correct: you must use ODBC, not Ole DB.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
  Reply With Quote
4 24th March 21:23
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Ac


Hi,

I changed approach and tried to use TransferDatabase using ODBC in stages,
first with VFP Free Table, then with a DBF in a DBC. In both cases I created
separate DSNs. I still need some help with this:

I created an ODBC connection to a VFP free table and manually linked it into
Access. This is what the linked file's description looks like:

"ODBC;DSN=Visual FoxPro
Tables;SourceDB=C:\DATAVFP;SourceType=DBF;Exclusiv e=No;BackgroundFetch=Yes;Collate=Machine;;Table=he lloworld"

Next I tried to modify the TransferDatabase MSDN Library example to pull in
the DBF which is part of the DBC and it's failing:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=VFPDBC;;;LANGUAGE=us_english;" _
& "DATABASE=c:\data\my.dbc", acTable, "helloworld", "helloworld"

Your help is appreciated!
  Reply With Quote
5 25th March 01:45
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Ac


I'm now able to bring in a Visual FoxPro dbf in a database container (*.dbc)
with a ODBC connection to the dbc with the following code. Is there a better
way to bring the data into Access then looping thought the recordset line by
line?
Function TestConn()
'The output table must exist.
On Error GoTo ErrCode
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlCmd As String
Set conn = New ADODB.Connection
conn.Open ("Provider=vfpoledb.1; Data Source=c:\DATAVFP\mydatabase.dbc")
sqlCmd = "DELETE * from mytable;"
DoCmd.RunSQL sqlCmd
sqlCmd = "Select * from mytable;"
Set rs = New ADODB.Recordset
rs.Open sqlCmd, conn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
'MsgBox (rs.Fields(0).Value)
CurrentDb.Execute "Insert Into mytable (fk_id, pk_id) Values (""" &
rs.Fields(0).Value & """,""" & rs.Fields(1).Value & """)" 'valueString)",
dbFailOnError
rs.MoveNext
Loop
ErrCode:
MsgBox Err.Number & ": " & Err.Description & " " & Err.Source & " " &
Err.HelpContext
End Function
  Reply With Quote
6 25th March 05:32
External User
 
Posts: 1
Default VBA to Import Visual FoxPro database (DBC) table (DBF) into Ac


I'm reposting this under subject VBA help needed with ODBC recorset.
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666