Importing Data from a program

[Mike Wood]

Is it possible to automate the import of data in a Visual Basic
program? Visual FoxPro has the IMPORT command that does this very
nicely.

I have multiple files that I must import into Access on a repetive
basis for data conversion for a large project. It is a real
pain-in-the-butt to do this manually for 25 tables.

 

[Mike Wood]

Or can this be done with Macros? Is there a recording facility like
in Word?

 

[John Nurick]

Hi Mike,

Access doesn't have a macro recorder, but it does have both a macro
language and Visual Basic for Applications. Each can import data:
TransferText, TransferSpreadsheet, etc.

 

[Mike Wood]

The data I am importing are Visual FoxPro tables. Do you have a
sample of how I can do this?

 

[Mike Wood]

I am trying to use the TransferDatabase action in a macro. I have set
the TransferType to Import, the Database Type to ODBC database, the
Database Name to ubreports.dsn, the Object Type to Table, the Source
File to tblcustfile, the Destination to Customer File and the
Structure Only option to No. When I run the macro I get an error
message 'Could not find installable ISAM. When I go thru the Get
External Data menu option it works fine.

Any ideas?

 

[Ronald W. Roberts]

Is it possible to automate the import of data in a Visual Basic
program? Visual FoxPro has the IMPORT command that does this very
nicely.

I have multiple files that I must import into Access on a repetive
basis for data conversion for a large project. It is a real
pain-in-the-butt to do this manually for 25 tables.

This sample code was posted by MVP Joe Fallon in answer another
questions:

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", _
"AccessTableName", "c:\MyFiles\" & strfile, True
'delete the file (consider moving
'it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

HTH
Ron

 

[John Nurick]

I don't, I'm afraid, Mike, and have no experience importing from VFP.

Try posting a message in microsoft.public.access.externaldata explaining
clearly what you're trying to do and what's happening - with a subject
line that gives a clue to the nature of the problem (e.g. "Macro import
from FoxPro fails").