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
Solved

Import/Convert .dbf file into access table

Posted on 2004-08-27
17
612 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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