PC Review


Reply
Thread Tools Rate Thread

Re: dBaseIV import by code problem

 
 
Steve Schapel
Guest
Posts: n/a
 
      9th Aug 2003
Peter,

A couple of things...
- I don't think it is correct to put the trailing \ on the directory
name in the Database Name argument
- There is a spelling error with the Object Type argument... should
be acTable, although you might as well leave this blank as it is not
applicable in this case
- You have not entered a Destination. I believe that this is
required.

I am also puzzled at your use of the Left$ function in the definition
of your fName variable. I can't really see what this would achieve
which simply Dir("C:\StoreDBFfiles\???A.dbf") alone will not achieve.

So, try it like this...
fName = Dir("C:\StoreDBFfiles\???A.dbf")
DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles", ,
fName, "MyTable"

- Steve Schapel, Microsoft Access MVP


On Sat, 09 Aug 2003 08:00:06 GMT, "Peter Jamieson"
<(E-Mail Removed)> wrote:

>This code snippet :
>
>fName = Left$(Dir("C:\StoreDBFfiles\???A.dbf"), 8)
>DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles\", acTablefs,
>fName
>
>gives me an error waring:
>
>Run_time error 2507:
>' The dBaseIV type isn't an installed database type or doesn't support the
>operation you chose"
>
>I have manually imported the .dbf file types OK.
>Any ideas as to what is cauing the error appreciated!
>Cheers, Peter J.
>


 
Reply With Quote
 
 
 
 
Peter Jamieson
Guest
Posts: n/a
 
      9th Aug 2003
G'day Steve,
Many thanks for your response which has proved to be just what was needed!
I have used your suggestions and my project has now progressed to the stage
below.
BTW, the "peculiar" fName variable was a hangover from earlier code and has
been rectified
along the lines of your suggestion.

The fName is useful to discover two required values, TCode and
DateCode.....from the
latter the date of the file can be determined.....and these values are to be
inserted into
the newly created fields, newField1 and newField2.

My next question is: how to do this insertion?
And then: how to collect each newly arrived table into a master table?...
and then I guess to delete the unwanted table?
Do I run the risk of serious bloat if I use this method for large numbers of
files?
As it stands the code stops, after only one dbf file is retrieved, at the
Set newField1 =etc
line since this has already been done at the previous cycle.

The full sub is intended to daily collect a lot of dbf files into one table
and so far looks like this:

Sub ImportDBFfiles()
'' Code to import all dbf files from a folder
'' and collect the files into an Access table
Dim fName As Variant
Dim theTable As TableDef
Dim dbs As Database
Dim newField1 As Field, newField2 As Field
Dim DateCode As String, TCode As String
Dim counter As Long

Set dbs = CurrentDb
fName = Dir("C:\StoreDBFfiles\???*R.dbf")
Do While (fName <> "")
DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles", ,
fName, "MyTable"
dbs.TableDefs.Refresh
Set theTable = dbs.TableDefs!MyTable
Set newField1 = theTable.CreateField("DateCode", dbText, 4)
Set newField2 = theTable.CreateField("TCode", dbText, 3)
With theTable
.Fields.Append newField1
.Fields.Append newField2
End With
' Determine TCode and DateCode from the fName
TCode = Left(fName, 3)
DateCode = Mid(fName, 4, 4)
'' Not sure how to insert these values into their fields
'' Not sure how to code append MyTable to a master table
Loop
dbs.Close
Set dbs = Nothing
End Sub

Again, many thanks for your assistance....much appreciated!
Cheers, Peter J.


"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Peter,
>
> A couple of things...
> - I don't think it is correct to put the trailing \ on the directory
> name in the Database Name argument
> - There is a spelling error with the Object Type argument... should
> be acTable, although you might as well leave this blank as it is not
> applicable in this case
> - You have not entered a Destination. I believe that this is
> required.
>
> I am also puzzled at your use of the Left$ function in the definition
> of your fName variable. I can't really see what this would achieve
> which simply Dir("C:\StoreDBFfiles\???A.dbf") alone will not achieve.
>
> So, try it like this...
> fName = Dir("C:\StoreDBFfiles\???A.dbf")
> DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles", ,
> fName, "MyTable"
>
> - Steve Schapel, Microsoft Access MVP
>
>
> On Sat, 09 Aug 2003 08:00:06 GMT, "Peter Jamieson"
> <(E-Mail Removed)> wrote:
>
> >This code snippet :
> >
> >fName = Left$(Dir("C:\StoreDBFfiles\???A.dbf"), 8)
> >DoCmd.TransferDatabase acImport, "dBaseIV", "C:\StoreDBFfiles\",

acTablefs,
> >fName
> >
> >gives me an error waring:
> >
> >Run_time error 2507:
> >' The dBaseIV type isn't an installed database type or doesn't support

the
> >operation you chose"
> >
> >I have manually imported the .dbf file types OK.
> >Any ideas as to what is cauing the error appreciated!
> >Cheers, Peter J.
> >

>



 
Reply With Quote
 
 
 
 
Steve Schapel
Guest
Posts: n/a
 
      9th Aug 2003
Peter,

(My apologies for accidentally sending this as private email)

Possibly the simplest way is to use Update and Append queries to get
the data where you want it. Declare a variable...
Dim strSQL As String
.... and then in the place you indicated in your code, something like
this...

strSQL = "UPDATE MyTable SET DateCode='" & Mid(fName, 4, 4) & "',
TCode='" & Left(fName, 3) & "'"
dbs.Execute strSQL
strSQL = "INSERT INTO MasterTable (DateCode, TCode, OtherFields)
SELECT DateCode, TCode, OtherFields FROM MyTable"
dbs.Execute strSQL

I must say, though, that I would not approach it in this way. I can
see no reason for the creation of the new fields in MyTable. If you
need the DateCode and TCode included in each record in the
MasterTable, you can bypass putting this into MyTable, just include it
in the data append to MasterTable, e.g.

Sub ImportDBFfiles()
Dim fName As String
Dim strSQL As String
Dim dbs As Database
Set dbs = CurrentDB
Do While (fName <> "")
fName = Dir("C:\StoreDBFfiles\???*R.dbf")
DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles",
, fName, "MyTable"
strSQL = "INSERT INTO MasterTable (DateCode, TCode, OtherFields)
SELECT '" & Mid(fName, 4, 4) & "', '" & Left(fName, 3) & "',
OtherFields FROM MyTable"
dbs.Execute strSQL
strSQL = "DELETE * FROM MyTable"
dbs.Execute strSQL
Kill "C:\StoreDBfiles\" & fName
Loop
End Sub

Note that I have moved the fName definition inside the loop... it
wouldn't work the way you had it before. Also, I have assumed you
would take care of the fiel already processed by deleting rather than
renaming.

Having said all that, I still probably wouldn't do it this way. I
would have a separate import logging table, with an ID field and the
DateCode and TCode in there, and then reference this from MasterTable,
so...

Sub ImportDBFfiles()
Dim fName As String
Dim strSQL As String
Dim NextID As Long
Dim dbs As Database
Set dbs = CurrentDB
Do While (fName <> "")
NextID = DMax("[ID]","LogTable") + 1
fName = Dir("C:\StoreDBFfiles\???*R.dbf")
DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles",
, fName, "MyTable"
strSQL = "INSERT INTO LogTable (ID, DateCode, TCode) _
SELECT " & NextID & ", '" & Mid(fName, 4, 4) & "', '" & _
Left(fName, 3) & "'"
dbs.Execute strSQL
strSQL = "INSERT INTO MasterTable (ID, OtherFields) _
SELECT " & NextID & ", OtherFields FROM MyTable"
dbs.Execute strSQL
strSQL = "DELETE * FROM MyTable"
dbs.Execute strSQL
Kill "C:\StoreDBfiles\" & fName
Loop
End Sub

Warning: All the above is "air code", and I would be surprised if it
doesn't need some tweaking. Especially as this is a macros newsgroup,
and none of this has got anything to do with macros :-)

- Steve Schapel, Microsoft Access MVP


On Sat, 09 Aug 2003 14:57:01 GMT, "Peter Jamieson"
<(E-Mail Removed)> wrote:

>G'day Steve,
>Many thanks for your response which has proved to be just what was needed!
>I have used your suggestions and my project has now progressed to the stage
>below.
>BTW, the "peculiar" fName variable was a hangover from earlier code and has
>been rectified
>along the lines of your suggestion.
>
>The fName is useful to discover two required values, TCode and
>DateCode.....from the
>latter the date of the file can be determined.....and these values are to be
>inserted into
>the newly created fields, newField1 and newField2.
>
>My next question is: how to do this insertion?
>And then: how to collect each newly arrived table into a master table?...
>and then I guess to delete the unwanted table?
>Do I run the risk of serious bloat if I use this method for large numbers of
>files?
>As it stands the code stops, after only one dbf file is retrieved, at the
>Set newField1 =etc
>line since this has already been done at the previous cycle.
>
>The full sub is intended to daily collect a lot of dbf files into one table
>and so far looks like this:
>
>Sub ImportDBFfiles()
>'' Code to import all dbf files from a folder
>'' and collect the files into an Access table
>Dim fName As Variant
>Dim theTable As TableDef
>Dim dbs As Database
>Dim newField1 As Field, newField2 As Field
>Dim DateCode As String, TCode As String
>Dim counter As Long
>
>Set dbs = CurrentDb
> fName = Dir("C:\StoreDBFfiles\???*R.dbf")
>Do While (fName <> "")
> DoCmd.TransferDatabase acImport, "dBase IV", "C:\StoreDBFfiles", ,
>fName, "MyTable"
> dbs.TableDefs.Refresh
>Set theTable = dbs.TableDefs!MyTable
>Set newField1 = theTable.CreateField("DateCode", dbText, 4)
>Set newField2 = theTable.CreateField("TCode", dbText, 3)
>With theTable
> .Fields.Append newField1
> .Fields.Append newField2
>End With
> ' Determine TCode and DateCode from the fName
> TCode = Left(fName, 3)
> DateCode = Mid(fName, 4, 4)
>'' Not sure how to insert these values into their fields
>'' Not sure how to code append MyTable to a master table
>Loop
> dbs.Close
>Set dbs = Nothing
>End Sub
>
>Again, many thanks for your assistance....much appreciated!
>Cheers, Peter J.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting to dBaseIV - error "Field will not fit in record" rebecca Microsoft Access 1 19th Sep 2008 07:44 AM
importing old dbaseIV tables into acces =?Utf-8?B?R29tcDU=?= Microsoft Access External Data 2 13th Sep 2005 10:32 PM
Connecting dBaseIV and Access 97 w/Upgrade to 2000 and Now 2003 Fred Herndon via AccessMonster.com Microsoft Access Form Coding 0 28th Dec 2004 03:17 AM
Converting Dataset to a .IND file( dbaseIV index file) Hari Microsoft ADO .NET 0 12th Jul 2003 11:02 AM
Notes Truncating from dBaseIV table Fred Block Microsoft Access Queries 0 9th Jul 2003 08:57 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:50 AM.