?
Solved

Import/Convert .dbf file into access table

Posted on 2004-08-27
17
Medium Priority
?
667 Views
Last Modified: 2011-10-03
Hi,

I want to convert/import a .dbf file(DBASE FILE) into a table in MS access.
Could anyone please suggest me any possible way of doing it....I am using Vb to program in access.

I tried using Import External Data option in access but for some dbf files it gave me an error saying that NO INDEX FOUND & for other files it did try to import the records but some coulmns were found empty because of type conversion error.

Any suggestion would really be very helpful.Thank you for your time.
 
0
Comment
Question by:bluetornado_666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 5
17 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11915460
You need to have a unique index (primary key) EVERY dbf table.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11915642
If you don't have a unique key you could go round about and use Excel to import the dbf file and then import the excel file (you could even save it as csv) into Access.

Steve
0
 

Author Comment

by:bluetornado_666
ID: 11915916
Thanks..

I just want to know is there is any possibility of having more than one indexes in the dbf file?
If yes then would it be then possible to convert the file into access(I mean import into access)?Is there any way of doing it programatically in access?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11915943
You can use TransferDatabase to programmatically import a dbf file into Access, the same as if you had clicked File->Import.
0
 

Author Comment

by:bluetornado_666
ID: 11915964
could you please provide me with the code for startup...
0
 

Author Comment

by:bluetornado_666
ID: 11916331
I used the TransferDatabase to import the dbf file.The name of the the dbf file is "Proj.dbf"

I have a form on which I created a textbox where I get the FileOpen Dialogue Box.After selecting the dbf file,I click on the command button for importing that file into access.But I get the Run-Time Error that the file path is not valid or check that you are connected to the server on which the file resides...

My file is on "C:\proj.dbf"

Please help

The syntax on command button is:-

DoCmd.TransferDatabase acImport, "dBASE IV", Me.txtPath, acTable, "Proj", "T", 0

I hope the syntax is correct..
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11916422
Hmm. Looks right, but try change the "T" to True, see if that works. Also confirm that Me.txtPath contains the value you expect.
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 120 total points
ID: 11916496
I think for dbf you only point to the folder and not the file

DoCmd.TransferDatabase acImport, "dBASE IV", "C:\", acTable, "Proj", True, 0

Steve
0
 

Author Comment

by:bluetornado_666
ID: 11916631
This is what I have done now..but still the same error...My file path is correct


DoCmd.TransferDatabase acImport, "dBASE IV", Me.txtPath, acTable, "PROJ.dbf"(this is the source file)

I have left the destination field,structure field & saveloginid field blank

have I done correct....?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11916676
See Steve's comment above - from what I recall from the few times I've imported dbf files,I think he's right.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11916694
can you manually import the file ... File --> Get External Data

I am wondering if you have the dbf ISAM drivers loaded?

Steve
0
 

Author Comment

by:bluetornado_666
ID: 11916806
hey steve,

Your code is correct.It runs fine now...but I just want to ask..when it imports the file in access it names the access table as "-1"...is there any way we can name the access table while running this code ...?

But when i use this code

 DoCmd.TransferDatabase acImport, "dBASE IV", Me.txtpath, acTable, "CAPPRJS", True, 0

why is it giving me the error for the wrong path...although in the textbox I select the correct dbf file....so it should take the correct path...is this wrong way...is that we always have to use the drive name even though we had selected the file from textbox..?please have ur views on this..


I also want to ask what does the arguement "destination" mean here ....is it used for naming the new table we create i access or what...??

Thanks for your efforts...
0
 

Author Comment

by:bluetornado_666
ID: 11916816
OOOPS...the file name is PROJ not CAPPRJS... typing  error here
0
 

Author Comment

by:bluetornado_666
ID: 11917457
Could you please give ur views on the last (above) question....is it that everytime we have to type the path of the file we want to access..can't we use the me.txtpath instead of "c:\"..

please comment..
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 80 total points
ID: 11917587
In txtpath, you probably have C:\PROJ.dbf

You don't want the PROJ.dbf part, just the C:\ part. You can try this:
DoCmd.TransferDatabase acImport, "dBASE IV", Left$(Me.txtpath,InstrRev(Me.txtpath,"\")), acTable, "CAPPRJS", True, 0
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11917886
destination is where you put what you want to call the table, if the tabl;e already exists then Access will add a numeric increment. If you always want to remove the table before importing the new one then ...

CurrentDb.Execute "DROP TABLE CAPPRJS"

before DoCmd.TransferDatabase ...

If everyone has Office 2000 or above then you could use ... the FileDialog object that comes with Office ....

Public Function GetDBFPath()

Dim dlgFile As FileDialog
Dim strPath As String

Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.AllowMultiSelect = False
dlgFile.ButtonName = "OK"
dlgFile.Title = "Select DBF file to import"

dlgFile.Show

If Len(dlgFile.SelectedItems(1) & vbNullString) > 0 Then
    strPath = dlgFile.SelectedItems(1)
    strPath = Left$(strPath, Len(strPath) - InStrRev(strPath, "\"))
Else
    MsgBox " You must pick a folder the DBF file is located in."
End If

Set dlgFile = Nothing

End Function

Steve

0
 
LVL 39

Expert Comment

by:stevbe
ID: 11917903
made a mistake in the example ....

Public Function GetDBFPath()

Dim dlgFile As FileDialog
Dim strPath As String

Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.AllowMultiSelect = False
dlgFile.ButtonName = "OK"
dlgFile.Title = "Select DBF file to import"

dlgFile.Show

If Len(dlgFile.SelectedItems(1) & vbNullString) > 0 Then
    strPath = dlgFile.SelectedItems(1)
    strPath = Left$(strPath, InStrRev(strPath, "\"))
    Me.txtpath.Value = strPath
Else
    MsgBox " You must pick a folder the DBF file is located in."
End If

Set dlgFile = Nothing

End Function
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question