import dbase file automation

[Jimbo]

Is it possible to have a database work as a mediator on importing dbase
files into access, prompted for table name (better yet, current date as
table name) and save as a different database? Or is there a VB application
for this? I will be importing dbase files about 3 times a week and would
like to shorten the time that it takes to do this. The table name and
database name would be the same. I prefer using the short date such as
23Jan05. I am a intermediate access user and have some experience with vba.
There will only be one table in the database, no queries.

btw, I'm using exporting the data from another program into dbaseIV. (can't
export as mdb-rats!) Is that the better version to use?

TIA!

Troy

 

[Rob Oldfield]

Take a look at the TransferDatabase method. That will allow you to import
and export tables to/from dbase IV and other mdbs. To do things like
renaming files/tables to date strings it would just be a case of getting a
date (and then probably using the format function to get it into the
required format) and then using that string for whichever transferdatabase
argument you require.

I don't believe you can use one mdb to transfer a table into another mdb
directly (although if I'm wrong there then I'm sure someone will pick me up
on it), but you can always import into the current db, export it out into
another mdb and then delete it.

 

[John Nurick]

Hi Troy,

It's possible to use a "mediator" database but unnecessary: this sort of
thing can usually be done with the Jet database engine without the
trouble of launching Access. Here's a VBScript designed to be called
from the command line. This one creates a new MDB file and imports the
DBase table into it; if you want to import into a new table in an
existing MDB, just change "CreateDatabase" to "OpenDatabase". Paste the
code below into Notepad and save it as DbfToMdb.vbs.


'VBScript BEGIN
'
'DbfToMdb
'
VBScript to create an Access mdb file containing
'a table imported from dbf format.
'MDBFile: path and name of file to create
'dBaseFolder: folder containing .dbf file
'TableName: name of table (i.e. name of dbf file
' without the ".dbf"

Option Explicit

Sub DBImport(MDBFile, dBaseFolder, TableName)
'Procedure to do the work, modified from VBA
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.CreateDatabase(MDBFile, _
";LANGID=0x0409;CP=1252;COUNTRY=0")

strSQL = "SELECT * INTO " & TableName _
& " FROM " & TableName _
& " IN '' [dBASE IV; DATABASE=" & dBaseFolder & ";];"
oDB.Execute strSQL

oDB.Close
End Sub

'Main body
If WScript.Arguments.Count <> 3 Then
MsgBox "Usage:" & Chr(13) & Chr(10) & Chr(10) _
& " DbfToMdb.vbs MDBFile dBaseFolder TableName " _
& Chr(13) & Chr(10) & Chr(10) _
& "MDBFile: .mdb file to create" _
& Chr(13) & Chr(10) _
& "dBaseFolder: location of .dBase files" _
& Chr(13) & Chr(10) _
& "TableName: name of dBase table to import", _
64, "Create Access MDB from DBF"
Else
DBImport WScript.Arguments(0), _
WScript.Arguments(1), WScript.Arguments(2)
End If

'VBSCript END


The script can fairly easily be modified to get the system date and use
it for the table name.

If you prefer to work in Access VBA, you can simply paste the DBImport()
procedure from the script into an Access module. It will work as is, but
I suggest you replace each apostrophe in the variable declarations with
As, e.g.
Dim oJet As DAO.DBEngine