Solved

Import/Convert .dbf file into access table

Posted on 2004-08-27
17
588 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now