I have hundreds of dbf files to import from a folder to a table in access
2003
and or 2007. At present I import each file as a table then copy and paste
to a
master table. For other imports (text, excel etc) I have a macro to import
all
files in a folder, but I have no option to import DBF files, Anyone able to
advise?.
TIA
taxi data

On Aug 12, 3:14*pm, "Aileen Turner" <taxiail... (AT) yahoo (DOT) com.au> wrote:
Why not just link to the DBF files and run a import query against the
file? Drop the link when you're done, then move on to the next.

You can also do this for the text and excel files....

Unfortunately I am unable to link to the files. There is a separate file
for each day. I can copy each file from the server to a folder on my
computer. I then need to import each file to access. I need to do this for
every day of 2009.
I have a an import macro (copied from a newsgroup years ago) to import all
text file in a folder, then run and append query to append the master table
and I was hoping to find something similar for DBF files.

"Chuck Grimsby" <cgatgoo (AT) gmail (DOT) com> wrote

On Aug 12, 3:14 pm, "Aileen Turner" <taxiail... (AT) yahoo (DOT) com.au> wrote:
Why not just link to the DBF files and run a import query against the
file? Drop the link when you're done, then move on to the next.

You can also do this for the text and excel files....

Taxi Aileen wrote:
Here's a sample sub to scan all files in the folder.
Sub ScanDBFFilesInFolder(strFolder As String)
Dim s As String

If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

'find first dbf file
s = Dir(strFolder & "*.dbf")

Do While s > ""

Debug.Print s

'find next dbf file
s = Dir()
Loop
MsgBox "Done. All dbfs viewed"
End Sub

If you can import them, you can link to them. I assume your are doing
it by hand. That has to be a drag.
Docmd.TransferDatabase acLink, "dBase III",...

Here's an example from Help
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
"Corporate Sales for April"

You might have to futz with it, try different types, but if you are
doing it manually you'll know which database type to use.

You'd then run that acLink, then the query to append, prior to the
s=Dir() line. I'd recommend you have make a copy before running and
doing testing of the mdb that has the backend tables.

On Aug 14, 10:08*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Good code Salad!

Since you can just link to the DBFs, rather then doing a Transfer, I'd
suggest just doing that, linking rather then importing. Often what I
see is that programmers import everything then run a series of queries
against the imported data to get whatever they need in the format they
need it in. A lot of work for little return. Instead, if they just
link to the data, often a query or two can do all of that in one
procedure. So rather then TransferDatabase, it's often better to link
to the data as if it were a seperate table (since it already is) via
the CreateTableDef command, and just do whatever query(s) are needed.
Faster and less repeated work for each link each time the data is
imported.

This works for Excel and CSV or TSV files as well as DBFs. Just
remember you can't do update queries on these files!

Thank you Salad and Chuck.... Just what I was looking for!!!
Aileen
"Chuck Grimsby" <cgatgoo (AT) gmail (DOT) com> wrote

On Aug 14, 10:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Good code Salad!

Since you can just link to the DBFs, rather then doing a Transfer, I'd
suggest just doing that, linking rather then importing. Often what I
see is that programmers import everything then run a series of queries
against the imported data to get whatever they need in the format they
need it in. A lot of work for little return. Instead, if they just
link to the data, often a query or two can do all of that in one
procedure. So rather then TransferDatabase, it's often better to link
to the data as if it were a seperate table (since it already is) via
the CreateTableDef command, and just do whatever query(s) are needed.
Faster and less repeated work for each link each time the data is
imported.

This works for Excel and CSV or TSV files as well as DBFs. Just
remember you can't do update queries on these files!

Chuck Grimsby wrote:
I haven't used the CreateTableDef method much so I thought it'd be as
simple as changing the connect string but it's the SourceTableName
property that needs to be changed and once it is a tabledef it doesn't
like to be changed. So I wipe out the link for each dbf that is scanned.

I'll assume the structures of all dbfs are the same. So queries could
use the table DBFFile (the name I use and created in code) for ease of
use.

So with the code to scan all the dbfs, a tabledef of the currently
scanned one can be created and then updated in whatever queries are
required pointing to tabledef "DbfFile"

Sub SetDbfSource(dbf As String)
useage: SetDbfSource "Customer.dbf"
Dim tdf As TableDef
Dim dbs As Database

Set dbs = CurrentDb

'delete existing linked file if it exists
On Error Resume Next
DoCmd.DeleteObject acTable, "DbfFile"
On Error GoTo 0

' Create a linked table
Set tdf = dbs.CreateTableDef("DbfFile")

'the connect string I use is for an old FoxPro file I have.
'the OP could create a link to one of those dbf files and then
'from the Immediate window find out what the connect string is. Ex:
'? currentdb.TableDefs("Junk").Connect will display the connect
'string

'also, the folder name C:\Test is my folder. The OP needs to be
'change itto reflect the OP's folder name
tdf.Connect = "dBase III;HDR=NO;IMEX=2;DATABASE=C:\Test"

'sets the name of the dbf
tdf.SourceTableName = dbf
dbs.TableDefs.Append tdf
dbs.close

Set tdf = Nothing
Set dbs = Nothing

End Sub

Salad <salad (AT) oilandvinegar (DOT) com> wrote in
news:5oydnR22hfaAx_rRnZ2dnUVZ_t2dnZ2d (AT) earthlink (DOT) com:

It seems to me that Vista/Win7 have made it undesirable to use Dir()
for any generic functions where you don't know that the user is
going to have full access to the folder. I've found that Dir() fails
on Win7 if you try to use it on folders that are protected for
user-level security tokens (e.g., C:\). I haven't yet tried it, but
I'm assuming that the File System Object is more graceful in dealing
with this.

Am I right on this or am I missing something significant?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/