Convert Table (mdb) to table DBaseIV (dbf) ?

[regis]

I would like to convert or export table (mdb) to table (dbf).

Is't possible and how can I do that? I need exemple..

Thanks a lot
Redg

[epelyavski]

Well, you can do it manually through Access. Or take a look at a draft function I created to Import DBF to Access. You'll need to read Access VBA programming help and change it to Export.


private Declare Function ShowWindow Lib "user32" (byval hwnd as Long, byval nCmdShow as Long) as Long
private Const SW_HIDE = 0

' This function IMPORTS a DBF file to an Access table
private Sub Import_DBF()
' Access
Dim a as Access.Application

set a = new Access.Application

' Hide Access Window
ShowWindow a.hWndAccessApp, SW_HIDE

a.OpenCurrentDatabase <Access Database name>

a.DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DBFDATABASE;", acTable, <DBF Table>, <DBF Table>

a.CloseCurrentDatabase

a.Quit acQuitSaveAll

set a = nothing
End Sub





Let me know if it helps.
-EP

[regis]

Hi and Thanks a lot for your help.

I thinks it was the good solution but I received and error at this line...
a.DoCmd.TransferDatabase acImport, _
"ODBC Database", _
"ODBC;DSN=DBFDATABASE;", acTable, "C:\Toto.dbf"

The error refer to "the type of database (ODBC) is not installed do you know where is my problem?

May be in references but am not sure.

Thanks a lot and sorry for my bad english
Redg

[epelyavski]

You have to create an ODBC DSN (Data Source Name) with FoxPro DBF driver and call it DBFDATABASE and point it to the folder where you have your DBF file(s). In your example it is 'C:\'

[daneb]

Hi.

I'm also having a problem importing an external .DBF database (Clipper/dBase) to an Access database. (The original question in this thread I think is the opposite, that is exporting an Access table to a DBF file.) I tried your code, but unfortunately, my program seemed to hang in the a.DoCmd.TransferDatabase line. When I clicked the Stop button in the VB environment, a message box appeared saying that the other program (Access) is busy. When I pressed Ctrl+Alt_Del, my Access program is not responding. When I displayed the error through a message box, this showed up:

-2147023170 Automation error

A sample of my code appears below (I exclude the declarations at the top of my form):

Private Sub Import_DBF()
' This function IMPORTS a DBF file to an Access table

' Access
Dim a As Access.Application

On Error GoTo Import_DBF_Error

Set a = New Access.Application

' Hide Access Window
ShowWindow a.hWndAccessApp, SW_HIDE
a.OpenCurrentDatabase App.Path & "\database\bot.mdb"
a.DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DBFDB;", acTable, App.Path & "\dbf_files\newdbf.dbf", "NewDBF"
a.CloseCurrentDatabase
a.Quit acQuitSaveAll
Set a = Nothing
Exit Sub

Import_DBF_Error:
MsgBox Err.Number & ": " & Err.Description

End Sub

where: DBFDB is the System DSN using a dBase driver and points to App.Path & "\dbf_files\".

What's wrong with my code? Thanks for the help in advance!

[regis]

Hi!

I have an other question for you.

I don't know where and how can I create the ODBC DSN. DO I need the special logiciel ??? I have Visual studio 6 is't ok to make link ODBC ?

I don't understand ODBC (I never use this)

Thanks
Sorry for my bad english
Redg

[epelyavski]

Try not to use DBF extension: you use App.Path & "\dbf_files\newdbf.dbf", put "newdbf" instead. Your DSN already knows where the files are.


a.DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DBFDB;", acTable, "newdbf", "NewDBF"




Make sure that "newdbf" table doesn't exist in your Access db:


on error resume next
a.DoCmd.DeleteObject acTable, "newdbf"
on error GoTo ErrorHandler




Also you can try using "dBase 5.0" instead of ODBC

a.DoCmd.TransferDatabase acImport, "dBase 5.0", _
App.Path & "\dbf_files\", acTable, "newdbf.dbf", "newdbf"

[epelyavski]

Go to Start->Settings->Control Panel, then "ODBC data sources (32bit)"
Read MSDN Library for further info.

[daneb]

Thanks again for the reply. Your suggestion of using "dBase 5.0" instead of "ODBC Database" worked! However, when I executed the function the second time, no error occurred and when I opened my database, there were "newdbf" and "newdbf1" tables existing. How can I test if a table that I want to save data to is already existing or not, so that if it is existing, I can delete it first with the code you suggested?

[epelyavski]

I delete tables with the following code:

...
on error resume next
a.DoCmd.DeleteObject acTable, <Your Table Name>
...

[daneb]

Thanks again. I got the following error when I tried to delete a table, "NewDBF" which is non-existent:

Error #3011: The Microsoft Jet database engine could not find the object 'NewDBF'. Make sure the object exists and that you spell its name and the path name correctly.@@@2@5003011@2

If "NewDBF" is existing, no error occurred and the function ran successfully. My question is how to check first if a table is existing or not so that I can avoid this error.

[epelyavski]

Just put on error resume next

before calling the delete table function. This will block error message from appearing. If you really need to know whether the table exists before deleteing it then this is something you'll have to investigate. My "method" deletes the table without checking. As they say, no questions asked ;-)

[daneb]

Thanks. I got what you want to say. You're a great help!