Solved

Import/Convert .dbf file into access table

Posted on 2004-08-27
17
621 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 30 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 20 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 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