Importing a Table with DBF Extension to Access

[DavidWE]

Does anyone have any suggestions on importing a DBF table into an Access table using VBA?

I tried the following code but get a message that the object being imported cannot be found.

Private Sub cmdExport_Click()
  DoCmd.TransferDatabase transfertype:=acExport, _
  databasetype:="Microsoft Access", _
  databasename:="C:\Documents and Settings\delliott\Desktop\Piece Rate   Tickets Salem\DB w RS\Excel test\Piece DB Test.mdb", _
  ObjectType:=acTable, Source:="C:\Documents and Settings\delliott\Desktop\Piece Rate Tickets Salem\DB w RS\Excel test\TOTALS.DBF", _
  Destination:="whattbl", structureonly:=True
End Sub

Executing the code causes the error "object cannot be found" and displays the following path.
"C:\Documents and Settings\delliott\Desktop\Piece Rate Tickets Salem\DB w RS\Excel test\TOTALS.DBF"

I'm assuming it does not recognize TOTALS.DBF as a legitimate file the way the code is written. The path is correct.

If I am unable to import the DBF file using VBA, would I be able to use VBA to convert the DBF table to an Access table? I can open the DBF in an Access table and save it, but it would be helpful to have a routine written in VBA to convert it or import it. Is there a way to open with Access and then save it using code? The DBF file defaults to Foxpro on my machine.

Thanks for any help.

 

[pbaldy]

For starters, you realize you've specified export rather than import?

acExport

 

[DavidWE]

Paul,

I've tried it both ways, export and import, just to see what happens. I get the same message each time.

 

[pbaldy]

What kind of file is that? The only DBF files I have around to test on are FoxPro, which is an ODBC connection. Is that a DBase file?

 

[DavidWE]

Yes, it is a DBase file. We get the file from a company that calculates our payroll. They have a procedure where we can download the file in that format. I think it would be much easier if we could get it as a csv file or even an Excel file, but I don't think they have any other alternatives.

When I double click it opens in Foxpro. It also opens in Access and Excel if I choose. It would be nice if I could write a routine where the user just clicks a button to get it into Access. Not everyone feels comfortable right-clicking on a file, then choosing "open with" and then saving the file.

I think the code I posted above would work (or would be close to working) if the table was in Access, Excel, or a text file.

 

[KenHigg]

I'm thinking you need to tell it this is a dbase file instead of an MS Access file - ?

(Which is where I'm thinking Paul is headed - ?)

 

[pbaldy]

Exactly. Have you changed the databasetype argument to the appropriate DBase format? I'm also not sure if the spaces in the path will affect anything. Just for chuckles, test with the file on the root of C until you get it working, then play with the path.

 

[DavidWE]

OK, I'm trying it with "dBase IV" now. I actually thought that line was specifying the destination instead of the source. I'm inexperienced with this code. That did make a difference. Now I'm getting some other error that I need to look at. I might post another question later.

Thanks, Paul and Ken.

 

[DavidWE]

And I will change the path. It's gotten too long anyway.

I'll try it on C.

 

[pbaldy]

I actually thought that line was specifying the destination instead of the source.

It would be the destination when exporting. It's for the source when importing. The other side of either is assumed to be Access (actually has to be Access).

 

[DavidWE]

Here is what I have now:

Private Sub cmdExport_Click()
 DoCmd.TransferDatabase transfertype:=acImport, _
  databasetype:="dBase IV", _
  databasename:="C:\TOTALS.DBF", _
  objecttype:=acTable, Source:="Totals", _
  Destination:="importedTotals", structureonly:=False
End Sub

"Totals" is the name of the table in TOTALS.DBF that needs to be imported into the Access database.

Now it gives the message ":="C:\TOTALS.DBF" is not a valid path. Make sure the path name is spelled correctly."

Any ideas? Can anyone provide a link with other examples of code to import/export data? Will I even be able to import data from a Foxpro database?

Thanks.

 

[KenHigg]

Any chance you can zip and post the dbf file (maybe with a small sample of data)?

 

[DavidWE]

Here is the file. Thanks for looking.

 

[KenHigg]

I was able to link directly to the table as a dbase 5 file.

Is there any reason why you can't simply do the same instead of trying to import with code?

 

[DavidWE]

Ken,

I probably can just link to the table. It would be helpful if I could then move the data to an Access table. I would like to be able to append the data to a permanent table and have it automatically create a key for each record.

I'll review some code that might work. I might have more questions on that later.

I might be able to just write the records from the linked table to the permanent table.

Do you have a link to some examples using VBA to link tables?

 

[KenHigg]

I do something similar where I get a new data file once a month. The first time I simply give it a generic name and put it in a specific folder. Then I attach to it in Access. Then I created an append query using this linked table as the source and run it. Then when I get a new data file I delete the old one and put the new one in its place. It must have the same name and be in the same folder location as the old one. So now when the append query is run it puts the new data in the Access table and I can to what is needed to the data - Think this will work in your situation?

fyi - I'm getting the same 'location' error you were getting - ?! (But I'm still tinkering with it )

 

[DavidWE]

Ken,
That would probably work. There might be a problem with other people in the company attempting it. That is why I am trying to automate it as much as possible. I could probably write the query to access the table in the folder where the file is downloaded. It would be nice if I could write some code to link to the table before running the query. What I would really like is to have a command button for the user to click to run code that would link the table and execute the query.

I'm still considering all of my options.