Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import/Convert .dbf file into access table

Posted on 2004-08-27
17
Medium Priority
?
689 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

927 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